How to select Subpartition name in a Select query?
834452Jan 26 2011 — edited Jan 26 2011Hi,
I have a table that is partitioned on date range and subpartitioned based on and ID list. Lets assume the table name is something like: MY_TABLE
The partition name would look like: P_20110126160527
The subpartition list is as follows: GB, IN, AU, US etc. The sub partition name for GB would look like
P_20110126160527_GB
I need to run a select query to fetch data from MY_TABLE along with Sub partition name. The result set needs to look like:
Name|Location|SubPartition
Sam|UK|P_20110126160527_GB
Tom|UK|P_20110126160527_GB
Dave|AU|P_20110126160527_AU
The data available in ALL_TAB_SUBPARTITIONS and USER_TAB_SUBPARTITIONS can't be used just because the only join condition available is the TABLE Name but we would also have to join on SUBPARTITION KEY. I am not sure how to achieve this.
Does anyone here have a clue?