Oracle中一個通過添加本地分區索引提高SQL效能的案例

今天接到同事求助，說有一個select query，在Oracle上要跑一分多鐘，他希望能在5s內出結果，該sql如下：

Select  /*+ parallel(src, 8) */ distinct  src.systemname as systemname    ,  src.databasename as databasename    ,  src.tablename as tablename    ,  src.username as username  from  <strong>meta_dbql_table_usage_exp_hst</strong> src   inner <strong>join DR_QRY_LOG_EXP_HST</strong> rl on  <strong>src.acctstringdate = rl.acctstringdate    and src.queryid = rl.queryid</strong>    And Src.Systemname = Rl.Systemname    and src.acctstringdate > sysdate - 30    And Rl.Acctstringdate > Sysdate - 30   inner join  <strong>meta_dr_qry_log_tgt_all_hst </strong>tgt on  upper(tgt.systemname) = upper('MOZART')    And Upper(tgt.Databasename) = Upper('GDW_TABLES')    And Upper(tgt.Tablename) = Upper('SSA_SLNG_LSTG_MTRC_SD')    <strong>AND src.acctstringdate = tgt.acctstringdate    and rl.statement_id = tgt.statement_id</strong>    and rl.systemname = tgt.systemname    And Tgt.Acctstringdate > Sysdate - 30    And Not(      Upper(Tgt.Systemname)=Upper(src.systemname)      And    Upper(Tgt.Databasename) = Upper(Src.Databasename)      And    Upper(Tgt.Tablename) = Upper(Src.Tablename)      )    And   tgt.Systemname is not null  And   tgt.Databasename Is Not Null  And   tgt.tablename is not null;

SQL的簡單分析

總得來看，這個SQL就是三個表(meta_dbql_table_usage_exp_hst，DR_QRY_LOG_EXP_HST，meta_dr_qry_log_tgt_all_hst）的INNER JOIN，這三個表資料量都在百萬層級，且都是分區表（以acctstringdate為分區鍵），執行計畫如下：

------------------------------------------------------------------------------------------------------------------------  | Id  | Operation                              | Name                          | Rows  | Bytes | Cost  | Pstart| Pstop |  ------------------------------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT                       |                               |     1 |   159 |  8654 |       |       |  |   1 |  PX COORDINATOR                        |                               |       |       |       |       |       |  |   2 |   PX SEND QC (RANDOM)                  | :TQ10002                      |     1 |   159 |  8654 |       |       |  |   3 |    SORT UNIQUE                         |                               |     1 |   159 |  8654 |       |       |  |   4 |     PX RECEIVE                         |                               |     1 |    36 |     3 |       |       |  |   5 |      PX SEND HASH                      | :TQ10001                      |     1 |    36 |     3 |       |       |  |*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST            |     1 |    36 |     3 |       |       |  |   7 |        NESTED LOOPS                    |                               |     1 |   159 |  8633 |       |       |  |   8 |         NESTED LOOPS                   |                               |  8959 |  1076K|  4900 |       |       |  |   9 |          BUFFER SORT                   |                               |       |       |       |       |       |  |  10 |           PX RECEIVE                   |                               |       |       |       |       |       |  |  11 |            PX SEND BROADCAST           | :TQ10000                      |       |       |       |       |       |  |  12 |             PARTITION RANGE ITERATOR   |                               |     1 |    56 |  4746 |   KEY |    14 |  |* 13 |              TABLE ACCESS FULL         | META_DR_QRY_LOG_TGT_ALL_HST   |     1 |    56 |  4746 |   KEY |    14 |  |  14 |          PX BLOCK ITERATOR             |                               |  8959 |   586K|   154 |   KEY |   KEY |  |* 15 |           TABLE ACCESS FULL            | META_DBQL_TABLE_USAGE_EXP_HST |  8959 |   586K|   154 |   KEY |   KEY |  |  16 |         PARTITION RANGE ITERATOR       |                               |     1 |       |     2 |   KEY |   KEY |  |* 17 |          INDEX RANGE SCAN              | DR_QRY_LOG_EXP_HST_IDX        |     1 |       |     2 |   KEY |   KEY |  ------------------------------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------           6 - filter("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID" AND "RL"."SYSTEMNAME"="TGT"."SYSTEMNAME" AND "SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")    13 - filter(UPPER("TGT"."SYSTEMNAME")='MOZART' AND UPPER("TGT"."DATABASENAME")='GDW_TABLES' AND              UPPER("TGT"."TABLENAME")='SSA_SLNG_LSTG_MTRC_SD' AND "TGT"."ACCTSTRINGDATE">SYSDATE@!-30 AND "TGT"."SYSTEMNAME" IS NOT NULL              "TGT"."DATABASENAME" IS NOT NULL AND "TGT"."TABLENAME" IS NOT NULL)    15 - filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE" AND (UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME") OR              UPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME") OR UPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME")) AND              "SRC"."ACCTSTRINGDATE">SYSDATE@!-30)    17 - access("SRC"."QUERYID"="RL"."QUERYID" AND "SRC"."ACCTSTRINGDATE"="RL"."ACCTSTRINGDATE")         filter("RL"."ACCTSTRINGDATE">SYSDATE@!-30)