RE: Column order in indices (oracle 9.2)
- From: "Schultz, Charles" <sac@xxxxxxxxxxxxx>
- To: <genegurevich@xxxxxxxxxxxxxxxxxxxxx>, "goran bogdanovic" <goran00@xxxxxxxxx>
- Date: Tue, 1 Aug 2006 08:20:30 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxx
To
m>
genegurevich@xxxxxxxxxxxxxxxxxxxxx
cc
08/01/2006 08:03 sac@xxxxxxxxxxxxx, oracle-l
AM <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx> 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@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
Other related posts:
