【SQL】子查询中有rownum，导致全表扫描，效率极

现象：

视图中有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
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/42105477
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/42105477