Use of Combined Index

The Below example shows the difference in execution plan when a composite index is created  

SELECT d.ITEM_ID,d.ITEM_DESC,D.UNIT,d.ITEM_REF_ID,d.ITEM_LEVEL,N_QTYMILE,S_WORKCODE,
SYS_CONNECT_BY_PATH (ITEM_DESC,'--->') scbdesc,LPAD(' ',level*3,' ')||ITEM_DESC LNAME ,ACCESSORIES,SYS_CONNECT_BY_PATH (ITEM_ID,'--->') scbp,level,CONNECT_BY_ROOT ITEM_ID rootn , CONNECT_BY_ISLEAF leaf FROM ITEM_MASTER d START WITH ITEM_REF_ID is null CONNECT BY PRIOR ITEM_ID =ITEM_REF_ID ORDER SIBLINGS BY ITEM_LEVEL

create index idx_ITEM_MASTER_refid  on ITEM_MASTER (ITEM_ID,ITEM_REF_ID)

After creating combined index check the execution plan




SELECT  d.ITEM_ID,d.ITEM_DESC,D.UNIT,d.ITEM_REF_ID,d.ITEM_LEVEL,N_QTYMILE,S_WORKCODE,
SYS_CONNECT_BY_PATH (ITEM_DESC,'--->') scbdesc,LPAD(' ',level*3,' ')||ITEM_DESC LNAME ,ACCESSORIES,SYS_CONNECT_BY_PATH (ITEM_ID,'--->') scbp,level,CONNECT_BY_ROOT ITEM_ID rootn , CONNECT_BY_ISLEAF leaf FROM ITEM_MASTER d
START WITH ITEM_REF_ID is  null CONNECT BY PRIOR ITEM_ID =ITEM_REF_ID ORDER SIBLINGS BY ITEM_LEVEL