Home » SQL & PL/SQL » SQL & PL/SQL » When to use partition indexes? (Oracle9i)
When to use partition indexes? [message #316107] Wed, 23 April 2008 15:15 Go to next message
Messages: 261
Registered: April 2008
Senior Member
Dear all,

I am not able to find the specific reason to go for Global index and global partitioned index..

I wanted to know at what circumstances we can use local index,global index and global partition index??

Any answer please??


Re: When to use partition indexes? [message #316141 is a reply to message #316107] Wed, 23 April 2008 22:44 Go to previous messageGo to next message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Consider a partitioned table with a primary key of TXN_NUM, and a partition key of TXN_DATE.

To create a primary key constraint, you must use a Global Index or Globally Partitioned Index. If it is Local, it would have to be non-unique because it does not contain the partition key.

Some people opt to create a UNIQUE index on (TXN_NUM, TXN_DATE) or (TXN_DATE, TXN_NUM). This is just WRONG.

Enforcing uniqueness is the most common reason for Global Indexes. The other reason is a bit more subtle, but it is incredibly important.

To understand whether you need a Global Index (other than for uniqueness) you need to understand what type of queries the index will serve.

Consider the table described above:
  • TXN_NUM - PK
  • TXN_DATE - Partition key
  • CUSTOMER_NUM - Indexed column

Now consider the following query:
FROM my_table
WHERE customer_num = '12345'

If the index on CUSTOMER_NUM is partitioned, then Oracle will perform a separate RANGE SCAN on each index partition. Worst case, this could involve (say) 3-4 block reads per partition. 100 partitions: 300-400 blocks.

If the index on CUSTOMER_NUM is GLOBAL then Oracle will perform a single range scan reading just 3-4 blocks from the global index. 100 times faster - Big difference!

But what if you perform this query:
FROM my_table
WHERE customer_num = '12345'

If you use monthly partitions, then your result set will come from at most 2 different partitions. In this case, the difference between a LOCAL and GLOBAL index would be negligible.

Rosco's Golden Rule of Global Indexes
If you are indexing for queries that return a SMALL number of rows from a potentially LARGE number of partitions, use a GLOBAL index.

"Alright then", I hear you say, "what if each customer has tens of thousands of transactions? Your example says to use a GLOBAL index, but Rosco's Golden Rule says that GLOBAL indexes are only required for a small number of transactions. How about them apples?"

The Golden Rule is right. In this case, the rows for each customer will spill across many blocks. Say the Global Index contained 100 blocks of customer 12345 and the Local Index contained 100 partitions - each with 1 block of customer 12345. The cost of scanning the local indexes - including branch blocks - would be 3-4 blocks per partition, or 300-400 in total. The Global index would still only scan 103-104 blocks.

The Global index is still faster, but the margin is being eaten away. Remember the small-volume example was 100 times faster.

Global indexes will ALWAYS be faster for queries that do not use the partition key. HOW MUCH faster depends on how many rows you return. There is a trade off of performance vs. maintainability (LOCAL indexes much easier to maintain). When this trade-off tips the balance is up to you.

Ross Leishman
Re: When to use partition indexes? [message #316518 is a reply to message #316141] Fri, 25 April 2008 08:20 Go to previous message
Messages: 261
Registered: April 2008
Senior Member
Ross, Awesome explanation. Thanks again.
Previous Topic: Table Types
Next Topic: Can DBA log in to any schema ?
Goto Forum:

Current Time: Fri Oct 28 05:34:20 CDT 2016

Total time taken to generate the page: 0.04985 seconds