RE: Column order in indices (oracle 9.2)

Charles

The explain plan is pretty straightforward:

|   0 | SELECT STATEMENT     |                  |     1 |    41 |     1 |
|       |        |      |            |
|   1 |  PARTITION LIST ALL  |                  |       |       |       |
1 |     3 | 60,00  | PCWP |            |
|*  2 |   TABLE ACCESS FULL  | DSPLY_ACCT_OFFR  |     1 |    41 |     1 |
1 |     3 | 60,00  | P->S | QC (RAND)

And it does indicates the full table scan. When I change the SQL and add another condition to the where clause
which filters on the leading column in the PK, the explain plan does show the index being used. I agree that
there are other factors involved , but I don't know which ones.

thank you

Gene Gurevich
Oracle Engineering
224-405-4079

                                                                           
             "Schultz,                                                     
             Charles"                                                      
             <sac_at_uillinois.ed                                          To 
             u>                        <genegurevich_at_discoverfinancial.com 


                                       >, "oracle-l"                       

             08/01/2006 07:48          <oracle-l_at_freelists.org>            
             AM                                                         cc 
                                                                           
                                                                   Subject 
                                       RE: Column order in indices (oracle 
                                       9.2)                                
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
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

--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l