有关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博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2994052/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
