Oracle案例：通过添加本地分区索引提高SQL性能

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
;