0 Replies Latest reply on Jan 3, 2014 7:08 AM by 1005473

    Partition pruning not happening on timestamp field/attribute.

    1005473

      Hello:

       

      Below is table DDL statement.

       

      CREATE TABLE "Part_1"

        (

          "RecID"           NUMBER NOT NULL ENABLE,

          "UName"           VARCHAR2(255 BYTE),

          "ID"   NUMBER NOT NULL ENABLE,

          "NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE,

          "TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE,

          "TIME_ID" TIMESTAMP (6)WITH LOCAL TIME ZONE NOT NULL ENABLE,

        )

        PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP LOGGING STORAGE

        (

          BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

        )

        TABLESPACE "App1" LOB

       

        PARTITION BY RANGE

        (

          "TIME_ID"

        )

        SUBPARTITION BY LIST

        (

          "ID"

        )

        (

      ...

       

      SQL Query:

       

      select UName,

      NAME,

      TYPE,

      id

      from Part_1

      where ( TIME_ID BETWEEN '25-DEC-13 05.00.00.000000000 PM' AND '26-DEC-13 05.00.00.000000000 PM' );

      Note: Parameters '25-DEC-13 05.00.00.000000000 PM' and '26-DEC-13 05.00.00.000000000 PM' are getting from UI/APEX and its in timestamp format.

       

      Execution Plan:

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

      | Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

      |   0 | SELECT STATEMENT         |           | 20632 |   906K| 31672   (1)| 00:06:21 |       |       |        |      |            |

      |*  1 |  PX COORDINATOR          |           |       |       |            |          |       |       |        |      |            |

      |   2 |   PX SEND QC (RANDOM)    | :TQ10000  | 20632 |   906K| 31672   (1)| 00:06:21 |       |       |  Q1,00 | P->S | QC (RAND)  |

      |*  3 |    FILTER                |           |       |       |            |          |       |       |  Q1,00 | PCWC |            |

      |   4 |     PX PARTITION LIST ALL|           | 20632 |   906K| 31672   (1)| 00:06:21 |     1 |  LAST |  Q1,00 | PCWC |            |

      |*  5 |      TABLE ACCESS FULL   | Part_1 | 20632 |   906K| 31672   (1)| 00:06:21 |   KEY |   KEY |  Q1,00 | PCWP |            |

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

       

      Please do let me know why partition pruning is not happening over here, as time_id and parameters are of timestamp data type..

      Here it shows dynamic partition pruning, however actually it doesn't prune the partitions.

      Anything missing in the query? Please suggest.

       

      Thanks

      - a.m.

       

      -