Why PCM_OP_SEARCH is not using index? (Doc ID 3071644.1)

Last updated on FEBRUARY 12, 2025

Applies to:

Goal

Executed the search:
0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1 PIN_FLD_CUSTOM_INFO SUBSTRUCT [0] allocated 20, used 1
2 PIN_FLD_BILL_NO STR [0] "111111"
0 PIN_FLD_ARGS ARRAY [2] allocated 20, used 1
1 EFX_FLD_DETAIL ARRAY [1] allocated 20, used 1
2 PIN_FLD_EVENT_OBJ POID [0] 0.0.0.1 /event/delayed/offline -1 0
0 PIN_FLD_TEMPLATE STR [0] "select X from /invoice_detail where F1 = V1 and F2.type like V2 "
0 PIN_FLD_FLAGS INT [0] 768
0 PIN_FLD_RESULTS ARRAY [*] allocated 20, used 1
1 EFX_FLD_DETAIL ARRAY [*] allocated 20, used 2
2 EFX_FLD_PRODUCT_CODE STR [0] ""
2 PIN_FLD_EVENT_OBJ POID [0] 0.0.0.1 /event/delayed/offline -1 0

Following query is executed with full table scan and index on OBJ_ID0 is not used:
select obj_id0, rec_id, prod_code, event_obj_DB, event_obj_ID0, event_obj_TYPE, event_obj_REV from efx_inv_detail_rec_t where efx_inv_detail_rec_t.event_obj_TYPE LIKE :1 AND obj_id0 in
(:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80,:81,:82,:83,:84,:85,:86,:87,:88,:89)

Optimizer is not using index. (INTERNAL_FUNCTION("OBJ_ID0") AND "EFX_INV_DETAIL_REC_T"."EVENT_OBJ_TYPE" LIKE :1) performed an internal conversion because the index I_EFX_INV_DETAIL_REC__ID on OBJ_ID0 is not used.

Question: Why PCM_OP_SEARCH is not using index? What is INTERNAL_FUNCTION being used by on obj_id0?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.