This discussion is archived
7 Replies Latest reply: Jan 30, 2013 11:08 AM by rp0428 RSS

Reference partitioning and partition pruning

rahulras Explorer
Currently Being Moderated
Hi All,

I am on v 11.2.0.3.
I have a pair of typical parent-child tables. Child table is growing like hell, hence we want to partition it, which will be used for deleting/dropping old data later on.
There is no partitioning key in the child table which I can use for relating the data to the time when data was created. So, I thought I can use the timestamp from parent table for partitioning the parent table and reference partition the child table.
I am more concerned about the child table (or the queries running on the child table) in terms of performance. ITEM_LIST_ID from the child table is extensively used in queries to access data from child table.
How will partition pruning work when the child table is queried on the foreign key? will it every time go to the parent table, find out the partition and then resolve the partition for child table?
The setup is given in the scripts below, will it cause lot of locking (to resolve partitions)? or am I worrying for nothing?

Here are the scripts
CREATE TABLE ITEM_LISTS /* Parent table, tens of thousands of records */
(
  ITEM_LIST_ID    NUMBER(10)     NOT NULL PRIMARY KEY, /* Global index on partitioned table !!! */
  LIST_NAME       VARCHAR2(500)  NOT NULL,
  FIRST_INSERTED  TIMESTAMP(6)   NOT NULL
)
PARTITION BY RANGE ( FIRST_INSERTED )
(
  partition p0 values less than ( to_date('20130101','YYYYMMDD') ),
  partition p201301 values less than ( to_date('20130201','YYYYMMDD') ),
  partition p201302 values less than ( to_date('20130301','YYYYMMDD') ),
  partition p201303 values less than ( to_date('20130401','YYYYMMDD') ),
  partition p201304 values less than ( to_date('20130501','YYYYMMDD') ),
  partition p201305 values less than ( to_date('20130601','YYYYMMDD') )
);

CREATE INDEX ITEM_LISTS_IDX1 ON ITEM_LISTS ( LIST_NAME ) LOCAL ;

CREATE TABLE ITEM_LIST_DETAILS /* Child table, millions of records */
(
  ITEM_ID        NUMBER(10)     NOT NULL,
  ITEM_LIST_ID   NUMBER(10)     NOT NULL, /* Always used in WHERE clause by lots of big queries */
  CODE           VARCHAR2(30)   NOT NULL,
  ALT_CODE       VARCHAR2(30)   NOT NULL,
  CONSTRAINT   ITEM_LIST_DETAILS_FK
  FOREIGN KEY  ( ITEM_LIST_ID ) REFERENCES ITEM_LISTS
)
PARTITION BY REFERENCE ( ITEM_LIST_DETAILS_FK )
;

CREATE INDEX ITEM_LIST_DETAILS_IDX1 ON ITEM_LIST_DETAILS (ITEM_ID) LOCAL;
CREATE INDEX ITEM_LIST_DETAILS_IDX2 ON ITEM_LIST_DETAILS (ITEM_LIST_ID, CODE) LOCAL;
Any thoughts / opinions / corrections ?

Thanks in advance
  • 1. Re: Reference partitioning and partition pruning
    JohnWatson Guru
    Currently Being Moderated
    If you find that performance degrades, you should use global indexes, not local.
  • 2. Re: Reference partitioning and partition pruning
    rp0428 Guru
    Currently Being Moderated
    >
    There is no partitioning key in the child table which I can use for relating the data to the time when data was created. So, I thought I can use the timestamp from parent table for partitioning the parent table and reference partition the child table.
    I am more concerned about the child table (or the queries running on the child table) in terms of performance. ITEM_LIST_ID from the child table is extensively used in queries to access data from child table.
    How will partition pruning work when the child table is queried on the foreign key? will it every time go to the parent table, find out the partition and then resolve the partition for child table?
    >
    It will work just fine. See the 'Full Partition-Wise Joins' section of the VLDB doc
    http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BABCGBBC
    >
    A reference-partitioned table can take advantage of partition pruning through the join with the referenced table.
    . . .
    Such a large join is typical in data warehousing environments. In this case, the entire customer table is joined with one quarter of the sales data. In large data warehouse applications, this might mean joining millions of rows. The join method to use in that case is obviously a hash join. You can reduce the processing time for this hash join even more if both tables are equipartitioned on the cust_id column. This functionality enables a full partition-wise join.
  • 3. Re: Reference partitioning and partition pruning
    JohnWatson Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    There is no partitioning key in the child table which I can use for relating the data to the time when data was created. So, I thought I can use the timestamp from parent table for partitioning the parent table and reference partition the child table.
    I am more concerned about the child table (or the queries running on the child table) in terms of performance. ITEM_LIST_ID from the child table is extensively used in queries to access data from child table.
    How will partition pruning work when the child table is queried on the foreign key? will it every time go to the parent table, find out the partition and then resolve the partition for child table?
    >
    It will work just fine. See the 'Full Partition-Wise Joins' section of the VLDB doc
    >
    The partitioning key is not the join key, so partitionwise join (full or partial) is impossible.

    The problem to be considered is that local indexes on the child table means that the join will require searching many index partitions. That is why a global index would be better, if it does not impact too much on manageability.

    Edited by: JohnWatson on Jan 29, 2013 10:12 PM
    My comment above is not correct, apologies. Partitionwise join is indeed possible - but whether it is relevant is a different matter. It would only apply for a hash join, not for a predicate on item_list_id.
  • 4. Re: Reference partitioning and partition pruning
    rahulras Explorer
    Currently Being Moderated
    Thanks friends.

    I am still thinking. What is the point of reference partitioning ?

    In case of reference partitioning, there will always be a child table, which might always be the bigger table. If we have to join/query the child table using the partition key, how the partition will be pruned?
    I was imagining that, "reference partitioning idea" will use the parent table to find out the partition of "parent and child" and help us using just one partition.
  • 5. Re: Reference partitioning and partition pruning
    rp0428 Guru
    Currently Being Moderated
    >
    I am still thinking. What is the point of reference partitioning ?

    In case of reference partitioning, there will always be a child table, which might always be the bigger table. If we have to join/query the child table using the partition key, how the partition will be pruned?
    I was imagining that, "reference partitioning idea" will use the parent table to find out the partition of "parent and child" and help us using just one partition.
    >
    The point of reference partitioning is to allow you to partition a child table on columns that don't exist in the table. That allows the parent and child to be equi-partitioned without having to duplicate the parent columns in the child table.

    For best results you still need to use the partition key in the query predicates.
  • 6. Re: Reference partitioning and partition pruning
    rahulras Explorer
    Currently Being Moderated
    To check how partition pruning works here, I inserted some data in these tables. Inserted data in ITEM_LISTS (parent) from DBA_OBJECTS ( object_id => item_list_id, object_name => list_name, first_inserted => manually created). Also created corresponding child data in ITEM_LIST_DETAILS, so that, for every item_list_id in parent about 5000 records go in child table.

    Looking at the queries and plan below, my question is, what exactly does the operations "PARTITION REFERENCE SINGLE" and "PARTITION REFERENCE ITERATOR" imply ??
    I gave a search on "PARTITION REFERENCE ITERATOR" in Oracle 11.2 documentation, it says "No exact match found" !!
    /* Direct query on child table */
    SQL> select count(*) from item_list_details where item_list_id = 6323 ;
    
      COUNT(*)
    ----------
          5000
    
    1 row selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2798904155
    
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                        |     1 |     5 |    22   (0)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE             |                        |     1 |     5 |            |          |       |       |
    |   2 |   PARTITION REFERENCE SINGLE|                        |  5000 | 25000 |    22   (0)| 00:00:01 |   KEY |   KEY |
    |*  3 |    INDEX RANGE SCAN         | ITEM_LIST_DETAILS_IDX2 |  5000 | 25000 |    22   (0)| 00:00:01 |   KEY |   KEY |
    ----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("ITEM_LIST_ID"=6323)
    
    SQL> select * from temp1; /* Dummy table to try out some joins */
    
     OBJECT_ID OBJECT_NAME
    ---------- ----------------------------------------
          6598 WRH$_INTERCONNECT_PINGS
    
    1 row selected.
    
    /* Query on child table, joining with some other table */
    SQL> select count(*)
      2  from temp1 d, ITEM_LIST_DETAILS i1
      3  where d.object_id = i1.item_list_id
      4  and d.object_name = 'WRH$_INTERCONNECT_PINGS';
    
      COUNT(*)
    ----------
          5000
    
    1 row selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2288153583
    
    -------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                        |     1 |    70 |    24   (0)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE                |                        |     1 |    70 |            |       |  |       |
    |   2 |   NESTED LOOPS                 |                        |  5000 |   341K|    24   (0)| 00:00:01 |       |       |
    |*  3 |    TABLE ACCESS FULL           | TEMP1                  |     1 |    65 |     3   (0)| 00:00:01 |       |       |
    |   4 |    PARTITION REFERENCE ITERATOR|                        |  5000 | 25000 |    21   (0)| 00:00:01 |   KEY |   KEY |
    |*  5 |     INDEX RANGE SCAN           | ITEM_LIST_DETAILS_IDX2 |  5000 | 25000 |    21   (0)| 00:00:01 |   KEY |   KEY |
    -------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("D"."OBJECT_NAME"='WRH$_INTERCONNECT_PINGS')
       5 - access("D"."OBJECT_ID"="I1"."ITEM_LIST_ID")
  • 7. Re: Reference partitioning and partition pruning
    rp0428 Guru
    Currently Being Moderated
    >
    Looking at the queries and plan below, my question is, what exactly does the operations "PARTITION REFERENCE SINGLE" and "PARTITION REFERENCE ITERATOR" imply ??
    >
    PARTITION REFERENCE SINGLE means that Oracle will use only ONE partition. If you look at the Pstart and Pstop columns you will see KEY. That means that the partition that Oracle will access will be determined at runtime.

    PARTITION REFERENCE ITERATOR means that Oracle may use multple partitions and will iterate from the Pstart partition through the Pstop partition. Again the use of KEY means that Oracle will determine the range at runtime.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points