结论with as 固化的结果集再添加条件再次使用的时候是可以使用原表的索引的。
测试步骤如下:
1.构建测试表
create table dbmgr.TEST_APPEND as select * from dba_objects ;
insert into dbmgr.TEST_APPEND select * from dbmgr.TEST_APPEND ;
select count(*) from dbmgr.TEST_APPEND ;
---8125900
2.创建索引
create index dbmgr.test_withas on dbmgr.test_append(object_name) ;
3.收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'dbmgr',tabname => 'TEST_APPEND') ;
PL/SQL procedure successfully completed
4.验证正常情况下索引可以使用。
select /* without with as aaaaaaaaaa*/ * from dbmgr.TEST_APPEND aa where object_name='I_USER1' ;
select sql_id,sql_text from v$sql m where m.SQL_TEXT like '%aaaaa%' ;
QL> select * from table(dbms_xplan.display_cursor('45wh03j2uhb86')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 45wh03j2uhb86, child number 0
-------------------------------------
select /*+ without with as aaaaaaaaaa*/ * from dbmgr.TEST_APPEND
where object_name='I_USER1'
Plan hash value: 1601588939
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 623 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_APPEND | 743 | 66127 | 623 (0)|
|* 2 | INDEX RANGE SCAN | TEST_WITHAS | 743 | | 6 (0)| <<<<<<<<<<<<<<<<<<<<可以看到正常可以使用索引
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='I_USER1')
20 rows selected
5.刷新shared_pool
SQL> alter system flush shared_pool ;
System altered
6.使用with as 后验证是否可以使用到索引
with test_wias as
(select owner, object_name, object_id, object_type from dbmgr.TEST_APPEND)
select /* without with as bbbbbbbbbbbb*/ *
from test_wias
where object_name = 'I_USER1';
select sql_id,sql_text from v$sql m where m.SQL_TEXT like '%bbbbbbbbbbbb%' ;
SQL> select * from table(dbms_xplan.display_cursor('c7dq7c6ddnhz6')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c7dq7c6ddnhz6, child number 0
-------------------------------------
with test_wias as (select owner,object_name,object_id,object_type from
dbmgr.TEST_APPEND ) select /* without with as bbbbbbbbbbbb*/ * from
test_wias where object_name='I_USER1'
Plan hash value: 1601588939
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 623 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_APPEND | 743 | 26748 | 623 (0)|
|* 2 | INDEX RANGE SCAN | TEST_WITHAS | 743 | | 6 (0)| <<<<<<<<<<<<<<<<<<<<<<<<<<<可以看到仍然可以使用索引
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='I_USER1')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
21 rows selected