1 Reply Latest reply: Jul 16, 2014 11:45 PM by 1005473 RSS

    Sub Query and Partition Pruning problem.

    1005473

      Hi,

      I have two table ELM (master table) and STD1 (Detail Table).

       

      ELM- Contains range partition on TIME1 and list sub partition on TARGET_ID.

       

      SQL1: target id directly included in IN clause.

       

      SELECT time1,event1,Target_name

      FROM ELM

      WHERE  TIME1 BETWEEN '15-JUL-14 09.36.54.000000000 AM' AND '16-JUL-14 09.36.54.000000000 AM'

      and TARGET_ID in (1,3);

       

      EVENT 10128 information:-

      Partition Iterator Information:

        partition level = ABSOLUTE

        call time = START

        order = ASCENDING

        Partition iterator for level 1:

         iterator = RANGE [31, 32]

         index = 31

      partition iterator for level 2:

         iterator = ARRAY [count= 1, max = 3] = { 1 }

         index = 1

        current partition: part# = 31, subp# = 1, abs# = 90

        current partition: part# = 32, subp# = 1, abs# = 93

        current partition: part# = 32, subp# = 3, abs# = 95

       

      It scans two sub partitions(i.e. 1 and 3).

       

      SQL2: Added subquery to get target_id's 1 and 3. Below Subquery gives two target ids 1 and 3.

       

      SELECT time1,event1,Target_name

      FROM EVENT_LOG

      WHERE  TIME1 BETWEEN '15-JUL-14 09.36.54.000000000 AM' AND '16-JUL-14 09.36.54.000000000 AM'

      and TARGET_ID IN (SELECT TARGET_ID

                              FROM STD1

                               WHERE UPPER(TARGET_NAME)=UPPER('OracleST76') OR UPPER(TARGET_NAME) LIKE UPPER('OracleST76_DELETED'||'%')

                                );

       

      EVENT 10128 information:

      Partition Iterator Information:

        partition level = ABSOLUTE

        call time = START

        order = ASCENDING

        Partition iterator for level 1:

         iterator = RANGE [31, 32]

         index = 31

      partition iterator for level 2:

         iterator = RANGE [0, 2]

         index = 0

        current partition: part# = 31, subp# = 0, abs# = 89

        current partition: part# = 31, subp# = 1, abs# = 90

        current partition: part# = 31, subp# = 2, abs# = 91

        current partition: part# = 32, subp# = 0, abs# = 92

        current partition: part# = 32, subp# = 1, abs# = 93

        current partition: part# = 32, subp# = 2, abs# = 94

        current partition: part# = 32, subp# = 3, abs# = 95

       

      It scans all subpartitions..

       

      Could you help me achieve partition pruning writing sub query.

       

      P.M.

        • 1. Re: Sub Query and Partition Pruning problem.
          1005473

          Below is the predicate information:

           

          SQL1:

          Predicate Information (identified by operation id):

          ---------------------------------------------------

             1 - filter('15-JUL-14 09.36.54.000000000 AM'<='16-JUL-14 09.36.54.000000000 AM')

             3 - filter('15-JUL-14 09.36.54.000000000 AM'<='16-JUL-14 09.36.54.000000000 AM')

             5 - filter(("TARGET_ID"=1 OR "TARGET_ID"=3) AND "TIME1">='15-JUL-14 09.36.54.000000000 AM' AND

                        "TIME1"<='16-JUL-14 09.36.54.000000000 AM')

           

          SQL2:

          Predicate Information (identified by operation id):

          ---------------------------------------------------

          1 - filter('15-JUL-14 09.36.54.000000000 AM'<='16-JUL-14 09.36.54.000000000 AM')

             3 - filter('15-JUL-14 09.36.54.000000000 AM'<='16-JUL-14 09.36.54.000000000 AM')

             4 - access("TARGET_ID"="TARGET_ID")

             9 - access(ROWID=ROWID)

            11 - filter("STD1"."TARGET_NAME_VC"='ORACLEST76' OR "STD1"."TARGET_NAME_VC" LIKE

                        'ORACLEST76_DELETED%')

            15 - filter("TIME1">='15-JUL-14 09.36.54.000000000 AM' AND "TIME1"<='16-JUL-14 09.36.54.000000000 AM')

           

          It looks like filters are different of both the SQL.How to control this and achieve the partition pruning.

           

          P.M.