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 -----------------------------------------------------------------