Re: DWH : Index on Partition Key s

Tim,

Thanks for your response.
Since the PARTITION_KEY stores only a single value ('200910310000' -- for all the rows for October 2009), the index is absolutely useless and only an overhead !
I *hope* that I don't discover "end dates" , but I do see signs of "Flag" and "Code" columns, presumably to identify different statuses or record types, hopefully not just to identify "this is an active row". 

Hemant K Chitale
http://hemantoracledba.blogspot.com

At 12:41 AM Saturday, Tim Gorman wrote:

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










--
http://www.freelists.org/webpage/oracle-l

Other related posts: