作者:马立和 高振娇 韩锋
来源:大数据DT(ID:hzdashuju)
内容摘编自《数据库高效优化:架构、规范与SQL技巧》
select table_name,index_name,leaf_blocks,num_rows,clustering_factor from user_indexes where table_name in ('T1','T2'); TABLE_NAME INDEX_NAME LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR -------------- -------------- ---------------- ---------- --------------------- T1 SYS_C0025294 6275 3200000 31520 T2 SYS_C0025295 13271 3200000 632615
select * from t2 where id between '3199990' and '3200000'; -------------------------------------------------------------------------------- | Id | Operation | Name |Rows|Bytes |Cost(%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6| 390 | 5 (0)|00:00:01| | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 6| 390 | 5 (0)|00:00:01| |* 2 | INDEX RANGE SCAN | SYS_C0025295 | 6| | 3 (0)|00:00:01| -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 13 consistent gets 0 physical reads
案例03 规范SQL写法好处多
1. 案例说明select ... from ... where ( ( order_creation_date>= to_date(20120208,'yyyy-mm-dd') and order_creation_date<to_date(20120209,'yyyy-mm-dd') ) or ( send_date>= to_date(20120208,'yyyy-mm-dd') and send_date<to_date(20120209, 'yyyy-mm-dd') ) ) andnvl(a.bd_id,0) = 1 -------------------------------------------------------------------------------- | Id | Operation | Name |Cost (%CPU)| Time |Pstart | Pstop | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2470K(100)| | | | | 1 | SORT GROUP BY | | | | | | | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | XXXX | 5 (0) | 00:00:01 | ROW L | ROW L | | 3 | NESTED LOOPS | | 2470K (1) | 08:14:11 | | | | 4 | VIEW |VW_NSO_1| 2470K (1) | 08:14:10 | | | | 5 | FILTER | | | | | | | 6 | HASH GROUP BY | | 2470K (1)| 08:14:10 | | | | 7 | TABLE ACCESS BY GLOBAL INDEX ROWID | XXXX | 5 (0)| 00:00:01 | ROW L | ROW L | | 8 | NESTED LOOPS | | 2470K (1)| 08:14:10 | | | | 9 | SORT UNIQUE | | 2340K (2)| 07:48:11 | | | | 10 | PARTITION RANGE ALL | | 2340K (2)| 07:48:11 | 1 | 92 | | 11 | TABLE ACCESS FULL | XXXX | 2340K (2)| 07:48:11 | 1 | 92 | | 12 | INDEX RANGE SCAN | XXXX | 3 (0)| 00:00:01 | | | | 13 | INDEX RANGE SCAN | XXXX | 3 (0)| 00:00:01 | | | --------------------------------------------------------------------------------
select ... from ... where order_creation_date >= to_date(20120208,'yyyy-mm-dd') and order_creation_date<to_date(20120209,'yyyy-mm-dd') union all select ... from ... where send_date>= to_date(20120208,'yyyy-mm-dd') and send_date<to_date(20120209,'yyyy-mm-dd') and nvl(a.bd_id,0) = 5
select ... from ... where ( ( order_creation_date>= to_date(20120208,'yyyymmdd') and order_creation_date<to_date(20120209,'yyyymmdd') ) or ( send_date>= to_date(20120208,'yyyymmdd') and send_date<to_date(20120209,'yyyymmdd') ) ); -------------------------------------------------------------------------------- | Id | Operation | Name | Cost(%CPU)|Time | Pstart | Pstop | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 42358 (1)| 00:08:29 | | | | 1 | SORT AGGREGATE | | | | | | | 2 | CONCATENATION | | | | | | | 3 | PARTITION RANGE SINGLE | | 17393 (1)| 00:03:29 | 57 | 57 | |* 4 | TABLE ACCESS FULL | XXXX | 17393 (1)| 00:03:29 | 57 | 57 | |* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID | XXXX | 24966 (1)| 00:05:00 | ROWID | ROWID | |* 6 | INDEX RANGE SCAN | XXXX | 658 (1)| 00:00:08 | | | ---------------------------------------------------------------------------------
select... from xxx a join xxx b on a.order_id = b.lyywzdid left join xxx c on b.gysid = c.gysid whereb.cdate>= to_date('2012-03-31', 'yyyy-mm-dd') – 3 and ... a.send_date>= to_date('2012-03-31', 'yyyy-mm-dd') - 1 and a.send_date<to_date('2012-03-31', 'yyyy-mm-dd'); -------------------------------------------------------------------------------- |Id | Operation |Name | Rows | Bytes | Cost (%CPU) |Pstart|Pstop| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 104 | 9743(1)| | | | 1 | HASH JOIN OUTER | | 1 | 104 | 9743(1)| | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | XXXX | 1 | 22 | 0(0)| 1189 | 1189| | 3 | NESTED LOOPS | | 1 | 94 | 9739(1)| | | | 4 | PARTITION RANGE ITERATOR | | 1032 | 74304 | 9739(1)| 123 | 518 | | 5 | TABLE ACCESS FULL | XXXX | 1032 | 74304 | 9739(1)| 123 | 518 | | 6 | PARTITION RANGE SINGLE | | 1 | | 0(0)| 1189 | 1189 | | 7 | INDEX RANGE SCAN | XXXX | 1 | | 0(0)| 1189 | 1189 | | 8 | TABLE ACCESS FULL | XXXX | 183 | 1830 | 3(0)| | | --------------------------------------------------------------------------------
exec dbms_stats.gather_index_stats( ownname=>'xxx', indname=>'xxx', partname=>'PART_xxx', estimate_percent => 10);
数据分析咨询请扫描二维码