让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
