Hi,
I would need to split the comma separated values in a column to individual rows. For that purpose, recursive sql is used as given below.
Even though there is an index on the queried column, the query is always going for Full table scan.
Is there any option to push the predicate to the recursive sql so that the index will be used and only the required rows are scanned?
create table acct_test ( SourceSys varchar2(100), accId varchar2(50));
create index at_indx1 on acct_test(accId);
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;
insert into acct_test values ('S1,S2','A1');
insert into acct_test values ('S1,S2,S3','A2');
insert into acct_test values ('S1,S2','A3');
insert into acct_test values ('S1,S2','A4');
insert into acct_test values ('S1,S2','A5');
insert into acct_test values ('S1,S2','A6');
insert into acct_test values ('S1,S2','A7');
insert into acct_test values ('S1,S2','A8');
insert into acct_test values ('S1,S2','A9');
insert into acct_test values ('S1,S2,S6','A10');
Commit;
select * from acct_test_view where ACCID = 'A10';
Plan:
SELECT STATEMENT
VIEW
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL ACCT_TEST
RECURSIVE WITH PUMP
Why is the index at_indx1 not used? Is there any better option to split the rows?
Thanks
Renjith
Instead of recursive with to generate the rows, use LATERAL to join the table to get the required number of rows, e.g.:
select ...
from acct_test,
lateral (
select level l from dual
connect by level <= regexp_count ( SourceSys, ',' ) + 1
) Put this in a view and you can get predicate pushing:
create or replace view acct_test_view as
with rws as (
select regexp_substr (
SourceSys, '[^,]+', 1, l
),
ACCID
from acct_test,
lateral (
select level l from dual
connect by level <= regexp_count ( SourceSys, ',' ) + 1
)
)
select * from rws;
alter session set statistics_level = all;
set serveroutput off
select * from acct_test_view where accid = 'A10';
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |
| 1 | NESTED LOOPS | | 1 | 1 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACCT_TEST | 1 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | AT_INDX1 | 1 | 1 | 1 |
| 4 | VIEW | VW_LAT_A2756428 | 1 | 1 | 3 |
| 5 | CONNECT BY WITHOUT FILTERING | | 1 | | 3 |
| 6 | FAST DUAL | | 1 | 1 | 1 |
-------------------------------------------------------------------------------------------