|
dingjun123@ORADB> drop table t;
Table dropped. Elapsed: 00:00:00.10 dingjun123@ORADB> create table t as select * from dba_objects; Table created. Elapsed: 00:00:00.25 创建索引： dingjun123@ORADB> create index idx_t on t(object_id); Index created. 收集统计信息： exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'dingjun123',tabname=>'t', estimate_percent=>null,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10); |
下面创建分别不带rownum和带rownum的视图看看会发生什么：
|
--不带rownum的view
dingjun123@ORADB> create view v1_t as select * from t; View created. --带rownum的view dingjun123@ORADB> create view v1_t_rownum(object_id,object_name,rn) as select object_id,object_name,rownum from t; View created. --简单view可以view merge谓词推入到视图里，走索引 dingjun123@ORADB> select * from v1_t where object_id = 100; 1 row selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1594971208 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 1611 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --带rownum的view,无法view merge,谓词推入不到视图里，无法走索引 dingjun123@ORADB> select * from v1_t_rownum where object_id = 100; 1 row selected. Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- Plan hash value: 4233993970 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 76816 | 6901K| 306 (1)| 00:00:04 | |* 1 | VIEW | V1_T_ROWNUM | 76816 | 6901K| 306 (1)| 00:00:04 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| T | 76816 | 2250K| 306 (1)| 00:00:04 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 1101 consistent gets 0 physical reads 0 redo size 676 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |