RE: what does info from onstat -g ses xxx indicate here

>===== Original Message From Dan Michaelis <dan_michaelis@my-deja.com> ===== >In article <864nt2$a5l$1@nnrp1.deja.com>, > mars1972@my-deja.com wrote: >> >> > The indexes are causing the problem. >> > >> > > Current statement name : slctcur >> > > Current SQL statement : >> > > select mast_pol_num from pal_obmast where (mast_allocated="N" or>> > > mast_allocated="Y") and mast_tab_num="329" >> > >> > mast_tab_num is indeed indexed but its part of a composite key >> > >> > i.e ( an_other, and_an_other, mast_tab_num ) are the fields listed >in >> the >> > index. >> > >> > Now for the above query neither of the 1st two fields are involved , >> so in >> > my experience I >> > think that the index will be being scanned sequentially and >therefore >> its >> > useless. >> > >> >> I wouldn't say useless. Even if it is being sequentially scanned, >it's >> better than sequentially scanning the table, where each row would be >> significantly larger than the rows in the index. > >Forgive my ignorance, but is it not true that if you're filtering on a >column that is not the head of an index, even if that column is part of >an index, the index isn't used? If that's the case, and the first two >fields are NEVER used as filters, the index is a little worse than >useless... It slows down updates, inserts and deletes, and is never used >for queries... > If the index contains all the fields in the table foo which the query is requesting or filtering on, the database can just do a sequential scan of the index and do less i/o. (This is assuming your index takes up less pages than all of your data pages. Usually a safe assumption) <SNIP> Hope this helps, Will ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------