Home » SQL & PL/SQL » SQL & PL/SQL » index on partition keys useful?
index on partition keys useful? [message #255723] Wed, 01 August 2007 10:30 Go to next message
Messages: 25
Registered: April 2007
Junior Member

I have a general question. Imagine a table:

 year | week | transaction_id | product_type
      |      |                |

This table is partitioned by year and sub-partitioned by week number.

Now I would want to have an index by product_type and I know that it is mainly queried together with year and week. So it would probably make sense to use a local index, but do I need to create the local index on the columns (year, week, product_type) or is (product_type) enough, as the local index is partitioned by the other columns anyway?

Best regards,
Re: index on partition keys useful? [message #255727 is a reply to message #255723] Wed, 01 August 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you query with the partition columns then a local index will be better not only for performances but for maintenance.

Re: index on partition keys useful? [message #255816 is a reply to message #255727] Wed, 01 August 2007 21:56 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You don't need to store the partition columns in a non-unique locally partitioned index. For locally partitioned indexes they only need to be there if the index is UNIQUE, and even then I believe (don't quote me, I can't remember whether I read this or made it up) that Oracle doesn't even need to store them if they appear first in the indexed columns.

Ross Leishman
Next Topic: How to get count of records in a delete statement
Goto Forum:

Current Time: Mon Aug 21 14:52:23 CDT 2017

Total time taken to generate the page: 0.09300 seconds