62? (5)| 00:00:02 |? ? ? |? ? ? |
|? 2 |? UNION-ALL? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? ? |? ? ? |? ? ? |? ? ? ? ? ? |? ? ? ? ? |? ? ? |? ? ? |
|? 3 |? ? PARTITION RANGE SINGLE? ? ? ? |? ? ? ? ? ? ? ? ? ? |? ? 1 |? 235 |? ? 89? (6)| 00:00:02 |? ? 1 |? ? 1 |
|*? 4 |? ? TABLE ACCESS FULL? ? ? ? ? ? | SYS_FBA_HIST_95824 |? ? 1 |? 235 |? ? 89? (6)| 00:00:02 |? ? 1 |? ? 1 |
|*? 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):
---------------------------------------------------
? 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 |? ? F