Jenny Zhang wrote:
> I have a simple SQL statement: > SELECT i_id, a_lname FROM item, author WHERE i_a_id=a_id and a_lname > LIKE 'BABABABA%' > > I have two indexes on author (a_lname, a_id) > I have one index on item (i_a_id) > > The execution plan I got shows that it is not using index on > author(a_lname) > > 'DBT';'AUTHOR';'';'TABLE SCAN';' 162';'';'';'';'' > 'DBT';'ITEM';'I_A_ID';'JOIN VIA INDEXED COLUMN';' > 970';'';'';'';'' > 'DBT';'';'';' RESULT IS COPIED , COSTVALUE IS';' > 1600';'';'';'';'' > > I read from the mailing list that if the wildcard is at the > beginning of > the string, then it will not use the index. But in this case, the > wildcard is at the end of the sting. > > If I use the exact string: > > SELECT i_id, a_lname FROM item, author WHERE i_a_id=a_id and a_lname > LIKE 'BABABABA' > > The the optimizer will use the index, and the execution plan is: > > 'DBT';'AUTHOR';'A_LNAME';'EQUAL CONDITION FOR INDEXED COLUMN';' > 162';'';'';'';'' > 'DBT';'ITEM';'I_A_ID';'JOIN VIA INDEXED COLUMN';' > 970';'';'';'';'' > 'DBT';'';'';' RESULT IS COPIED , COSTVALUE IS';' > 2';'';'';'';'' > > Any suggestions on how I can make the optimizer use the index? It is a common misunderstanding that using an index is always better than scanning the primary table. If you use an index the you have to scan one tree (that with the index in it) and jump randomly into those pages (leaf-pages of the primary tree, storing the records) whose keys could be found in the index. Depending on the size of your primary data and the part of the index which has to be scanned concerning your qualification the optimizer sometimes prefers the table scan to the jumping back and force between two trees and randomly accesses in one tree. May be that - the wildcard just makes the difference (maybe small, but important) between table scan and index-usage or - that the statistic-values concerning the index/primary sizes are too old and UPDATE STATISTICS should be done for this table. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
