Re: Inefficient query plan using large in-list
Date: Thu, 16 May 2019 20:57:09 +0300
Message-ID: <CAOVevU5gMQ+gzw2Dx31DCo3VTjrruN7WvOF50cv_UDAkTRyA+A_at_mail.gmail.com>
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 || 1000 | 7000 | 24 (0)| 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
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_at_gmail.com> wrote:
> Senthil,
>
> But in your case I don't see the reason to use collections. You showed
>>> 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) ;
>>>
>>
> Have you checked this part?
> Why do you need table() function here?
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 16 2019 - 19:57:09 CEST