with as 固化的结果集是否可以使用原表的索引？

结论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