今天接到同事求助，说有一个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  meta_dbql_table_usage_exp_hst src
 inner join DR_QRY_LOG_EXP_HST rl on
  src.acctstringdate = rl.acctstringdate
  and src.queryid = rl.queryid
  And Src.Systemname = Rl.Systemname
  and src.acctstringdate > sysdate - 30
  And Rl.Acctstringdate > Sysdate - 30
 inner join  meta_dr_qry_log_tgt_all_hst tgt on
  upper(tgt.systemname) = upper('MOZART')
  And Upper(tgt.Databasename) = Upper('GDW_TABLES')
  And Upper(tgt.Tablename) = Upper('SSA_SLNG_LSTG_MTRC_SD')
  AND src.acctstringdate = tgt.acctstringdate
  and rl.statement_id = tgt.statement_id
  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
;

您可能感兴趣的文章: