Oracle 12c Upgrade : Virtual Column with Function Based Index (bug)

Issue: Post 12C upgrade: Oracle Bug - Virtual Column issue

Fix: Set it at session level or system level - fix_control=’16237969:OFF’

Be aware of the virtual column getting created with function based index, which may lead to sub-optimal plans.

In 11g , index is getting picked by the oracle optimizer and going with optimal Plan. But in 12c , index is not getting picked by the oracle optimizer and hence it’s going with sub-optimal plan in the query that  result in high logical I/O’s and elapse time.

Thanks Vasu N, by sharing his findings on this and working with Oracle on the same and he thought of helping to all by sharing this.

#########################################
Reproduce the issue with below steps:
#########################################

CREATE TABLE TEST_VIRTUAL
AS
SELECT ROWNUM id, 'TEST' || ROWNUM name
FROM DUAL
CONNECT BY ROWNUM < 10000;

CREATE TABLE TEST_VIRTUAL_PARENT
AS
SELECT ROWNUM id, MOD (ROWNUM, 3) GROUP_NAME
FROM DUAL
CONNECT BY ROWNUM < 10000;

CREATE INDEX TEST_VIRTUAL_FBI_IDX ON TEST_VIRTUAL (UPPER ("NAME"));
CREATE INDEX TEST_VIRTUAL_PARENT_IDX ON TEST_VIRTUAL_PARENT (ID);

##############
#Execute statement with a simple function
#############
SELECT LRD.ID FROM TEST_VIRTUAL_PARENT LRD, TEST_VIRTUAL MLR
WHERE     UPPER (MLR.NAME) = 'TEST1'
AND LRD.ID = MLR.ID(+)
AND LRD.GROUP_NAME = 1;

#####################
The resulted Plan is as below by not picking fbi  index
#####################
SELECT STATEMENT ALL_ROWS Cost: 17 Bytes: 66,660 Cardinality: 3,333

4 FILTER Filter Predicates: UPPER("NAME")='TEST1'
3 HASH JOIN OUTER Access Predicates: "LRD"."ID"="MLR"."ID"(+) Cost: 17 Bytes: 66,660 Cardinality: 3,333
1 TABLE ACCESS FULL TABLE PERF11I.TEST_VIRTUAL_PARENT Filter Predicates: "LRD"."GROUP_NAME"=1 Cost: 7 Bytes: 23,331 Cardinality: 3,333
2 TABLE ACCESS FULL TABLE PERF11I.TEST_VIRTUAL Cost: 10 Bytes: 129,987 Cardinality: 9,999

##############
#Execute statement with a simple function, along with hint to not use the virtual columns
#############

SELECT /*+ OPT_PARAM('_replace_virtual_columns','false') */
LRD.ID
FROM TEST_VIRTUAL_PARENT LRD, TEST_VIRTUAL MLR
WHERE UPPER (MLR.NAME) = 'TEST1' AND LRD.ID = MLR.ID(+)

###############
Plan
###############
SELECT STATEMENT ALL_ROWS Cost: 16 Bytes: 1,700 Cardinality: 100
4 HASH JOIN Access Predicates: "LRD"."ID"="MLR"."ID" Cost: 16 Bytes: 1,700 Cardinality: 100
2 TABLE ACCESS BY INDEX ROWID BATCHED TABLE PERF11I.TEST_VIRTUAL Cost: 9 Bytes: 1,300 Cardinality: 100
1 INDEX RANGE SCAN INDEX PERF11I.TEST_VIRTUAL_FBI_IDX Access Predicates: UPPER("NAME")='TEST1' Cost: 1 Cardinality: 40
3 INDEX FAST FULL SCAN INDEX PERF11I.TEST_VIRTUAL_PARENT_IDX Cost: 7 Bytes: 39,996 Cardinality: 9,999