My Oracle Support Banner

Partition Pruning Does Not Work In SQL Parse Phase When Unnecessary Change Line Character Is Included In Quotation Marks (Doc ID 2761515.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

    ====Bad SQL sql====

     PARTITIONS::
     PRUNED: 372 <<<<<<<<<<<<<<<
     ANALYZED: 372  UNANALYZED: 0


    ===Good sql plan====

     PARTITIONS::
     PRUNED: 2 <<<<<<<<<<<<<<<
     ANALYZED: 2  UNANALYZED: 0
    grant dba to <USERNAME> identified by <PASSWORD>;
    conn <USERNAME>/<PASSWORD>
    CREATE TABLE <TABLENAME>(
     s_productid  NUMBER,
     s_saledate   DATE,
     s_custid     NUMBER,
     s_totalprice NUMBER)
    PARTITION BY RANGE (s_saledate)
    (PARTITION sal99q1 VALUES LESS THAN
      (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
     PARTITION sal99q2 VALUES LESS THAN
      (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
     PARTITION sal99q3 VALUES LESS THAN
      (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
     PARTITION sal99q4 VALUES LESS THAN
      (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));

    SQL> SELECT * FROM <TABLENAME>
    WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', '
    DD-MON-YYYY'))
     AND (TO_DATE('01-OCT-1999', '
    DD-MON-YYYY')) AND s_productid = 1200;
     2    3    4    5
    no rows selected

    SQL> select * from table(dbms_xplan.display_cursor());


    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  42mpnn1739dkh, child number 0
    -------------------------------------
    SELECT * FROM <TABLENAME> WHERE s_saledate BETWEEN
    (TO_DATE('01-JUL-1999', ' DD-MON-YYYY'))   AND (TO_DATE('01-OCT-1999',
    ' DD-MON-YYYY')) AND s_productid = 1200

    Plan hash value: 386830797

    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |             |       |       |     2 (100)|          |       |       |
    |*  1 |  FILTER                   |             |       |       |            |          |       |       |
    |   2 |   PARTITION RANGE ITERATOR|             |     1 |    48 |     2   (0)| 00:00:01 |   KEY |   KEY |<<<<<<<<<<<<<<<Did NOT Pruned during SQL parse phase
    |*  3 |    TABLE ACCESS FULL      | <TABLENAME> |     1 |    48 |     2   (0)| 00:00:01 |   KEY |   KEY |
    ---------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

      1 - filter(TO_DATE('01-OCT-1999',' DD-MON-YYYY')>=TO_DATE('01-JUL-1999',' DD-MON-YYYY'))
      3 - filter(("S_PRODUCTID"=1200 AND "S_SALEDATE">=TO_DATE('01-JUL-1999',' DD-MON-YYYY') AND
                 "S_SALEDATE"<=TO_DATE('01-OCT-1999',' DD-MON-YYYY')))

    Note
    -----
      - dynamic statistics used: dynamic sampling (level=2)


    28 rows selected.

    SQL> SQL> SELECT * FROM <TABLENAME>
    WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))
     AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;
     2    3
    no rows selected

    SQL> select * from table(dbms_xplan.display_cursor());

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  a5v2535gwtxu3, child number 0
    -------------------------------------
    SELECT * FROM <TABLENAME> WHERE s_saledate BETWEEN
    (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))   AND (TO_DATE('01-OCT-1999',
    'DD-MON-YYYY')) AND s_productid = 1200

    Plan hash value: 3978535265

    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |             |       |       |     2 (100)|          |       |       |
    |   1 |  PARTITION RANGE ITERATOR|             |     1 |    48 |     2   (0)| 00:00:01 |     3 |     4 |<<<<<<<<<<<<<<<Pruned during SQL parse phase
    |*  2 |   TABLE ACCESS FULL      | <TABLENAME> |     1 |    48 |     2   (0)| 00:00:01 |     3 |     4 |
    --------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

      2 - filter(("S_PRODUCTID"=1200 AND "S_SALEDATE"<=TO_DATE(' 1999-10-01 00:00:00',
                 'syyyy-mm-dd hh24:mi:ss')))

    Note
    -----
      - dynamic statistics used: dynamic sampling (level=2)


    26 rows selected.

Changes

 Added Change Line Character within Quotation Marks

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.