It is important to note that the issue is not present in 19c - I have reproduced it only on 12.2.0.1.190416 and 18c.
The listings that I am using in this article are taken from 18c.
1 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 | SQL> @or_expand_setupSQL> drop table t1;Table dropped.SQL>SQL> create table t1 ( 2 part_key varchar2(8), 3 status varchar2(12), 4 pad1 char(500) 5 ) 6 partition by list(part_key) ( 7 partition values ('P1'), 8 partition values (default) 9 ) 10 ;Table created.SQL>SQL>SQL> insert into t1 2 select 'P1', status, 'X' 3 from (select 90 pct, 'PROCESSED' status from dual union all 4 select 1, 'UNPROCESSED' from dual union all 5 select 9, 'PENDING' from dual) params, 6 lateral( 7 select level 8 from dual 9 connect by level <= params.pct * 1000 10 ) duplicator;100000 rows created.SQL>SQL> commit;Commit complete.SQL>SQL> create index t1_status_i on t1(status) local;Index created.SQL>SQL> select status, 2 count(*), 3 round(ratio_to_report(count(*)) over () * 100, 2) pct 4 from t1 5 group by status 6 order by 1;STATUS COUNT(*) PCT------------ ---------- ----------PENDING 9000 9PROCESSED 90000 90UNPROCESSED 1000 1 |
PROCESSED status, whereas PENDING and UNPROCESSED statuses account only for 9% and 1% correspondingly.The script itself: or_expand.sql
Next, I am going to run a simple query and check its plan with and without a virtual column on the
STATUS column:1 2 3 4 5 6 7 | select --+ gather_plan_statistics or_expand(@sel$1 (1) (2)) count(pad1) from t1 where part_key = :part_key and (:param = 'WAITING' and status = 'UNPROCESSED' or :param = 'ALL' and status <> 'PENDING'); |
1 2 | var part_key varchar2(10)='P1'var param varchar2(12)='WAITING' |
UNPROCESSED status from it.Here is the result of the script without the virtual column first:
1 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | SQL> @or_expand_test_no_vcolSQL> set def on lin 124 pages 100SQL>SQL> -- alter table t1 add lower_status varchar2(128) generated always as (lower(status)) virtual;SQL>SQL> exec dbms_stats.gather_table_stats( '', 't1', method_opt => 'for all columns size 254')PL/SQL procedure successfully completed.SQL>SQL> col tfi old_v tfi nopriSQL>SQL> select to_char(sysdate, 'yyyymmdd_hh24miss') tfi from dual;SQL>SQL> alter session set tracefile_identifier='&tfi.';old 1: alter session set tracefile_identifier='&tfi.'new 1: alter session set tracefile_identifier='20200520_181304'Session altered.SQL>SQL> alter session set events 'trace[sql_optimizer.*]';Session altered.SQL>SQL>SQL> var part_key varchar2(10)='P1'SQL> var param varchar2(12)='WAITING'SQL>SQL> select --+ gather_plan_statistics or_expand(@sel$1 (1) (2)) 2 count(pad1) 3 from t1 4 where part_key = :part_key 5 and (:param = 'WAITING' and status = 'UNPROCESSED' 6 or 7 :param = 'ALL' and status <> 'PENDING');COUNT(PAD1)----------- 1000SQL>SQL> select * 2 from dbms_xplan.display_cursor(format=> 'allstats last outline');PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------SQL_ID fq8b3jumk131t, child number 0-------------------------------------An uncaught error happened in prepare_sql_statement : ORA-01403: no data foundPlan hash value: 1293629841-----------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 7831 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 7831 || 2 | PARTITION LIST SINGLE| | 1 | 920 | 1000 |00:00:00.01 | 7831 ||* 3 | TABLE ACCESS FULL | T1 | 1 | 920 | 1000 |00:00:00.01 | 7831 |-----------------------------------------------------------------------------------------Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('18.1.0') DB_VERSION('18.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter((((:PARAM='ALL' AND "STATUS"<>'PENDING') OR ("STATUS"='UNPROCESSED' AND :PARAM='WAITING')) AND "PART_KEY"=:PART_KEY))35 rows selected.SQL>SQL> alter session set events 'trace[sql_optimizer.*] off';Session altered.SQL>SQL> col value for a80SQL>SQL> select value 2 from v$diag_info 3 where name='Default Trace File';VALUE--------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2137_20200520_181304.trc |
I highlighted the commented ALTER TABLE ADD COLUMN command. I am going to refer to it below.
It is worth noting that the OR Expansion transformation has not been performed and the relevant trace file contains a clue:
1 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 | ***********************************Cost-Based OR Expansion***********************************ORE: Trying CBQT OR expansion before unnestingORE: Checking validity of OR Expansion for query block SEL$1 (#1)ORE: Predicate chain before QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Predicate chain after QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')OR Expansion on query block SEL$1 (#1)ORE: Checking validity of OR Expansion for query block SEL$1 (#1)ORE: Predicate chain before QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Predicate chain after QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Using search type: linearORE: Checking validity of OR Expansion for query block SEL$1 (#1)ORE: Predicate chain before QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Predicate chain after QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Checking validity of disjunct chain ORE: Bypassed for disjunct chain: No Index or Partition driver found.ORE: # conjunction chain - 1ORE: No state generated. |
ORE: Bypassed for disjunct chain: No Index or Partition driver found. message in one of my previous posts: Concatenation and filter subqueries oddity.What can go wrong if we now uncomment the aforementioned ALTER TABLE ADD COLUMN command?
The script or_expand_test_vcol.sql:
1 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | SQL> @or_expand_test_vcolSQL> set def on lin 124 pages 100SQL>SQL> alter table t1 add lower_status varchar2(128) generated always as (lower(status)) virtual;Table altered.SQL>SQL> exec dbms_stats.gather_table_stats( '', 't1', method_opt => 'for all columns size 254')PL/SQL procedure successfully completed.SQL>SQL> col tfi old_v tfi nopriSQL>SQL> select to_char(sysdate, 'yyyymmdd_hh24miss') tfi from dual;SQL>SQL> alter session set tracefile_identifier='&tfi.';old 1: alter session set tracefile_identifier='&tfi.'new 1: alter session set tracefile_identifier='20200520_181319'Session altered.SQL>SQL> alter session set events 'trace[sql_optimizer.*]';Session altered.SQL>SQL>SQL> var part_key varchar2(10)='P1'SQL> var param varchar2(12)='WAITING'SQL>SQL> select --+ gather_plan_statistics or_expand(@sel$1 (1) (2)) 2 count(pad1) 3 from t1 4 where part_key = :part_key 5 and (:param = 'WAITING' and status = 'UNPROCESSED' 6 or 7 :param = 'ALL' and status <> 'PENDING');COUNT(PAD1)----------- 1000SQL>SQL> select * 2 from dbms_xplan.display_cursor(format=> 'allstats last outline');PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------SQL_ID fq8b3jumk131t, child number 0-------------------------------------An uncaught error happened in prepare_sql_statement : ORA-01403: no data foundPlan hash value: 3973059565----------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |----------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 83 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 83 || 2 | VIEW | VW_ORE_BA8ECEFB | 1 | 91982 | 1000 |00:00:00.01 | 83 || 3 | UNION-ALL | | 1 | | 1000 |00:00:00.01 | 83 ||* 4 | FILTER | | 1 | | 1000 |00:00:00.01 | 83 || 5 | PARTITION LIST SINGLE | | 1 | 999 | 1000 |00:00:00.01 | 83 ||* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 1 | 999 | 1000 |00:00:00.01 | 83 ||* 7 | INDEX RANGE SCAN | T1_STATUS_I | 1 | 1000 | 1000 |00:00:00.01 | 6 ||* 8 | FILTER | | 1 | | 0 |00:00:00.01 | 0 || 9 | PARTITION LIST SINGLE | | 0 | 90983 | 0 |00:00:00.01 | 0 ||* 10 | TABLE ACCESS FULL | T1 | 0 | 90983 | 0 |00:00:00.01 | 0 |----------------------------------------------------------------------------------------------------------------------------Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('18.1.0') DB_VERSION('18.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$9162BF3C_2") OUTLINE_LEAF(@"SET$9162BF3C_1") OUTLINE_LEAF(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) OUTLINE_LEAF(@"SEL$BA8ECEFB") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$BA8ECEFB" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") INDEX_RS_ASC(@"SET$9162BF3C_1" "T1"@"SET$9162BF3C_1" ("T1"."STATUS")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "T1"@"SET$9162BF3C_1") FULL(@"SET$9162BF3C_2" "T1"@"SET$9162BF3C_2") END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter(:PARAM='WAITING') 6 - filter("PART_KEY"=:PART_KEY) 7 - access("STATUS"='UNPROCESSED') 8 - filter(:PARAM='ALL') 10 - filter(("STATUS"<>'PENDING' AND "PART_KEY"=:PART_KEY AND (LNNVL(:PARAM='WAITING') OR LNNVL("STATUS"='UNPROCESSED'))))54 rows selected.SQL>SQL> alter session set events 'trace[sql_optimizer.*] off';Session altered.SQL>SQL> col value for a80SQL>SQL> select value 2 from v$diag_info 3 where name='Default Trace File';VALUE--------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2137_20200520_181319.trc |
The relevant section of the trace file is as follows:
1 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 | ***********************************Cost-Based OR Expansion***********************************ORE: Trying CBQT OR expansion before unnestingORE: Checking validity of OR Expansion for query block SEL$1 (#1)ORE: Predicate chain before QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Predicate chain after QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')OR Expansion on query block SEL$1 (#1)ORE: Checking validity of OR Expansion for query block SEL$1 (#1)ORE: Predicate chain before QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Predicate chain after QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Using search type: linearORE: Checking validity of OR Expansion for query block SEL$1 (#1)ORE: Predicate chain before QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Predicate chain after QB validity check - SEL$1"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')ORE: Checking validity of disjunct chain ORE: # conjunction chain - 2ORE: Checking validity of disjunct chain ORE: Predicate listP1 : "T1"."PART_KEY"=:B1P2 : :B1='WAITING'P3 : "T1"."STATUS"='UNPROCESSED'P4 : :B1='ALL'P5 : "T1"."STATUS"<>'PENDING' DNF Matrix (Before sorting OR branches) P1 P2 P3 P4 P5 CNJ (#1) : 1 1 1 0 0 CNJ (#2) : 1 0 0 1 1 |
- The virtual column should be on the STATUS column. Neither PART_KEY, nor PAD1 works - they both result in the absence of OR Expansion.
- The OR Expansion transformation kicks in if there are extended statistics on the STATUS column, such as: SYS_OP_COMBINED_HASH(STATUS, PART_KEY).
- Unsurprisingly, there is OR Expansion when there is an FBI, such as: 1
createindext1_lower_status_iont1(lower(status))local; - The full outline of the query with OR Expansion does not lead to the OR Expansion transformation in the examples from this article where there was no OR Expansion.
- Once again, 19c behaves differently - there is no OR Expansion in the given examples when 12.2 and 18c performs it. I am going to talk about in the next blog post.
Although I do expect OR Expansion in the queries from this article, I am not able to make a connection between OR Expansion and virtual columns/extended statistics/FBIs in the provided examples.
Hence, this issue is a bit mixed bag for me: I would love to have OR Expansion - the examples were designed after real application queries after all (they benefit from OR Expansion) - it might as well be a bug that was fixed in 19c.
Time will tell. I have raised an SR to follow up on this issue: SR 3-22449907431 : OR-Expansion does not work following 19.5 upgrade. Hopefully, Oracle Support will explain it one day - we have not made any progress during last 2 months which has become customary while working with Oracle Support.
