Arne Gehlhaar wrote: > Becker, Holger wrote: > > J. Jeff Roberts wrote: > > > >>I have a question about indices. If I typically do this: > >> select field1, field2 from table where field3 = 1 > >>but I sometimes do this: > >> select field1 from table where field3 = 1 > > > > You have to valuate the additional space for the index and > the overhead > > to keep index actual. > > I think one index is the better solution. > > But be aware that field3 is the first index column. > > I have a similar question. I have some queries with are > something like: > select a.* from table a where a.f1 = x > select a.* from table a where a.f1 = x and a.f2 = y > select a.* from table a where a.f2 = y and a.f3 = z > select a.* from table a where a.f1 = x and a.f2 = y and a.f3 = z > ... > These queries are automatically generated by the (java) application. > > Should I create three individual indexes or one index with > many columns? > Which is better for performance? Does the number of > different values > per column make a difference? It depends.
I recommend to create single indexes on columns with high distinct values wich are used in where clauses and perhaps multiple indexes for columns which are often used together in one where clause. Also multiple indexes with all columns used in one statement could make sense if the underlaying table is very huge and only index strategy could speed up your queries. Afterwards you should check the execution plan of your statement with the explain statement and the usage of each index with a 'select * from indexes'. Additionally you could check the overall system performance of your SAP DB instance with the DB Analyzer (for more information see http://www.sapdb.org/7.4/htmhelp/2b/361e3bd129be04e10000000a114084/frameset.htm) HTH. Kind regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
