Gints, Thanks for the links. I think I have exactly the situation where index skip scan should work as my the first column in my index has only three distinct values. But the explain plan still shows the full table scan. I'll open SR with Oracle to see what they say thank you Gene Gurevich "Gints Plivna" <gints.plivna@gma il.com> To Sent by: sac@xxxxxxxxxxxxx oracle-l-bounce@f cc reelists.org genegurevich@xxxxxxxxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx> Subject 08/01/2006 08:08 Re: Column order in indices (oracle AM 9.2) Please respond to gints.plivna@gmai l.com More about column orders and skip scans: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5671539468597 and http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1156161921465 Gints Plivna http://www.gplivna.eu/ 2006/8/1, Schultz, Charles <sac@xxxxxxxxxxxxx>: > The column order does matter, but Oracle can SKIP SCAN an index in the > situation you talk about. To determine exactly what is going on, you > should at least get an explain plan of your sql. I imagine there are > other factors involved. > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of > genegurevich@xxxxxxxxxxxxxxxxxxxxx > Sent: Tuesday, August 01, 2006 7:44 AM > To: oracle-l > Subject: Column order in indices (oracle 9.2) > > Hi everybody: > > I remember reading that in Oracle 9.2 and higher the order of the > columns in an index does not matter. > That is oracle will be able to quickly search on a column even if it is > not a leading one. I have a table with a primary key consisting of three > columns. When a table is queried based on the third column, the data > start coming out immediately, but when I use the second column the query > just sits there. > Is that an expected behavior? Did I misunderstood something about the > column order? > > thanks for any insight > > Gene Gurevich > > > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l