Home » RDBMS Server » Performance Tuning » Index Partitioning local or global or both??
Index Partitioning local or global or both?? [message #431868] Thu, 19 November 2009 13:23 Go to next message
nsingh49
Messages: 9
Registered: August 2007
Junior Member
Hi there,
How advisable is it to have both local and global indexes together on a partitioned table, spread across multiple disks???


Thanks..

Nirmal
Re: Index Partitioning local or global or both?? [message #431870 is a reply to message #431868] Thu, 19 November 2009 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Advisable or not depends on each specific case.

Regards
Michel
Re: Index Partitioning local or global or both?? [message #431880 is a reply to message #431870] Thu, 19 November 2009 14:13 Go to previous messageGo to next message
nsingh49
Messages: 9
Registered: August 2007
Junior Member
Well irrespective of the specific case, in general is this a good idea.
Re: Index Partitioning local or global or both?? [message #431883 is a reply to message #431880] Thu, 19 November 2009 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no general idea. It depends on the needs and physical layer.

Regards
Michel
Re: Index Partitioning local or global or both?? [message #432139 is a reply to message #431868] Sat, 21 November 2009 17:36 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
If the table is partitioned by date and you wish to drop old partitions periodically - then it is a good thing to have local (partitioned) index.
In any other case : it depends.
Read about prefixed and non-prefixed local indexes - although it is not a full explanation, it might help you in understanding that subject. General rule is: if you don't know the thing then don't use it. If your knowledge is not enough then use google or seek for help from more experienced people (more experienced google?).
Re: Index Partitioning local or global or both?? [message #432189 is a reply to message #432139] Sun, 22 November 2009 21:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
wakula wrote on Sun, 22 November 2009 10:36
If the table is partitioned by date and you wish to drop old partitions periodically - then it is a good thing to have local (partitioned) index.


I would prefer to word it:

Quote:
If the table is partitioned by date and you wish to drop old partitions periodically - then sure is more convenient if your indexes happen to be locally partitioned.


Indexes are designed to improve performance. Full stop. End of story. They are not designed to make archiving more convenient.

There is nothing wrong with having local and globally partitioned indexes on the same table.

If you are seeking a good rule of thumb:
  • Indexes that already include the table partition key as the first column (i.e. "prefixed" indexes) should be LOCALly partitioned.
  • For all other indexes, those with selective keys (e.g. where an index scan returns fewer rows than there are partitions) should be GLOBAL (either globally partitioned or non-partitioned).
  • Non-selective indexes may be LOCALly partitioned for convenience without impacting performance.


Ross Leishman
Re: Index Partitioning local or global or both?? [message #432248 is a reply to message #432189] Mon, 23 November 2009 03:40 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
rleishman wrote on Mon, 23 November 2009 04:20

[*]For all other indexes, those with selective keys (e.g. where an index scan returns fewer rows than there are partitions) should be GLOBAL (either globally partitioned or non-partitioned).

I would not agree with that statement in 100%.
Partitioning add some extra processing time but it might be vary small issue when you compare it to indexes becoming unusable after a (sub)partition is dropped.

And my earlier statement might have been unclear by suggesting that it is a good idea to have such index no matter what - which is not true. Indexing is a very very complex subject in Oracle DB.
Re: Index Partitioning local or global or both?? [message #432376 is a reply to message #432248] Mon, 23 November 2009 20:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
wakula wrote on Mon, 23 November 2009 20:40
rleishman wrote on Mon, 23 November 2009 04:20

[*]For all other indexes, those with selective keys (e.g. where an index scan returns fewer rows than there are partitions) should be GLOBAL (either globally partitioned or non-partitioned).

I would not agree with that statement in 100%...


rleishman wrote on Mon, 23 November 2009 14:20
If you are seeking a good rule of thumb: ...


Which is why I said it was a good "rule of thumb"; I don't agree 100% with any rule of thumb, least of all my own. It's fine to break the rules if you understand the consequences. But if you don't understand the consequences, stick to the rules.

If you are suggesting that - as a first choice - you should create all indexes as locally partitioned for convenience until you discover a performance problem, then I think we'll just have to agree to disagree.

Ross Leishman

Re: Index Partitioning local or global or both?? [message #433142 is a reply to message #431868] Mon, 30 November 2009 06:06 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
If we will think logically...

local index will help instead of a global index for partition tables.

simply it makes the i/o faster and using both local and global the index will slow down if use the same partition key.

If you are thinking I am wrong go step by step and check the cpu cost, i/o doing autotrace on.

1. create only local index and check
2. create only global index and check
3. create both and check


to check the cost and i/o
set parameter SQL_TRACE=TRUE or ALTER SYSTEM SET SQL_TRACE=TRUE;
please run @$ORACLE_HOME/sqlplus/admin/plustrce.sql
run a select query on that table you did indexing.

I just love autotrace.
Re: Index Partitioning local or global or both?? [message #434463 is a reply to message #433142] Wed, 09 December 2009 06:00 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't think the OP was talking about indexing the same columns both locally and globally. I certainly wasn't. I read this as a question of "If one of your indexes is local/global, should all others be the same".

Ross Leishman
Previous Topic: Need to tune a Query - xjd
Next Topic: require to tune a sql query
Goto Forum:
  


Current Time: Wed Dec 07 03:21:48 CST 2016

Total time taken to generate the page: 0.19523 seconds