|
|
|
|
|
|
|
|
| Re: Index Partitioning local or global or both?? [message #432139 is a reply to message #431868] |
Sat, 21 November 2009 17:36   |
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 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
wakula wrote on Sun, 22 November 2009 10:36If 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 |
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 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
wakula wrote on Mon, 23 November 2009 20:40rleishman 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:20If 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 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
