create table acct_test ( SourceSys varchar2(100), accId varchar2(50))
Table created.
create index at_indx1 on acct_test(accId)
Index created.
create or replace view acct_test_view as
WITH vw_act ( accId,SourceSys,start_pos,end_pos ) AS (
SELECT accId,SourceSys,1,INSTR( SourceSys, ',', 1 )
FROM acct_test
UNION ALL
SELECT accId,SourceSys,end_pos + 1,INSTR( SourceSys, ',', end_pos + 1 )
FROM vw_act
WHERE end_pos > 0
)
SELECT accId,
CASE end_pos WHEN 0 THEN SUBSTR( SourceSys, start_pos )
ELSE SUBSTR( SourceSys, start_pos, end_pos - start_pos )
END AS SourceSys
FROM vw_act
View created.
insert into acct_test values ('S1,S2','A1')
1 row(s) inserted.
insert into acct_test values ('S1,S2,S3','A2')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A3')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A4')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A5')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A6')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A7')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A8')
1 row(s) inserted.
insert into acct_test values ('S1,S2','A9')
1 row(s) inserted.
insert into acct_test values ('S1,S2,S6','A10')
1 row(s) inserted.
Commit
Statement processed.
select * from acct_test_view where ACCID = 'A10'
| ACCID | SOURCESYS | A10 | S1 | A10 | S2 | A10 | S6 |
|---|
explain plan for select * from acct_test_view where ACCID = 'A10'
Statement processed.
EXPLAIN PLAN SET statement_id = 'sample1' FOR
select * from acct_test_view where ACCID = 'A10'
Statement processed.
SELECT lpad(' ',level-1)||operation||' '||options||' '||
object_name "Plan"
FROM plan_table
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'sample1'
ORDER BY id
no data found
select * FROM plan_table
| STATEMENT_ID | PLAN_ID | TIMESTAMP | REMARKS | OPERATION | OPTIONS | OBJECT_NODE | OBJECT_OWNER | OBJECT_NAME | OBJECT_ALIAS | OBJECT_INSTANCE | OBJECT_TYPE | OPTIMIZER | SEARCH_COLUMNS | ID | PARENT_ID | DEPTH | POSITION | COST | CARDINALITY | BYTES | OTHER_TAG | PARTITION_START | PARTITION_STOP | PARTITION_ID | OTHER | OTHER_XML | DISTRIBUTION | CPU_COST | IO_COST | TEMP_SPACE | ACCESS_PREDICATES | FILTER_PREDICATES | PROJECTION | TIME | QBLOCK_NAME | example_plan1 | 60496 | 18-JUN-20 | - | SELECT STATEMENT | - | - | - | - | - | - | - | ALL_ROWS | - | 0 | - | 0 | 3 | 3 | 14 | 1218 | - | - | - | - | - | - | - | 39667 | 3 | - | - | - | - | 1 | - | example_plan1 | 60496 | 18-JUN-20 | - | TABLE ACCESS | FULL | - | SQL_WELYAVJQFUVZBPRGJVPDKWVQG | EMP | "EMP"@"SEL$1" | 1 | TABLE | - | - | 1 | 0 | 1 | 1 | 3 | 14 | 1218 | - | - | - | - | - | <other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_WELYAVJQFUVZBPRGJVPDKWVQG"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">3634526668</info><info type="plan_hash">3956160932</info><info type="plan_hash_2">3634526668</info><stats type="compilation"><stat name="bg">2</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "EMP"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml> | - | 39667 | 3 | - | - | - | "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22] | 1 | SEL$1 |
|---|
SELECT lpad(' ',level-1)||operation||' '||options||' '||
object_name "Plan"
FROM plan_table
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'example_plan1'
ORDER BY id
no data found
EXPLAIN PLAN SET statement_id = 'sample1' FOR
select * from acct_test_view where ACCID = 'A10'
Statement processed.
select * FROM plan_table
no data found
select * FROM plan_table
no data found