Re: Why "Separating Data and Indexes improves performance" is a myth?

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 27 Apr 2004 03:20:40 +0300
Message-ID: <01c501c42bed$79551880$ed869fd9@porgand>

> Now will someone please pass the shotgun. I've a duhveloper to shoot =
> because having his indexes in a separate tablespace from the data =
> "improves performance"!! Like who needs an index on a 100 row x 4 =
> column table in the first place?

You definitely can benefit from an index on even a tiny table, if it is frequently accessed. Or better yet use an IOT or a single table hash cluster (preferrably without colliding hashed values). The win comes partially from smaller number of LIOs required, but the LIOs can also be a bit cheaper if Oracle knows the indexed values to be unique, also allowing the optimizer to generate better exeution plans by putting known single row predicates first in join order.

Tanel.

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 Mon Apr 26 2004 - 19:18:21 CDT