【Analytic】使用MAX分析函式高效獲取每個Schema下最後被分析的表名
有關MAX分析函式的使用方法和介紹請參見文章《【Analytic】分析函式之MAX函式》（http://space.itpub.net/519536/viewspace-624749）。
這裡給出一個使用MAX函式高效獲取每個Schema下被分析的最後一個表名的實際應用案例。
1.分析
1）首先構造一個子查詢，保證子查詢能夠按照owner分組後獲得每組中last_analyzed欄位的最大值。
2）在外層查詢中給出獲得最大值的那些資訊。
2.實現
為了下面獲得簡潔的SQL執行計劃，我們這裡不在dba_tables上直接操作，而是構造一個資料內容一致的t_dba_tables表作為查詢的基表。
SQL> create table t_dba_tables as select * from dba_tables;
Table created.
按照我們的思路，實現的SQL語句如下。
select owner, table_name, last_time
from ( select t.*, max(last_analyzed) over (partition by owner) last_time
from t_dba_tables t
)
where last_analyzed = last_time
/
上述SQL語句的執行結果如下：
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
CTXSYS DR$STATS 2010-09-15 22:39:22
DBSNMP MGMT_SNAPSHOT 2007-04-17 03:57:24
DBSNMP MGMT_SNAPSHOT_SQL 2007-04-17 03:57:24
DBSNMP MGMT_RESPONSE_CONFIG 2007-04-17 03:57:24
DBSNMP MGMT_LATEST_SQL 2007-04-17 03:57:24
DMSYS DM$P_MODEL 2007-04-17 04:12:11
DMSYS DM$P_MODEL_TABLES 2007-04-17 04:12:11
EXFSYS RLM4J$ATTRALIASES 2007-04-17 04:17:01
EXFSYS RLM$VALIDPRIVS 2007-04-17 04:17:01
EXFSYS RLM$SCHACTLIST 2007-04-17 04:17:01
EXFSYS SYS_IOT_OVER_42513 2007-04-17 04:17:01
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
EXFSYS RLM4J$EVTSTRUCTS 2007-04-17 04:17:01
EXFSYS SYS_IOT_OVER_42516 2007-04-17 04:17:01
EXFSYS RLM4J$RULESET 2007-04-17 04:17:01
EXFSYS SYS_IOT_OVER_42519 2007-04-17 04:17:01
HR JOB_HISTORY 2010-09-14 11:00:41
HR JOBS 2010-09-14 11:00:41
HR EMPLOYEES 2010-09-14 11:00:41
HR DEPARTMENTS 2010-09-14 11:00:41
IX ORDERS_QUEUETABLE 2010-09-14 22:50:47
IX STREAMS_QUEUE_TABLE 2010-09-14 22:50:47
MDSYS SDO_COORD_OP_PARAM_VALS 2010-09-14 22:51:01
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
OE CUSTOMERS 2010-09-14 22:51:03
OLAPSYS CWM2$DIMENSION 2010-09-14 22:51:03
OLAPSYS CWM2$LEVEL 2010-09-14 22:51:03
OLAPSYS CWM2$HIERARCHY 2010-09-14 22:51:03
OLAPSYS CWM2$DIMENSIONATTRIBUTE 2010-09-14 22:51:03
OLAPSYS CWM2$LEVELATTRIBUTE 2010-09-14 22:51:03
OLAPSYS CWM2$CUBE 2010-09-14 22:51:03
OLAPSYS CWM2$MEASURE 2010-09-14 22:51:03
ORDSYS SI_IMAGE_FORMATS_TAB 2007-04-17 04:26:29
ORDSYS SI_VALUES_TAB 2007-04-17 04:26:29
OUTLN OL$NODES 2007-04-17 03:57:25
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
OUTLN OL$HINTS 2007-04-17 03:57:25
PM PRINT_MEDIA 2010-09-14 22:51:00
PM ONLINE_MEDIA 2010-09-14 22:51:00
SCOTT SALGRADE 2010-09-14 22:50:47
SCOTT BONUS 2010-09-14 22:50:47
SCOTT EMP 2010-09-14 22:50:47
SCOTT DEPT 2010-09-14 22:50:47
SH MVIEW$_EXCEPTIONS 2010-09-14 22:51:03
SYS SEQ$ 2010-09-18 07:04:10
SYSMAN MGMT_USER_TYPE_METRIC_PREFS 2007-04-17 04:49:54
SYSMAN MGMT_USER_FOLDERS 2007-04-17 04:49:54
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
SYSMAN MGMT_USER_PREFERENCES 2007-04-17 04:49:54
SYSMAN MGMT_USER_TARGETS 2007-04-17 04:49:54
SYSMAN MGMT_USER_JOBS 2007-04-17 04:49:54
SYSMAN MGMT_USER_CONTEXT 2007-04-17 04:49:54
SYSMAN MGMT_USER_CALLBACKS 2007-04-17 04:49:54
SYSMAN MGMT_VIEW_USER_CREDENTIALS 2007-04-17 04:49:54
SYSMAN MGMT_UPDATE_OPERATIONS_DETAILS 2007-04-17 04:49:54
SYSMAN MGMT_UPDATE_THRESHOLDS_DATA 2007-04-17 04:49:54
SYSMAN MGMT_UPDATE_PROPERTIES_DATA 2007-04-17 04:49:54
SYSMAN MGMT_VERSIONS 2007-04-17 04:49:54
SYSTEM AQ$_QUEUE_TABLES 2010-09-14 11:05:38
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
SYSTEM AQ$_QUEUES 2010-09-14 11:05:38
SYSTEM MVIEW$_ADV_INDEX 2010-09-14 11:05:38
SYSTEM MVIEW$_ADV_PARTITION 2010-09-14 11:05:38
TSMSYS SRS$ 2010-09-14 22:50:46
WMSYS WM$WORKSPACES_TABLE 2007-04-17 04:01:05
WMSYS WM$VERSION_TABLE 2007-04-17 04:01:05
WMSYS WM$WORKSPACE_PRIV_TABLE 2007-04-17 04:01:05
WMSYS WM$WORKSPACE_SAVEPOINTS_TABLE 2007-04-17 04:01:05
WMSYS WM$VT_ERRORS_TABLE 2007-04-17 04:01:05
XDB XDB$H_INDEX 2010-09-14 22:50:58
65 rows selected.
上面結果便是我們想要的最終結果。
3.效率分析
為什麼說使用分析函式可以給我們帶來比較高的查詢效率呢？
請見SQL語句的執行計劃:
SQL> set autot trace explain;
SQL> select owner, table_name, last_time
2 from ( select t.*, max(last_analyzed) over (partition by owner) last_time
3 from t_dba_tables t
4 )
5 where last_analyzed = last_time
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3988658493
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1581 | 82212 | 14 (15)| 00:00:01 |
|* 1 | VIEW | | 1581 | 82212 | 14 (15)| 00:00:01 |
| 2 | WINDOW SORT | | 1581 | 67983 | 14 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_DBA_TABLES | 1581 | 67983 | 12 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_ANALYZED"="LAST_TIME")
Note
-----
- dynamic sampling used for this statement
執行計劃中給出了僅需一次全表掃描的的執行路徑，這就是效率高的根本原因。留給大家一個思考：有興趣的朋友可以根據這個需求換幾種其他實現方法，比較一下SQL的執行計劃。
4.小結
為了提高SQL的效能，我們應該本著“無所不用其極”的人生態度。在一個需求出現在我們面前的時候，思考一下，一共會有多少種實現方案，這些方案中哪個是效率最高的？比較之後便會有收穫和提高，雖然這個過程需要一些時間成本，但“價值來源於積累”，這些都是值得的。
Good luck.
secooler
10.09.17
-- The End --
這裡給出一個使用MAX函式高效獲取每個Schema下被分析的最後一個表名的實際應用案例。
1.分析
1）首先構造一個子查詢，保證子查詢能夠按照owner分組後獲得每組中last_analyzed欄位的最大值。
2）在外層查詢中給出獲得最大值的那些資訊。
2.實現
為了下面獲得簡潔的SQL執行計劃，我們這裡不在dba_tables上直接操作，而是構造一個資料內容一致的t_dba_tables表作為查詢的基表。
SQL> create table t_dba_tables as select * from dba_tables;
Table created.
按照我們的思路，實現的SQL語句如下。
select owner, table_name, last_time
from ( select t.*, max(last_analyzed) over (partition by owner) last_time
from t_dba_tables t
)
where last_analyzed = last_time
/
上述SQL語句的執行結果如下：
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
CTXSYS DR$STATS 2010-09-15 22:39:22
DBSNMP MGMT_SNAPSHOT 2007-04-17 03:57:24
DBSNMP MGMT_SNAPSHOT_SQL 2007-04-17 03:57:24
DBSNMP MGMT_RESPONSE_CONFIG 2007-04-17 03:57:24
DBSNMP MGMT_LATEST_SQL 2007-04-17 03:57:24
DMSYS DM$P_MODEL 2007-04-17 04:12:11
DMSYS DM$P_MODEL_TABLES 2007-04-17 04:12:11
EXFSYS RLM4J$ATTRALIASES 2007-04-17 04:17:01
EXFSYS RLM$VALIDPRIVS 2007-04-17 04:17:01
EXFSYS RLM$SCHACTLIST 2007-04-17 04:17:01
EXFSYS SYS_IOT_OVER_42513 2007-04-17 04:17:01
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
EXFSYS RLM4J$EVTSTRUCTS 2007-04-17 04:17:01
EXFSYS SYS_IOT_OVER_42516 2007-04-17 04:17:01
EXFSYS RLM4J$RULESET 2007-04-17 04:17:01
EXFSYS SYS_IOT_OVER_42519 2007-04-17 04:17:01
HR JOB_HISTORY 2010-09-14 11:00:41
HR JOBS 2010-09-14 11:00:41
HR EMPLOYEES 2010-09-14 11:00:41
HR DEPARTMENTS 2010-09-14 11:00:41
IX ORDERS_QUEUETABLE 2010-09-14 22:50:47
IX STREAMS_QUEUE_TABLE 2010-09-14 22:50:47
MDSYS SDO_COORD_OP_PARAM_VALS 2010-09-14 22:51:01
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
OE CUSTOMERS 2010-09-14 22:51:03
OLAPSYS CWM2$DIMENSION 2010-09-14 22:51:03
OLAPSYS CWM2$LEVEL 2010-09-14 22:51:03
OLAPSYS CWM2$HIERARCHY 2010-09-14 22:51:03
OLAPSYS CWM2$DIMENSIONATTRIBUTE 2010-09-14 22:51:03
OLAPSYS CWM2$LEVELATTRIBUTE 2010-09-14 22:51:03
OLAPSYS CWM2$CUBE 2010-09-14 22:51:03
OLAPSYS CWM2$MEASURE 2010-09-14 22:51:03
ORDSYS SI_IMAGE_FORMATS_TAB 2007-04-17 04:26:29
ORDSYS SI_VALUES_TAB 2007-04-17 04:26:29
OUTLN OL$NODES 2007-04-17 03:57:25
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
OUTLN OL$HINTS 2007-04-17 03:57:25
PM PRINT_MEDIA 2010-09-14 22:51:00
PM ONLINE_MEDIA 2010-09-14 22:51:00
SCOTT SALGRADE 2010-09-14 22:50:47
SCOTT BONUS 2010-09-14 22:50:47
SCOTT EMP 2010-09-14 22:50:47
SCOTT DEPT 2010-09-14 22:50:47
SH MVIEW$_EXCEPTIONS 2010-09-14 22:51:03
SYS SEQ$ 2010-09-18 07:04:10
SYSMAN MGMT_USER_TYPE_METRIC_PREFS 2007-04-17 04:49:54
SYSMAN MGMT_USER_FOLDERS 2007-04-17 04:49:54
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
SYSMAN MGMT_USER_PREFERENCES 2007-04-17 04:49:54
SYSMAN MGMT_USER_TARGETS 2007-04-17 04:49:54
SYSMAN MGMT_USER_JOBS 2007-04-17 04:49:54
SYSMAN MGMT_USER_CONTEXT 2007-04-17 04:49:54
SYSMAN MGMT_USER_CALLBACKS 2007-04-17 04:49:54
SYSMAN MGMT_VIEW_USER_CREDENTIALS 2007-04-17 04:49:54
SYSMAN MGMT_UPDATE_OPERATIONS_DETAILS 2007-04-17 04:49:54
SYSMAN MGMT_UPDATE_THRESHOLDS_DATA 2007-04-17 04:49:54
SYSMAN MGMT_UPDATE_PROPERTIES_DATA 2007-04-17 04:49:54
SYSMAN MGMT_VERSIONS 2007-04-17 04:49:54
SYSTEM AQ$_QUEUE_TABLES 2010-09-14 11:05:38
OWNER TABLE_NAME LAST_TIME
------------------------------ ------------------------------ -------------------
SYSTEM AQ$_QUEUES 2010-09-14 11:05:38
SYSTEM MVIEW$_ADV_INDEX 2010-09-14 11:05:38
SYSTEM MVIEW$_ADV_PARTITION 2010-09-14 11:05:38
TSMSYS SRS$ 2010-09-14 22:50:46
WMSYS WM$WORKSPACES_TABLE 2007-04-17 04:01:05
WMSYS WM$VERSION_TABLE 2007-04-17 04:01:05
WMSYS WM$WORKSPACE_PRIV_TABLE 2007-04-17 04:01:05
WMSYS WM$WORKSPACE_SAVEPOINTS_TABLE 2007-04-17 04:01:05
WMSYS WM$VT_ERRORS_TABLE 2007-04-17 04:01:05
XDB XDB$H_INDEX 2010-09-14 22:50:58
65 rows selected.
上面結果便是我們想要的最終結果。
3.效率分析
為什麼說使用分析函式可以給我們帶來比較高的查詢效率呢？
請見SQL語句的執行計劃:
SQL> set autot trace explain;
SQL> select owner, table_name, last_time
2 from ( select t.*, max(last_analyzed) over (partition by owner) last_time
3 from t_dba_tables t
4 )
5 where last_analyzed = last_time
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3988658493
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1581 | 82212 | 14 (15)| 00:00:01 |
|* 1 | VIEW | | 1581 | 82212 | 14 (15)| 00:00:01 |
| 2 | WINDOW SORT | | 1581 | 67983 | 14 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_DBA_TABLES | 1581 | 67983 | 12 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_ANALYZED"="LAST_TIME")
Note
-----
- dynamic sampling used for this statement
執行計劃中給出了僅需一次全表掃描的的執行路徑，這就是效率高的根本原因。留給大家一個思考：有興趣的朋友可以根據這個需求換幾種其他實現方法，比較一下SQL的執行計劃。
4.小結
為了提高SQL的效能，我們應該本著“無所不用其極”的人生態度。在一個需求出現在我們面前的時候，思考一下，一共會有多少種實現方案，這些方案中哪個是效率最高的？比較之後便會有收穫和提高，雖然這個過程需要一些時間成本，但“價值來源於積累”，這些都是值得的。
Good luck.
secooler
10.09.17
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-674347/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Analytic】分析函式之MAX函式函式
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之AVG函式函式
- 【Analytic】分析函式之RANK函式函式
- 【Analytic】分析函式之COUNT函式函式
- 【Analytic】分析函式之ROW_NUMBER函式函式
- 【Analytic】分析函式之DENSE_RANK函式函式
- 【Analytic】分析函式之FIRST_VALUE函式函式
- 【Analytic】分析函式之LAST_VALUE函式函式AST
- 不用分析函式求出每組前幾名函式
- JavaScript獲取每個月最後一天的日期JavaScript
- JS陣列at函式(獲取最後一個元素的方法)介紹JS陣列函式
- ORACLE HANDBOOK系列之一：Oracle分析函式（Analytic Function）Oracle函式Function
- php 獲取函式被呼叫位置PHP函式
- 核心分析PE獲取DLL匯出函式地址函式
- sqlserver時間函式獲取本月最後一天SQLServer函式
- 反射如何獲取函式的引數名反射函式
- PostgreSQL 函式獲取表DDLSQL函式
- 【Analytic】使用分析函式ROW_NUMBER輔助完成外來鍵的索引批量建立函式索引
- Oracle 分析函式的使用Oracle函式
- 函式: 獲得每月的最後一天函式
- [Oracle]高效的SQL語句之分析函式（三）OracleSQL函式
- [Mysql]檢視每個資料庫大小以及每個表最後的修改時間MySql資料庫
- 教你在Nodejs中如何獲取當前函式被呼叫的行數及檔名NodeJS函式
- Oracle 使用分析函式刪除表中的重複行Oracle函式
- 幾個分析函式的比較函式
- 透過Lambda函式的方式獲取屬性名稱函式
- Oracle獲取所有表名資訊和獲取指定表名欄位資訊Oracle
- Oracle分析函式七——分析函式案例Oracle函式
- PHP獲取陣列最後一個值PHP陣列
- JavaScript獲取陣列最後一個元素JavaScript陣列
- DB2_使用表函式獲取健康監視器快照DB2函式
- [Oracle]高效的SQL語句之分析函式（一）（二）OracleSQL函式
- Java——通過反射獲取函式引數名稱Java反射函式
- jQuery獲取class相同的div中的最後一個jQuery
- 一個使用getopt()函式獲取命令列引數的例子(轉)函式命令列
- 分析函式函式
- 使用bcc分析函式耗時函式