让or使用索引
select a.object_id, b.object_type
from yz.t1 a
left join yz.t5 b
on a.object_type = b.object_type
where (a.object_id = 919 or a.object_id1 = 919)
and rownum = 1
条件里有or执行计划不走索引,把or 前后列上都创建索引后,执行计划使用索引
create index yz.idx_t1 on yz.t1(object_id)
create index yz.idx1_t1 on yz.t1(object_id1)
创建索引前和索引后执行计划对比如下
[qdtais1]@ht19c01[/home/oracle]$./ora sql 68th03t6xnygy DBA_HIST_SQLSTAT detail(recent 15 days): SNAP_ID SNAP_DATE INST PHV CPU_S EXECS BUF_PE ROW_PE MS_PE DISK_R IO_S APP_MS -------- -------------------- ---- ---------- ------- --------- ---------- ---------- --------- ---------- ------- ---------- 1103 20220606 10:00 1 390571546 0 0 12 0 2 0 0 0 SQL top event in gv_active_session_history: Historical SQL plans in AWR: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 68th03t6xnygy -------------------- select a.object_id,b.object_type from yz.t1 a left join yz.t5 b on a.object_type=b.object_type where (a.object_id=919 or a.object_id1=919) and rownum =1 Plan hash value: 390571546 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 30383 (100)| | | 1 | COUNT STOPKEY | | | | | | | 2 | NESTED LOOPS OUTER| | 1 | 33 | 30383 (1)| 00:00:02 | | 3 | TABLE ACCESS FULL| T1 | 1 | 24 | 30383 (1)| 00:00:02 | | 4 | INDEX UNIQUE SCAN| PK_T5 | 1 | 9 | 0 (0)| | ----------------------------------------------------------------------------- Current SQL plans in Curor: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 68th03t6xnygy, child number 0 ------------------------------------- select a.object_id,b.object_type from yz.t1 a left join yz.t5 b on a.object_type=b.object_type where (a.object_id=919 or a.object_id1=919) and rownum =1 Plan hash value: 529750346 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 34 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | NESTED LOOPS OUTER | | 2 | 48 | 34 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 30 | 34 (0)| 00:00:01 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | 5 | BITMAP OR | | | | | | | 6 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 7 | INDEX RANGE SCAN | IDX_T1 | | | 1 (0)| 00:00:01 | | 8 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 9 | INDEX RANGE SCAN | IDX1_T1 | | | 1 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | PK_T5 | 1 | 9 | 0 (0)| | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 7 - access("A"."OBJECT_ID"=919) 9 - access("A"."OBJECT_ID1"=919) 10 - access("A"."OBJECT_TYPE"="B"."OBJECT_TYPE") Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level Historical Plans Summary(dba_hist_sqlstat): RN PLAN_HASH_VALUE AVG_ETIME_S AVG_CPU_S AVG_BUFFERS AVG_READS AVG_ROWS EXECS FIRST_SNAP LAST_SNAP ---- --------------- ------------ ------------ ----------- ---------- --------- --------- ------------------- ------------------- 1 390571546 0.002 0.002 12 0 0 1 2022-06-06 10:00:34 2022-06-06 10:00:34 Current Plans Summary(gv_sql): RN PLAN_HASH_VALUE AVG_ETIME_S AVG_CPU_S AVG_BUFFERS AVG_READS AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME LAST_ACTIVE ---- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- -------------------- 1 529750346 0.016 0.013 650 7 1 1 2022-06-06/09:30:30 2022-06-06 10:14:11 Tables involved(used objects may not exists now): TABLE_OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED SIZE_M ------------------------------ ------------------------------ ---------- -------------------- ---------- YZ T1 9975 2022-06-06 10:08:41 880 YZ T5 23 2022-06-06 10:08:45 0 Above is sql info for sql_id :68th03t6xnygy ,plan format is typical. for advanced plan ,use ora sql <sqlid> adv