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
moschen
Messages: 25
Registered: April 2007
Junior Member
Hi,

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,
M.
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: 64140
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.

Regards
Michel
Re: index on partition keys useful? [message #255816 is a reply to message #255727] Wed, 01 August 2007 21:56 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Doubt in executing PL/SQL procedure
Next Topic: Dates between
Goto Forum:
  


Current Time: Fri Dec 09 04:09:04 CST 2016

Total time taken to generate the page: 0.08529 seconds