yes...very well explained dear :cool:
I just wonder why do I have to locally index my Hiredate
when the table is already partitioned by it and usually it has low
cardinality(lots of dups) because its a range partition
Printable View
yes...very well explained dear :cool:
I just wonder why do I have to locally index my Hiredate
when the table is already partitioned by it and usually it has low
cardinality(lots of dups) because its a range partition
People,
I am not very good in partitioning but here is what I know about that
Local indexes helps in maintanence. You can rebuild just one partition, truncate/drop/add corresponding partition when you truncate/drop/add partition to the table, etc.
If you do all that with global indexes, it will invalidate the entire index and you will have to rebuild it which means generally sorting the entire data in the table (that's lot of work)
One can build an index on one column and partition by another (not included in the index) as fat as I remember, so no issue here with local indexes. The issue appears when one tries to build UNIQUE index. Then the index key should contain the partition key and that will force global index creation.
On the other hand, the primary/unique key constraint can be build on pre-created index which is not obligatory to be uniue, but I guess (no real experience) that such situation will bother the performance when checking the constraint
Regards
Boris
For what reason are you partitioning by hiredate?Quote:
Originally Posted by yxez