Home » RDBMS Server » Performance Tuning » cluter in index (oracle 11gr2)
cluter in index [message #567745] Thu, 04 October 2012 22:46 Go to next message
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 #567746 is a reply to message #567745] Thu, 04 October 2012 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>is there any way i can load the data so that adjecent rows are stored in the same data block
If a row has 10 columns & you can ORDER BY any one of the 10 columns, which row is "adjacent" to this row?
In other words, does not "adjacent" row depend upon the ORDER BY clause?

Re: cluter in index [message #567747 is a reply to message #567746] Thu, 04 October 2012 23:55 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
HI I now understand that using order by i can make use of the records to be directed in the same datablocks until it gets fill
Re: cluter in index [message #567748 is a reply to message #567747] Thu, 04 October 2012 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>HI I now understand that using order by i can make use of the records to be directed in the same datablocks until it gets fill
please post SQL & results that show above is true

What happened to adjacent rows?
Re: cluter in index [message #567818 is a reply to message #567748] Fri, 05 October 2012 17:19 Go to previous message
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
Previous Topic: Is this a bug on Oracle11gr2?
Next Topic: SSL on database connections
Goto Forum:
  


Current Time: Thu Mar 28 03:19:34 CDT 2024