Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mani.

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

Answered by: Connor McDonald - Last updated: September 16, 2020 - 10:04 am UTC

Category: SQL - Version: 11.2.0.4

Viewed 100+ times

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:

and we 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.

and you rated our response

  (3 ratings)

Reviews

Follow up question

September 06, 2020 - 9:47 pm UTC

Reviewer: Mani Ramasamy from Charlotte NC USA

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

Followup  

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

September 16, 2020 - 2:19 am UTC

Reviewer: A reader

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

Follow up question

September 16, 2020 - 2:35 am UTC

Reviewer: A reader

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

Followup  

September 16, 2020 - 10:04 am UTC

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

More to Explore

PL/SQL

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