RE: Index question
- From: Rick Stephenson <RStephenson@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 10 Aug 2004 11:33:26 -0600
Thanks for your help on this. The additional column is a varchar2(32) data
type. I ran some tests too see the effect on the number of blocks retrieved
to satisfy a query. The single column required 360 blocks, whereas the
concatenated column required 369 blocks. Not much difference, but I guess
it all depends on the performance requirement of the application.
Thanks,
Rick Stephenson
-----Original Message-----
From: Dan Tow [mailto:dantow@xxxxxxxxxxxxxx]
Sent: Tuesday, August 10, 2004 9:43 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index question
The concatenated index gets deeper faster (with fewer rows, that is), so
there
is a slight hit for that, potentially, but chances are pretty good that you
aren't close enough to an "edge" where the more-compact index will lose a
level
that this effect matters. If the second column is on average fatter than the
first (more bytes), this effect becomes more important.
For the same reason, caching is theoretically better in the single-column
index,
but this is only significant if the index is large enough that imperfect
caching is significant (pretty darn large), and if you are hitting
comparable
numbers of index leaf blocks and table blocks (which will almost always
dominate physical I/O), which is to say you are *not* doing range scans that
hit many table rows per range scan. As Lex points out, though, the act of
the
range scan, apart from the physical I/O, is more work with the unnecessary
column.
If you are still on the rule-based optimizer, you sometimes need the
single-column index just to get the optimizer to prefer that leading column
over some other index that has an entirely different index on some
less-selective column, but you can always work around this if you have
control
of the SQL, and don't mind doing some SQL tuning. In the RBO world, though,
this is a two-edged sword - the new single-column index can be *too*
attractive
to the RBO, getting preferred in many cases where you *don't* want it, too.
On the flip side, the extra index will cost you at insert and delete time,
and
at update time, if you ever update that leading column.
I've personally never run into a case where I needed the redundant
single-column
index - performance with the concatenated indexes I had was always good
enough.
Thanks,
Dan Tow
650-858-1557
www.singingsql.com
Quoting Rick Stephenson <RStephenson@xxxxxxxx>:
> I have a query that references Table t column c in the where clause.
Table
> t already has an index on (column c, column d). Is there any reason to
add
> an index to table t that contains just column c? I know that Oracle will
> use the other index because of the leading column, but is there a
> performance hit due to it being a composite index?
>
>
> I am running Oracle EE 9.2.0.5.
>
>
>
> Thanks,
>
>
>
> Rick Stephenson
>
>
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
