Forum Stats

  • 3,769,603 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Tuning a partitioned table query

User_FRTCM
User_FRTCM Member Posts: 116 Blue Ribbon
edited Nov 1, 2018 4:21AM in SQL & PL/SQL

Hi,

I need tuning recommendations for the below query which is to run on a partitioned table, please share your ideas.

SELECT primary_id

FROM   schema.table

WHERE  create_date>=to_timestamp('2018-10-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND    create_date<to_timestamp('2018-10-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

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

| Id  | Operation                                                            | Name       | Rows  | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                                          |                 |  1075K|    50M|   104K (1) | 00:20:52 |       |       |

|   1 |  PARTITION RANGE SINGLE                              |                  |  1075K|    50M|   104K (1) | 00:20:52 |    90 |    90 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID     | table         |  1075K|    50M|   104K (1) | 00:20:52 |    90 |    90 |

|*  3 |    INDEX RANGE SCAN                                      | IDX03        |   487K|            |  2980 (1) | 00:00:36 |    90 |    90 |

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

Predicate Information (identified by operation id):

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

   3 - access("CREATE_DATE">=TIMESTAMP' 2018-10-25 00:00:00.000000000' AND "CREATE_DATE"<TIMESTAMP' 2018-10-26

      00:00:00.000000000')

16 rows selected.

DB Version - 11.2.0.4.0

Statistics -

TABLE_NAME        LAST_ANALYZED

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

Table                       25-OCT-18

Tagged:
AndrewSayer
«1

Answers

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Oct 31, 2018 7:50AM

    Why you are looking for improvement? Is it performing badly? What is the data type of create_date column ? If it is a date then use to_date.

    _

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon
    edited Oct 31, 2018 7:52AM

    Query is taking too long to pump output, the date column is a timestamp.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy
    edited Oct 31, 2018 7:53AM

    Well, better than an index range scan I don't see what you can get on such a table with such filter conditions.

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon
    edited Oct 31, 2018 7:55AM

    any better way to write this query ? This is a nested query, I have to do a left outer join with its output on another table

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Oct 31, 2018 7:57AM

    Post your table structure, index related information and full execution plan with predicates. Mention your database version. Tell us about the statistics gathering.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy
    edited Oct 31, 2018 7:57AM

    You mean you have large amounts of data for that given period. What about having data extracted for several small periods instead of one large period? Maybe you may use dbms_parallel_execute and split the large period into several samller periods to have data chunked for the smaller periods. This depends on how you need to get the data. If you need that in some file(s) - which I believe it may be the case for a large amount of data -, then you may produce several small files using dbms_parallel_execute and than merge the data in the smaller files into one large file if only one large file is required.

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon
    edited Oct 31, 2018 8:03AM

    Name                        Null?              Type

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

    Primary_ID              NOT NULL      VARCHAR2(40)

    CREATE_DATE        NOT NULL      TIMESTAMP(6)

    Plan Details

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

    | Id  | Operation                                                                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

    |   0 | SELECT STATEMENT                                             |                           |  1075K|    50M |   104K (1)| 00:20:52 |       |       |

    |   1 |  PARTITION RANGE SINGLE                                  |                           |  1075K|    50M |   104K (1)| 00:20:52 |    90 |    90 |

    |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID         | Table                 |  1075K|    50M|   104K (1)| 00:20:52 |    90 |    90 |

    |*  3 |    INDEX RANGE SCAN                                          | IDX03                |   487K|              |  2980 (1)| 00:00:36 |    90 |    90 |

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

    Predicate Information (identified by operation id):

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

       3 - access("CREATE_DATE">=TIMESTAMP' 2018-10-25 00:00:00.000000000' AND "CREATE_DATE"<TIMESTAMP' 2018-10-26

          00:00:00.000000000')

    16 rows selected.

    DB Version - 11.2.0.4.0

    Statistics -

    TABLE_NAME        LAST_ANALYZED

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

    Table                       25-OCT-18

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon
    edited Oct 31, 2018 8:04AM

    Already had it in mind but it is not feasible, as we are in migration stage, need to come up with the most optimal solution.

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon
    edited Oct 31, 2018 8:07AM

    Final Query Plan :-

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

    | Id  | Operation                                                                  | Name | Rows  | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

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

    |   0 | SELECT STATEMENT                                                             | |    36 |  8928 |   104K  (1)| 00:20:52 | | |

    |   1 |  SORT GROUP BY                                                                  | |    36 |  8928 |   104K  (1)| 00:20:52 | | |

    |*  2 |   HASH JOIN RIGHT OUTER                                                  | |  1075K|   254M|   104K  (1)| 00:20:52 | | |

    |   3 |    PARTITION RANGE SINGLE                                                | |    10 |  1990 |     7   (0)| 00:00:01 |    90 |    90 |

    |*  4 |     TABLE ACCESS FULL                                           | Table2 |    10 |  1990 |     7   (0)| 00:00:01 |    90 |    90 |

    |   5 |    PARTITION RANGE SINGLE                                                | |  1075K|    50M|   104K  (1)| 00:20:52 |    90 |    90 |

    |   6 |     TABLE ACCESS BY LOCAL INDEX ROWID          | Table     |  1075K|    50M|   104K  (1)| 00:20:52 |    90 |    90 |

    |*  7 |      INDEX RANGE SCAN                                           | IDX03  |   487K| |  2980   (1)| 00:00:36 |    90 |    90 |

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

    Predicate Information (identified by operation id):

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

       2 - access("ID"="Table2"."ID"(+))

       4 - filter("CREATE_DATE"(+)>=TIMESTAMP' 2018-10-25 00:00:00.000000000' AND "CREATE_DATE"(+)<TIMESTAMP'

          2018-10-26 00:00:00.000000000')

       7 - access("CREATE_DATE">=TIMESTAMP' 2018-10-25 00:00:00.000000000' AND "CREATE_DATE"<TIMESTAMP' 2018-10-26

          00:00:00.000000000')

  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy
    edited Oct 31, 2018 8:14AM

    An index on table2.id may help. That way a full table scan on table2 will be avoided. But, while an index will help some queries, it will make transaction processing slower because that index will have to be updated.