Trying the OR_EXPAND() hint with the simple IN list of sql_ids, the 10053
trace reports:
ORE: Checking validity of OR Expansion for query block SEL$1 (#0)
ORE: Predicate chain before QB validity check - SEL$1
"V$SQLSTATS"."SQL_ID"='3w8u1cvrsc038' OR
"V$SQLSTATS"."SQL_ID"='4tppmg7r9s0bj'
ORE: Predicate chain after QB validity check - SEL$1
"V$SQLSTATS"."SQL_ID"='3w8u1cvrsc038' OR
"V$SQLSTATS"."SQL_ID"='4tppmg7r9s0bj'
ORE: bypassed - No valid predicate for OR expansion.
Trying the use_concat() hint the 10053 is slightly more informative
LORE: Trying or-Expansion on query block SEL$88122447 (#0)
LORE: Or-expansion bypassed: No index driver found in OR chain:
id=0 predicate=("X$KKSSQLSTAT"."SQL_ID"='3w8u1cvrsc038' OR
"X$KKSSQLSTAT"."SQL_ID"='4tppmg7r9s0bj') AND ("X$KKSSQLSTAT"."CON_ID"=3 OR
"X$KKSSQLSTAT"."CON_ID"=0) AND "X$KKSSQLSTAT"."INST_ID"=USERENV('INSTANCE')
LORE: Or-expansion bypassed: No index driver found in OR chain:
id=0 predicate=("X$KKSSQLSTAT"."CON_ID"=3 OR "X$KKSSQLSTAT"."CON_ID"=0)
AND "X$KKSSQLSTAT"."INST_ID"=USERENV('INSTANCE')
LORE: USE_CONCAT hint was ignored.
The antepenultimate line of the use_concat() case suggests that the code or
or_expansion (old or new) doesn't understand x$ pseudo-indexes.
Regards
Jonathan Lewis
