(1)ORACLE索引管理-索引合并连接
1 合并的触发条件是什么?
2 一般情况下,在多谓词条件中,采用access+filter的方式进行数据检索
3 会话级别级别禁用alter session set "_INDEX_JOIN_ENABLED"=FALSE;
4 ANALYZE TABLE tablename ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS SIZE 200;
5 分析案例:由于多个谓词条件均存在索引,且一个索引选择性不是很好,在执行计划过程中采用索引合并连接,效率较慢。
- 类似执行计划如下
select * from t1;
update t1 set object_type='abc' where object_type<>'view';
update t1 set object_type='cba' where object_type='view';
create index idx_t1_lastddl on t1(last_ddl_time) online;
create index idx_t1_type on t1(object_type) online;
create index idx_t1_object_id on t1(object_id) online;
select * from t1 where object_id>10000 and last_ddl_time>sysdate-120Description Object owner Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 4 1 135
TABLE ACCESS BY INDEX ROWID BATCHED SYS T1 4 1 135
INDEX RANGE SCAN SYS IDX_T1_LASTDDL 2 42
- 类似执行计划如下:
SQL>SELECT * fROM T WHERE ID=1 AND NAME='123';
分别在ID列和name列均有索引。
Execution Plan
----------------------------------------------------------
Plan hash value: 2464580630----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 13985 (1)| 00:02:48 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | VIEW | index$_join$_001 | 1 | 18 | 13985 (1)| 00:02:48 |
|* 3 | HASH JOIN | | | | | |
|* 4 | INDEX RANGE SCAN | JT_BASDATE | 1 | 18 | 23730 (1)| 00:04:45 |
|* 5 | INDEX FAST FULL SCAN| JT_BASTTIME | 1 | 18 | 5776 (1)| 00:01:10 |
----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("BASECREATEDATE"<=1577289600)
3 - access(ROWID=ROWID)
4 - access("BASECREATEDATE"<=1577289600)
5 - filter("BASESTATUS"<>'??????' AND "BASESTATUS"='??????????')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
36818 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL>