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

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 19 Apr 2016 10:49:37 -0400

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



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

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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2016