让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