RE: Column order in indices (oracle 9.2)

Charles,

The query that I'm trying to run (using only the second column in the index) should bring about 30 rows out of 1bil

thank you

Gene Gurevich

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


                                       >, "goran bogdanovic"               

             08/01/2006 08:20          <goran00_at_gmail.com>                 
             AM                                                         cc 
                                       "oracle-l" <oracle-l_at_freelists.org> 
                                                                   Subject 
                                       RE: Column order in indices (oracle 
                                       9.2)                                
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

Just curious, what is the percentage of rows returned with that predicate compared to the total number of rows? I see you have a partitioned table - I am wondering if that particular predicate pulls back more than 20% of the table.

-----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 8:11 AM
To: goran bogdanovic
Cc: oracle-l
Subject: Re: Column order in indices (oracle 9.2)

Goran,

In my case I don't have any range conditions. My query is select * from table where column='zzz';

thank you

Gene Gurevich

             "goran

             bogdanovic"

             <goran00_at_gmail.co
To

             m>
genegurevich_at_discoverfinancial.com

cc

             08/01/2006 08:03 sac_at_uillinois.edu, oracle-l

             AM                        <oracle-l_at_freelists.org>


Subject
                                       Re: Column order in indices
(oracle
                                       9.2)

Pay also attention if some of those columns has been used in the where clause with a range condition.
If such, put this column(s) at the end of the index. The reason why, you can find in the book of J.Lewis.
CBO walk the mysterious ways :-)

On 8/1/06, Schultz, Charles <sac_at_uillinois.edu> wrote:   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

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






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