Re: DWH : Index on Partition Key s

  • From: "Tim Gorman" <tim@xxxxxxxxx>
  • To: hkchital@xxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 30 Oct 2009 16:41:41 +0000


The optimizer will never choose to use it in a SQL statement. It does no good 
at all (FULL table scan with partition prune will always be better) and only 
does a little harm (by adding a little more workload to DML), but could cause a 
lot of harm if it does accidentally get used or if it is bitmap instead of 

The VARCHAR2 representation of a DATE value has no justification, as you're 
aware. Try to get them to use a DATE datatype before this thing grows and it 
becomes too late? On the other hand, they could do far worse. Like instituting 
"end dates" or "active flags" to indicate "current" rows, or something deadly 
like that...

Hope this helps,


-----Original Message-----
From: Hemant K Chitale [mailto:hkchital@xxxxxxxxxxxxxx]
Sent: Friday, October 30, 2009 09:01 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: DWH : Index on Partition Key s

Would there be any reason to create a single column index on the PARTITION_KEY 
column (which is a VARCHAR2 representation of a date --- I know, I know, bear 
with me !) ? So, we have 24 PARTITION_KEY values for the 24 months that we are 
holding data. Why have a single-column index on PARTITION_KEY ? (no this isn't 
a trick question : I am really confused by this design that I see having been 
implemented). Hemant K Chitale --

Other related posts: