Re: Order by with partition

In cases where it is difficult or impossible to infer the partition and/or
subpartition name logically, (hash partitioning, for example, or with complex
multicolumn range/list partitioning) there are a couple of other techniques
that you can use.

In the DBMS_MVIEW package there is a PMARKER function that returns the 
data object id for a rownumber, and you can join to user/dba/all_objects

using that.

Here's a funky example script using variable numbers of hash 
subpartitions ...

drop table t
/

create table t (col1 , col2 , col3 )
partition by range (col1)
subpartition by hash (col2)
(
partition p1 values less than (2) subpartitions 2,
partition p2 values less than (3) subpartitions 4,
partition p3 values less than (4) subpartitions 8
)
as
select mod(rownum,3)+1,
      floor(dbms_random.value(1,256)),
      floor(dbms_random.value(1,10))
from   all_objects
where rownum < 101
/

with   obj as
      (select --+ materialize
              data_object_id,
              subobject_name
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE SUBPARTITION')
select subobject_name,
      col3
from   T,
      obj
where  data_object_id = DBMS_MView.PMarker(t.rowid)
order by 1
/




A second way is to use the TBL$OR$IDX$PART$NUM() function to get the 
appropriate partition number for a table value or set of values.

It's an undocumented (except through metalink, if that counts) function

with a couple of magic numbers in it, but the general format to use is ...

TBL$OR$IDX$PART$NUM("PARTITIONED_TABLE_NAME", 0, d#, p#, "COLUMN_NAME")

The easiest way to get the appropriate format to use for this function 
is to run a trace on a "with validation" partition exchange against the

table of interest, and you'll pretty much get the complete SQL that you

need.

So you might end up with something similar to ...

with   utp as
      (select --+ materialize
              partition_position,
              partition_name
       from   user_tab_partitions
       where  table_name = 'MY_TABLE')
select utp.partition_name,
      last_name,
      first_name
from   my_table,
      utp
where  utp.partition_position = TBL$OR$IDX$PART$NUM("MY_TABLE", 0, 0, 
65535, "PART_COL")
/

I suspect that DBMS_MView.PMarker is easier, although in some 
circumstances it is slower than TBL$OR$IDX$PART$NUM because the latter is
more amenable to reducing the number of function-calls by pre-aggregating
the data before applying the function.


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

Other related posts: