现象:
视图中有rownum,导致在条件查询视图时用不到索引。
如视图:
CREATE ORREPLACE FORCE VIEW V_BILL ( ID, VSLNAME, VOYAGE, BLNO, WEIGHT, PACKAGE ) AS SELECT rownum, vslname, voyage, blno, weight, package FROM clp_bill; |
查看执行计划
SQL> select * from V _BILL where vslname='SA SCH' AND voyage='1410' ANDblno='590';
Elapsed: 00:00:01.85
Execution Plan
----------------------------------------------------------
Plan hash value: 591072819
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2101K| 154M| 3789 (10)| 00:00:46 |
|* 1 | VIEW | V_CLP_BILL | 2101K| 154M| 3789 (10)| 00:00:46 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| CLP_BILL | 2101K| 84M| 3789 (10)| 00:00:46 |
----------------------------------------------------------------------------------
走的是全表扫描
修改视图如下:
CREATE ORREPLACE FORCE VIEW V_BILL ( VSLNAME, VOYAGE, BLNO, WEIGHT, PACKAGE ) AS SELECT vslname, voyage, blno, weight, package FROM clp_bill;
|
查看执行计划
SQL> select * from V _BILL where vslname='SA SCH' AND voyage='1410' ANDblno='590';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 233001863
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLP_BILL | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UU_CLP_BILL_VSLVOYBLNO | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
走索引了。
猜想
由于视图就是去执行查询语句,查询视图也就是把视图里的内容放在子查询里,即select * from (视图查询语句)。因此猜想rownum在子查询,而过滤条件又在外面的查询中时会导致索引失效。
--子查询有rownum,过滤条件在子查询外,走全表扫描
SQL> select * from (selectrownum,VSLNAME,VOYAGE, BLNO,WEIGHT,PACKAGE from clp_bill)where vslname='SA SCH'AND voyage='1410' AND blno='590';
Elapsed: 00:00:02.46
Execution Plan
----------------------------------------------------------
Plan hash value: 943948639
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2101K| 154M| 3789 (10)| 00:00:46 |
|* 1 | VIEW | | 2101K| 154M| 3789 (10)| 00:00:46 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| CLP_BILL | 2101K| 84M| 3789 (10)| 00:00:46 |
--------------------------------------------------------------------------------
--子查询没有rownum,过滤条件在子查询外,走索引
SQL> select * from (selectVSLNAME,VOYAGE, BLNO,WEIGHT,PACKAGE from clp_bill) where vslname='SA SCH' AND voyage='1410' ANDblno='590';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 233001863
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLP_BILL | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UU_CLP_BILL_VSLVOYBLNO | 1| | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
--子查询有rownum,过滤条件在子查询内,走索引
SQL> select * from (selectROWNUM,VSLNAME,VOYAGE, BLNO,WEIGHT,PACKAGE from clp_bill where vslname='SA SCH'AND voyage='1410' AND blno='590');
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2033979732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 77 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CLP_BILL | 1 | 42 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | UU_CLP_BILL_VSLVOYBLNO | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
结论
这个实验证明了我刚刚的猜测。
因为rownum是伪列,在查询的时候会计算rownum,所以在子查询中没有加过滤条件时,会全表扫描去计算rownum,直到找符合过滤条件的数据。(过滤条件在子查询里与外是的rownum是不同的)
因此在查询中使用rownum要注意效率问题!
Sharon
2014.12.23
----------------------------------------------------------------------------------------------
http://blog.csdn.net/sharqueen_wu/article/details/42105477