When you say that sqlplus uses the index do you actually execute the sql and look at the sql trace or v$sqlplan for the sql, or are you looking at autotrace (traceonly) or explain? Explain, and therefore autotrace, is unreliable - if not to say a bloody liar - if the sql has bind variables. Even more so now in Oracle 9 (or 10) with bind variable peeking. Explain does not peek!!! david hill wrote: > I have a strange little problem this morning > It's a simple little query being executed by our JDE application, er > Peoplesoft, er Oracle=20 > > SELECT * FROM CRPDTA.F0901 WHERE ( GMMCU =3D :KEY1 AND GMOBJ =3D :KEY2 AND > GMSUB =3D :KEY3 ) > > And the table has an index on GMMCU, GMSUB, GMOBJ > > Now when I pull the execution plan from v$sql_plan I see that it is doing a > full table scan of this table > > but when I execute the query with autotrace on, or use explain plan for, > Everything looks good the query uses the index,=20 > > I've refreshed the stats, checked for stored outlines, there are none, and > bounced the db, and I always get the same result. > > The application does a Full table scan and in sqlplus and index,=20 > when running the test I do keep the bind variables. > > Anyone have any ideas on this one?? > > > Thanks > David Hill > > > > > > > > CONFIDENTIALITY NOTICE > This message contains confidential information intended only for the use of > the individual or entity named as recipient. Any dissemination, distribution > or copying of this communication by anyone other than the intended recipient > is strictly prohibited. If you have received this message in error, please > immediately notify us and delete your copy. Thank you. > > AVIS DE CONFIDENTIALIT=C9 > Les informations contenues aux pr=E9sentes sont de nature privil=E9gi=E9e et > confidentielle. Elles ne peuvent =EAtre utilis=E9es que par la personne ou > l'entit=E9 dont le nom para=EEt comme destinataire. Si le lecteur du pr=E9s= > ent > message n'est pas le destinataire pr=E9vu, il est par les pr=E9sentes pri= > =E9 de > noter qu'il est strictement interdit de divulguer, de distribuer ou de > copier ce message. Si ce message vous a =E9t=E9 transmis par m=E9garde, veu= > illez > nous en aviser imm=E9diatement et supprimer votre copie. Merci. > > -- > //www.freelists.org/webpage/oracle-l > -- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- //www.freelists.org/webpage/oracle-l