4 - filter("OBJECT_ID"=100 AND "ENDSCN">12723380596675 AND "ENDSCN"<=12723380675473 AND ("STARTSCN" IS NULL
OR "STARTSCN"<=12723380596675) AND ("OPERATION" IS NULL OR "OPERATION"<>'D'))
5 - filter("STARTSCN"<=12723380596675 OR "STARTSCN" IS NULL)
8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
9 - access("T"."OBJECT_ID"=100)
10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))
filter("RID"(+)=ROWIDTOCHAR("T".ROWID))
11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>12723380675473) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<12723380675473))
SYS_FBA_HIST_95824表的访问时FTS，我们可以对SYS_FBA_HIST_95824表在object_id上创建index，并收集统计信息
---在archive table上创建索引、收集统计
SQL> create unique index ind_SYS_FBA_HIST_95824 on SYS_FBA_HIST_95824(object_id); <--unique index也是不被允许的
create unique index ind_SYS_FBA_HIST_68841 on SYS_FBA_HIST_95824(object_id)
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_HIST_95824"
SQL> create index ind_SYS_FBA_HIST_95824 on SYS_FBA_HIST_95824(object_id);
Index created.
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'SYS_FBA_HIST_95824',cascade=>TRUE);
---使用到了索引
explain plan for select * from t0517_2 as of scn 12723380596675 where object_id=100;
set pagesize 100 linesize 150
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3579223519
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 75 (4)| 00:00:01 | | |
| 1 | VIEW | | 2 | 414 | 75 (4)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SYS_FBA_HIST_95824 | 1 | 132 | 2 (0)| 00:00:01 | 1 | 1 |
|* 4 | INDEX RANGE SCAN | IND_SYS_FBA_HIST_95824 | 2 | | 1 (0)| 00:00:01 | | |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 1 | 2119 | 73 (5)| 00:00:01 | | |
| 7 | SORT JOIN | | 1 | 91 | 3 (34)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | T0517_2 | 1 | 91 | 2 (0)| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | IND_T0517_2 | 1 | | 1 (0)| 00:00:01 | | |
|* 10 | SORT JOIN | | 3 | 6084 | 70 (3)| 00:00:01 | | |
|* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_95824 | 3 | 6084 | 69 (2)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------