Re: Using global index on partitioned table vs index on the same but non-partitioned table

> 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: http://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: _at_OracleSK  

> Marko Sutic <marko.sutic_at_gmail.com> 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!

--
http://www.freelists.org/webpage/oracle-l