oracle中查询字段使用子查询导致不能使用索引的案例

一个客户反应视图的查询很慢,一直查询不出来,让我们帮忙分析一下.我看了一下视图的创建sql,类似于如下:

create or replace view view_test as
select 
a.a1,
a.a2,
(select b.b1 from b where a.a1=b.a1) as b1,
(select c.c1 from c where a.a1=c.c1) as c1,
...
from a;

当使用查询语句:

select * from view_test where b1='xxx'

的时候,查询非常缓慢,而在b表上b1字段是有索引的,而且索引的可选择性非常好,但是查看执行计划却不走索引.

这种写法其实是一个非常差的写法,一般新手可能会这么写,下面用示例说明:

1.创建两张表,分别插入10w和100w条数据,创建索引,并且收集统计信息,如下:

create sequence SEQ_DML_test
minvalue 1
maxvalue 9999999999999
start with 1000000000
increment by 1 nocycle;

create table test(id int primary key,name varchar2(30),price number,opertime date);
insert into test select rownum,'init'||rownum,ABS(MOD(DBMS_RANDOM.RANDOM,10000)),sysdate from dual connect by rownum<100001;
commit;
create index idx_test_price on test(price);

alter sequence SEQ_DML_TEST increment by -100000 nocache;
select SEQ_DML_TEST.nextval from dual;
alter sequence SEQ_DML_TEST increment by 1 cache 20;

create table test2(id int primary key,name varchar2(30),price number,opertime date);
insert into test2 select rownum,'init'||rownum,ABS(MOD(DBMS_RANDOM.RANDOM,10000)),sysdate from dual connect by rownum<1000001;
commit;
create index idx_test2_name on test2(name);

exec dbms_stats.gather_table_stats('SUQ','TEST');

exec dbms_stats.gather_table_stats('SUQ','TEST2');

注意:test2表中name字段是有索引的.两张表都有主键为id

 

2.创建两个不同的视图,分别执行做比对:

create or replace view
view_test
as 
select
a.id,
a.opertime,
(select b.name from test2 b where a.id=b.id) as name
from
test a;

 

create or replace view
view_test2
as 
select
a.id,
a.opertime,
b.name
from
test a,test2 b where a.id=b.id;

其中view_test使用上面的子查询的方式.view_test2使用的是关联查询的方式方式.

 

3.查询查看两个sql的不同的执行效率

注意我们通过test2的name字段进行过滤查询,看两个不同的执行计划,下面是第一个sql的执行计划:

 

SQL> select * from view_test where name='init9';


Execution Plan
----------------------------------------------------------
Plan hash value: 2742430834

-------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  |  9997 |   380K|    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2	  |	1 |    16 |	3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | SYS_C003979 |	1 |	  |	2   (0)| 00:00:01 |
|*  3 |  VIEW			    | VIEW_TEST   |  9997 |   380K|    13   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL	    | TEST	  |  9997 |   117K|    13   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."ID"=:B1)
   3 - filter("NAME"='init9')


Statistics
----------------------------------------------------------
	  8  recursive calls
	  0  db block gets
      22712  consistent gets
	  0  physical reads
	  0  redo size
	670  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

下面是第二个视图的执行计划:

SQL> select * from view_test2 where name='init9';


Execution Plan
----------------------------------------------------------
Plan hash value: 3906120439

------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	       |     1 |    28 |     5	 (0)| 00:00:01 |
|   1 |  NESTED LOOPS		      | 	       |     1 |    28 |     5	 (0)| 00:00:01 |
|   2 |   NESTED LOOPS		      | 	       |     1 |    28 |     5	 (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST2	       |     1 |    16 |     4	 (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN	      | IDX_TEST2_NAME |     1 |       |     3	 (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN	      | SYS_C003978    |     1 |       |     0	 (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | TEST	       |     1 |    12 |     1	 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."NAME"='init9')
   5 - access("A"."ID"="B"."ID")


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  8  consistent gets
	  0  physical reads
	  0  redo size
	670  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

我们可以很明显的看到,第一个sql的执行计划很差,对test2表进行了全表扫描,第二个sql的执行计划要好很多,使用了name字段上的索引.那是因为这种视图可以将查询条件进行下推,下推到表TEST2上.那么就可以使用test2上的索引.

而如果使用第一种视图则无法下推,还有一种情况不能下推的,见我的另外一篇文章:

https://blog.csdn.net/su377486/article/details/102733619

其实上面这种字段转换的情况是比较多的人遇到的.

而我这种案例其实很少会有人写这种sql,可能新手才会这么写^ ^