Re: Order by with partition

From: <david_at_david-aldridge.com>
Date: Thu, 11 May 2006 11:10:04 -0600
Message-ID: <42FD968C0005CB16@mta10.wss.scd.yahoo.com>

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

Received on Thu May 11 2006 - 12:10:04 CDT