Re: Using global index on partitioned table vs index on the same but non-partitioned table
- From: Stefan Koehler <contact@xxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, marko.sutic@xxxxxxxxx
- Date: Mon, 18 Apr 2016 17:30:53 +0200 (CEST)
Hello Marko,
I have created simple test case and noticed that index on partitioned table
is bigger. Why is this?
Tim already wrote the answer to this question. The ROWID is 6 bytes for an
index on a normal table (or a local index on a partitioned table), 8 bytes
for a clustered index and 10 bytes for a global index on a partitioned table.
Are there any performance drawbacks if you have partitioned table with global
index? (ignore maintenance)
Yes. The clustering factor can go bad, but this depends on how your real data
looks like. Your test case might be a perfect example for no impact as
rowid and object_id are increasing monotonically in most cases. Tim also
described this on Oracle-L a while ago:
//www.freelists.org/post/oracle-l/clustering-factor-and-partitioned-tables,2
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage:
http://www.soocs.de
Twitter: @OracleSK
Marko Sutic <marko.sutic@xxxxxxxxx> hat am 18. April 2016 um 15:21
geschrieben:
Hello all,
is there any difference if you have global index on partitioned table vs
regular btree index on (same) non-partitioned table?
I have created simple test case and noticed that index on partitioned table
is bigger.
Why is this?
I would expect for both indexes the same size and same performance behavior.
Are there any performance drawbacks if you have partitioned table with
global index? (ignore maintenance)
Probably I'm missing something obvious...
Thanks!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
