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
Hemant,
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
B*Tree.
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,
-Tim
-----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 http://hemantoracledba.blogspot.com --
//www.freelists.org/webpage/oracle-l
Other related posts:
