Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.