Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mani.

Asked: August 28, 2020 - 3:24 pm UTC

Last updated: September 16, 2020 - 10:04 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

I have a table with partitions. I am trying to select the data and partition name in the same query. I have created a part_test table and inserted data. It is on the Live SQL link.

Looking to query something like this,

SELECT ld_dt, ld_src, <partition name> FROM part_test, all_tab_partitions and join the part_test with all_tab_partitions to get both data and partition name.

Any help is greatly appreciated.

Thanks,
Mani

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/kk0i56uevxb29jx79l8jow3e1

and Connor said...

This should do it

SQL> create table t
  2  partition by list ( x )
  3  ( partition p0 values (0),
  4    partition p1 values (1),
  5    partition p2 values (2)
  6  )
  7  as
  8  select mod(rownum,3) x, rownum y
  9  from dual
 10  connect by level <= 20;

Table created.

SQL>
SQL> with partitions as
  2  ( select /*+ materialize */ data_object_id, subobject_name
  3    from  user_objects
  4    where object_name = 'T'
  5    and   object_type = 'TABLE PARTITION'
  6  )
  7  select t.*, p.subobject_name
  8  from   t, partitions p
  9  where  dbms_rowid.rowid_object(t.rowid) = p.data_object_id;

         X          Y SUBOBJECT_NAME
---------- ---------- --------------------
         0          3 P0
         0          6 P0
         0          9 P0
         0         12 P0
         0         15 P0
         0         18 P0
         1          1 P1
         1          4 P1
         1          7 P1
         1         10 P1
         1         13 P1
         1         16 P1
         1         19 P1
         2          2 P2
         2          5 P2
         2          8 P2
         2         11 P2
         2         14 P2
         2         17 P2
         2         20 P2

20 rows selected.


I used "materialize" because diving thousands of times into the data dictionary views can be expensive.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up question

Mani Ramasamy, September 06, 2020 - 9:47 pm UTC

Thank you Connor for the answers.

I am seeing nulls in DATA_OBJECT_ID for some partitions. Also, when the table has sub partitions (OBJECT_TYPE = TABLE SUBPARTITION), what field can I use to join to the DBMS_ROWID.ROWID_OBJECT(ROWID) to get the records?
Connor McDonald
September 07, 2020 - 2:47 am UTC

It just means that this is a logical not a true partition of the data, eg

SQL> create table t1 ( x int, y int )
  2  partition by list ( x )
  3  subpartition by list ( y )
  4  (
  5    partition p1 values (1)
  6    (
  7      subpartition p1a values (10),
  8      subpartition p1b values (20)
  9    ),
 10    partition p2 values (2)
 11  );

Table created.

SQL>
SQL> insert into t1 values (1,10);

1 row created.

SQL> insert into t1 values (1,20);

1 row created.

SQL> insert into t1 values (2,30);

1 row created.

SQL>
SQL> select x,y, dbms_rowid.rowid_object(rowid)
  2  from t1;

         X          Y DBMS_ROWID.ROWID_OBJECT(ROWID)
---------- ---------- ------------------------------
         1         10                         101001
         1         20                         101002
         2         30                         101003

3 rows selected.

SQL>
SQL> select object_id, data_object_id, subobject_name
  2  from  user_objects
  3  where object_name = 'T1';

 OBJECT_ID DATA_OBJECT_ID SUBOBJECT_NAME
---------- -------------- --------------------
    100998
    100999                P1
    101000                P2
    101001         101001 P1A
    101002         101002 P1B
    101003         101003 SYS_SUBP5199

6 rows selected.


Notice that the data in the table *only* maps to a data object id. The others just reflect the dictionary definition

Thank you

A reader, September 16, 2020 - 2:19 am UTC

Thank you very much for the explanations. This is really helpful to pull data from the big tables.

Follow up question

A reader, September 16, 2020 - 2:35 am UTC

Hi Connor,

I have a table, it has object_id, data_object_id and subobject_name, but when I search either by subobject_name, in this case subpartition, I am not getting any data. Same, when I searched data_object_id against the dbms_rowid.rowid_object(tbl.rowid), I am not getting any records. What does this mean? Could you please clarify?

Thanks a lot!
Chris Saxon
September 16, 2020 - 10:04 am UTC

Please share an example of the statements you're running (like Connor's above).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library