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)

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索src
， and
， lenet iter 10000
UPPER
oracle 本地分区索引、oracle 分区索引、oracle 分区表 索引、oracle 创建分区索引、oracle 重建分区索引，以便于您获取更多的相关知识。