WITH addDate AS(
     SELECT trunc(SYSDATE,'DD') +rownum/24*1 high_value
     FROM dual
     CONNECT BY ROWNUM <= 168
)
,mv_par as ( 
        SELECT table_name,to_date(SUBSTR(p.high_value, 11, 19), 'YYYY-MM-DD HH24:MI:SS') high_value
       FROM MV_USERPARTITIONS_PART p ---8w条记录
      ORDER BY table_name,high_value
)
SELECT part.table_name, d.high_value 
FROM addDate d,
    (SELECT table_name 
     FROM User_Tables  --80条记录 
    where not REGEXP_LIKE (substr(table_name,instr(table_name,'_',-1)+1) ,'^\d+$') and partitioned='YES' and table_name like 'STAT%_HOUR'
    ) part
WHERE NOT EXISTS(
      SELECT 1 FROM mv_par p WHERE p.table_name = part.table_name
             AND to_date(SUBSTR(p.high_value, 11, 19), 'YYYY-MM-DD HH24:MI:SS') >= d.high_value
)
ORDER BY d.high_value,  part.table_name
;
--查询时间15min+。--小弟愚笨不懂 怎么让User_Tables 视图不被扩展。
--我讲User_Tables  视图建一个物化视图(或者表)查询速度11s。求大神指导有没有什么hint 优化此查询。

解决方案 »

  1.   

    日常开发中不建议使用 hint,描述一下你的表结构、索引情况以及数据量吧。另外,贴一下执行计划
      

  2.   

    Plan hash value: 2721298687
     
    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                        |     1 |   196 |       |  1246   (1)| 00:00:23 |
    |   1 |  SORT ORDER BY                            |                        |     1 |   196 |       |  1246   (1)| 00:00:23 |
    |*  2 |   HASH JOIN                               |                        |     1 |   196 |       |  1245   (1)| 00:00:23 |
    |   3 |    MERGE JOIN CARTESIAN                   |                        |     1 |   141 |       |  1244   (1)| 00:00:23 |
    |*  4 |     HASH JOIN ANTI                        |                        |     1 |   128 |       |  1244   (1)| 00:00:23 |
    |   5 |      NESTED LOOPS OUTER                   |                        |     1 |   102 |       |    11   (0)| 00:00:01 |
    |   6 |       NESTED LOOPS OUTER                  |                        |     1 |    97 |       |    10   (0)| 00:00:01 |
    |   7 |        NESTED LOOPS OUTER                 |                        |     1 |    93 |       |     9   (0)| 00:00:01 |
    |   8 |         NESTED LOOPS OUTER                |                        |     1 |    85 |       |     8   (0)| 00:00:01 |
    |   9 |          NESTED LOOPS                     |                        |     1 |    71 |       |     7   (0)| 00:00:01 |
    |  10 |           NESTED LOOPS                    |                        |     1 |    67 |       |     6   (0)| 00:00:01 |
    |  11 |            MERGE JOIN CARTESIAN           |                        |     1 |    37 |       |     5   (0)| 00:00:01 |
    |  12 |             VIEW                          |                        |     1 |     6 |       |     3   (0)| 00:00:01 |
    |  13 |              COUNT                        |                        |       |       |       |            |          |
    |* 14 |               CONNECT BY WITHOUT FILTERING|                        |       |       |       |            |          |
    |  15 |                FAST DUAL                  |                        |     1 |       |       |     3   (0)| 00:00:01 |
    |  16 |             BUFFER SORT                   |                        |     1 |    31 |       |     5   (0)| 00:00:01 |
    |* 17 |              TABLE ACCESS BY INDEX ROWID  | OBJ$                   |     1 |    31 |       |     2   (0)| 00:00:01 |
    |* 18 |               INDEX RANGE SCAN            | I_OBJ2                 |     1 |       |       |     1   (0)| 00:00:01 |
    |* 19 |            TABLE ACCESS CLUSTER           | TAB$                   |     1 |    30 |       |     1   (0)| 00:00:01 |
    |* 20 |             INDEX UNIQUE SCAN             | I_OBJ#                 |     1 |       |       |     0   (0)| 00:00:01 |
    |  21 |           TABLE ACCESS CLUSTER            | TS$                    |     1 |     4 |       |     1   (0)| 00:00:01 |
    |* 22 |            INDEX UNIQUE SCAN              | I_TS#                  |     1 |       |       |     0   (0)| 00:00:01 |
    |  23 |          TABLE ACCESS CLUSTER             | SEG$                   |     1 |    14 |       |     1   (0)| 00:00:01 |
    |* 24 |           INDEX UNIQUE SCAN               | I_FILE#_BLOCK#         |     1 |       |       |     0   (0)| 00:00:01 |
    |* 25 |         INDEX RANGE SCAN                  | I_OBJ1                 |     1 |     8 |       |     1   (0)| 00:00:01 |
    |* 26 |        INDEX RANGE SCAN                   | I_USER2                |     1 |     4 |       |     1   (0)| 00:00:01 |
    |* 27 |       INDEX RANGE SCAN                    | I_OBJ1                 |     1 |     5 |       |     1   (0)| 00:00:01 |
    |  28 |      VIEW                                 |                        |  7335 |   186K|       |  1233   (1)| 00:00:23 |
    |  29 |       SORT ORDER BY                       |                        |  7335 |    14M|    14M|  1233   (1)| 00:00:23 |
    |  30 |        MAT_VIEW ACCESS BY INDEX ROWID     | MV_USERPARTITIONS_PART |  7335 |    14M|       |     6   (0)| 00:00:01 |
    |* 31 |         INDEX RANGE SCAN                  | CCCCCINDX              |  1320 |       |       |     2   (0)| 00:00:01 |
    |  32 |     BUFFER SORT                           |                        |   100 |  1300 |       |  1244   (1)| 00:00:23 |
    |  33 |      FIXED TABLE FULL                     | X$KSPPCV               |   100 |  1300 |       |     0   (0)| 00:00:01 |
    |* 34 |    FIXED TABLE FULL                       | X$KSPPI                |     1 |    55 |       |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("KSPPI"."INDX"="KSPPCV"."INDX")
       4 - access("P"."TABLE_NAME"="O"."NAME")
           filter(TO_DATE(SUBSTR(INTERNAL_FUNCTION("P"."HIGH_VALUE"),11,19),'YYYY-MM-DD 
                  HH24:MI:SS')>=INTERNAL_FUNCTION("D"."HIGH_VALUE"))
      14 - filter(ROWNUM<=168)
      17 - filter(BITAND("O"."FLAGS",128)=0)
      18 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME" LIKE 'STAT%_HOUR')
           filter("O"."NAME" LIKE 'STAT%_HOUR' AND  NOT REGEXP_LIKE 
                  (SUBSTR("O"."NAME",INSTR("O"."NAME",'_',-1)+1),'^\d+$'))
      19 - filter(DECODE(BITAND("T"."PROPERTY",32),32,'YES','NO')='YES' AND BITAND("T"."PROPERTY",1)=0)
      20 - access("O"."OBJ#"="T"."OBJ#")
      22 - access("T"."TS#"="TS"."TS#")
      24 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+))
      25 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
      26 - access("CX"."OWNER#"="CU"."USER#"(+))
      27 - access("T"."BOBJ#"="CO"."OBJ#"(+))
      31 - access("TABLE_NAME" LIKE 'STAT%_HOUR')
           filter("TABLE_NAME" LIKE 'STAT%_HOUR')
      34 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')