| cluter in index [message #567745] |
Thu, 04 October 2012 22:46  |
 |
guddu_12
Messages: 227
Registered: April 2012
Location: UK
|
Senior Member |
|
|
HI ,
I understand that if the records are catter in different data block then the optimizer might use full table scan rather index scan, is there any way i can load the data so that adjecent rows are stored in the same data block
Please clarify i my understanding is wrong.
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: cluter in index [message #567818 is a reply to message #567748] |
Fri, 05 October 2012 17:19  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
In Oracle, Clusters (Index Clusters and Hash Clusters) will co-locate rows with the same cluster key in the same block. Index-Organized Tables will also co-locate rows, but they store the entire row in the index (this is Oracle's closest equivalent to - but not the same as - a SQL Server Clustered Index).
Both of these options have other performance impacts that you need to investigate. Clusters can affect Full Table Scan performance and partitioning. IOTs will make scans on secondary indexes much slower.
Of course you can rebuild your table with a CREATE TABLE AS SELECT ... ORDER BY ... to co-locate rows as well, but newly inserted rows (and updates) will not follow the scheme, so you would need to regularly rebuild.
If you are just worried about Oracle not using an index because of the clustering factor, I would stop worrying. For most index scans, clustering fact will make no difference to the optimizer's choice of plan.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
