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: Index question

RE: Index question

From: Rick Stephenson <RStephenson_at_Ovid.com>
Date: Tue, 10 Aug 2004 11:33:26 -0600
Message-ID: <555F2B7B278B5348A28F665E6B0E43A40935EB1F@exchange-slc.ut.ovid.com>


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_at_singingsql.com] Sent: Tuesday, August 10, 2004 9:43 AM
To: oracle-l_at_freelists.org
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_at_Ovid.com>:

> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 10 2004 - 12:30:04 CDT

Original text of this message

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