Andy,
I've just checked the solution with "materialize" on 18.5 and I see it
requires a bit more "materialize" steps:
Please see my comment here:
https://gist.github.com/xtender/24d29af63a398200d4d87ebc4a4a27b8
"Materialize" solution for Oracle 18.5:
```sql
with z(a) as (select/*+ materialize */ * from table(sys.odcinumberlist(1)))
,v(a) as (select/*+ materialize */ * from z where 1=1)
select/*+ leading(v t) use_nl(t) index(t) */ t.*
from vtest t
where exists(select * from v where t.a=v.a)
```
Plan:
```sql
Plan hash value: 434338359
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 20 | 58 (4)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION |
| | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)|
SYS_TEMP_0FD9D67BE_327432A | | | | |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH |
| 8168 | 16336 | 29 (0)| 00:00:01 |
| 4 | HASH GROUP BY |
| 1 | 20 | 29 (7)| 00:00:01 |
| 5 | NESTED LOOPS |
| 1000 | 20000 | 28 (4)| 00:00:01 |
| 6 | NESTED LOOPS |
| 1000 | 20000 | 28 (4)| 00:00:01 |
| 7 | VIEW |
| 8168 | 103K| 3 (0)| 00:00:01 |
| 8 | HASH UNIQUE |
| 1 | 16336 | | |
| 9 | TABLE ACCESS FULL |
SYS_TEMP_0FD9D67BE_327432A | 8168 | 16336 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | ITEST
| 1000 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | XTEST
| 1000 | 7000 | 24 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("A"="V"."A")
```
Unfortunately we need to wrap table() function twice on 18.5. I haven't
tried it on 19.3 yet, will do later.
On Thu, May 16, 2019 at 8:50 PM S M <xt.and.r@xxxxxxxxx> wrote:
Senthil,
But in your case I don't see the reason to use collections. You showed
Have you checked this part?this code:
select sh.*
from table(cast(multiset(select level from dual
connect by level <= length
(regexp_replace(:in_list, '[^,]+')) + 1) as sys.OdciNumberList)) xt,
secmaster_history sh
where sh.security_alias = xt.column_value
and to_date('15-MAY-2010') between effective_date and
nvl(expiration_date,sysdate) ;
and looks like there should be regexp_substr(...) instead of level:
select sh.*
from table(cast(multiset(select *regexp_substr(:in_list,
'[^,]+',1,level)* from dual
connect by level <= length
(regexp_replace(:in_list, '[^,]+')) + 1) as sys.OdciNumberList)) xt,
secmaster_history sh
where sh.security_alias = xt.column_value
and to_date('15-MAY-2010') between effective_date and
nvl(expiration_date,sysdate) ;
In this case you don't need table(cast multiset(...)), just use inline
view:
select sh.*
from (select regexp_substr(:in_list, '[^,]+',1,level) as val
from dual
connect by level <= length (regexp_replace(:in_list, '[^,]+')) +
1) xt,
secmaster_history sh
where sh.security_alias = xt.val
and to_date('15-MAY-2010') between effective_date and
nvl(expiration_date,sysdate) ;
Why do you need table() function here?
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
