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

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 19 Apr 2016 10:49:37 -0400
Message-ID: <57164581.4010800_at_gmail.com>



On 04/18/2016 11:30 AM, Stefan Koehler wrote:
> 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:
> 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
>
>

Extended rowids were invented precisely to deaLwith partitioning. Partitioning requires a special treatment because each partition is an object, with its own object id and data object id, as well as the table as a whole. The rowid column should tell to which object does the row belong, therefore a new and more obfuscated version of rowid was invented.

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 19 2016 - 16:49:37 CEST

Original text of this message