FreeLists / oracle-l / Re: Column order in indices (oracle 9.2)

[genegurevich@xxxxxxxxxxxxxxxxxxxxx]

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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
http://www.freelists.org/webpage/oracle-l






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