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 优化此查询。
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 优化此查询。
----------------------------------------------------------------------------------------------------------------------------
| 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')