Recursive_fts

View All Public Scripts Login to Run Script

Script Name Recursive_fts
Visibility Unlisted - anyone with the share link can access
Description Recursive sql full table scan. Pushing predicate to recursive sql
Area SQL General
Contributor sysadmin@suntecgroup.com
Created Thursday June 18, 2020

Statement 1

create table acct_test ( SourceSys  varchar2(100), accId varchar2(50))

Table created.

Statement 2

create index at_indx1 on acct_test(accId)

Index created.

Statement 3

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.

Statement 4

insert into acct_test values ('S1,S2','A1')

1 row(s) inserted.

Statement 5

insert into acct_test values ('S1,S2,S3','A2')

1 row(s) inserted.

Statement 6

insert into acct_test values ('S1,S2','A3')

1 row(s) inserted.

Statement 7

insert into acct_test values ('S1,S2','A4')

1 row(s) inserted.

Statement 8

insert into acct_test values ('S1,S2','A5')

1 row(s) inserted.

Statement 9

insert into acct_test values ('S1,S2','A6')

1 row(s) inserted.

Statement 10

insert into acct_test values ('S1,S2','A7')

1 row(s) inserted.

Statement 11

insert into acct_test values ('S1,S2','A8')

1 row(s) inserted.

Statement 12

insert into acct_test values ('S1,S2','A9')

1 row(s) inserted.

Statement 13

insert into acct_test values ('S1,S2,S6','A10')

1 row(s) inserted.

Statement 14
```
Commit
```
```
Statement processed.
```

Statement 15

select * from acct_test_view where ACCID = 'A10'


ACCID SOURCESYS
A10 S1
A10 S2
A10 S6

3 rows selected.

ACCID	SOURCESYS
A10	S1
A10	S2
A10	S6

Statement 16

explain plan for select * from acct_test_view where ACCID = 'A10'

Statement processed.

Statement 17

EXPLAIN PLAN SET statement_id = 'sample1' FOR 
  select * from acct_test_view where ACCID = 'A10'

Statement processed.

Statement 18

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

Statement 19

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

2 rows selected.

Statement 20

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

Statement 21

EXPLAIN PLAN SET statement_id = 'sample1' FOR 
  select * from acct_test_view where ACCID = 'A10'

Statement processed.

Statement 22
```
select * FROM plan_table 
```
```
no data found
```
Statement 23
```
select * FROM plan_table
```
```
no data found
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

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