Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Column order in indices (oracle 9.2)

Re: Column order in indices (oracle 9.2)

From: goran bogdanovic <goran00_at_gmail.com>
Date: Tue, 1 Aug 2006 15:03:32 +0200
Message-ID: <6d0a3ba80608010603m263e9984o898815b34e0eed59@mail.gmail.com>


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
Received on Tue Aug 01 2006 - 08:03:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US