Re: Inefficient query plan using large in-list

Senthil,

As i said previously there are few standard ways and their common idea is to hide kokbf$ functions. I'm not sure that I remember all of them: 1. Avoid table() functions at all (gtt, pl/sql, multiple executions, etc...);
2. Undocumented hint "materialize": you can hide your table() function in "with" clause with this hint (or you can to force materization other standard ways). Example:
with v as (select /*+ materialize */ * from table(...)) select *
from v, SECMASTER_HISTORY
where v.xxx=SECMASTER_HISTORY.xxx
3. Undocumented hint "precompute_subquery": select *
from SECMASTER_HISTORY
where xxx in (select/*+ precompute_subquery */ * from table(....)) 4. laterals/cross apply: unfortunately this approach is not stable and it doesn't always work. It's version-dependent thing, so I wouldn't recommend it
select *
from table(...) v

      ,lateral(select * from SECMASTER_HISTORY s where s.xxx=v.xxx) 5. use xmltable instead of table() - it doesn't always work too, but sometimes it can help.

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

                           connect by level <= length
(regexp_replace(:in_list, '[^,]+')) + 1) as sys.OdciNumberList)) xt,
       secmaster_history sh

        from dual
        connect by level <= length (regexp_replace(:in_list, '[^,]+')) + 1)
xt,
       secmaster_history sh

On Thu, May 16, 2019 at 7:22 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> As others have noted, table() functions don't mix well with the optimizer
> and various transformation may fail to appear for reasons that are not
> obvious to mere mortals such as us.
>
> Which version of Oracle, though, and have you demonstrated that a query
> like the following behaves the way you want because sometimes the presence
> of analytic functions blocks the query transformation you expect to see:
>
> select
> {columns}
> from secmaster_history
> where
> where security_alias in (1,2,3,4,5)
> and to_date('15-MAY-2010') between effective_date and
> nvl(expiration_date,sysdate)
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Senthil Subramanian <skumar.sen_at_gmail.com>
> Sent: 16 May 2019 16:43
> To: oracle-l_at_freelists.org
> Subject: Inefficient query plan using large in-list
>
> Hello list members,
>
> I would like get some help to solve a performance issue. I have a query
> with a simple view which needs to return rows when application passes comma
> separated input values. The view goes against an MVIEW with an analytic
> (LAG) function to compute the expiration date using an effective date for a
> given security ID & src. Below is the view definition:
>
>
> CREATE OR REPLACE FORCE VIEW SECMASTER_HISTORY
>
> AS
>
> SELECT security_alias,
>
> src_intfc_inst,
>
> effective_date,
>
> (effective_date) over (partition by security_alias,src_intfc_inst
> order by effective_date desc) - interval '1' second expiration_date,
>
> <other cols>
>
> FROM SECMASTER_HISTORY_MV
>
> WHERE src_intfc_inst = 4;
>
>
> There is an unique index on the MV with columns
> (SECURITY_ALIAS,SRC_INTFC_INST,EFFECTIVE_DATE)
>
>
> When I run the below query
>
>
> 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) ;
>
>
> The optimizer is not pushing the predicate into the view and does a full
> scan on the MV causing performance issue!
>
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name |
> Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |IN-OUT|
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | |
> 387K| 1653M| | 17M (1)| 00:11:12 | | | |
>
> | 1 | MERGE JOIN | |
> 387K| 1653M| | 17M (1)| 00:11:12 | | | |
>
> |* 2 | VIEW | SECMASTER_HISTORY |
> 47M| 199G| | 17M (1)| 00:11:12 | | | |
>
> | 3 | WINDOW SORT | |
> 47M| 71G| 91G| 17M (1)| 00:11:12 | | | |
>
> | 4 | PARTITION RANGE ALL | |
> 47M| 71G| | 1209K (1)| 00:00:48 | 1 |1048575| |
>
> |* 5 | MAT_VIEW ACCESS FULL | SECMASTER_HISTORY_MV |
> 47M| 71G| | 1209K (1)| 00:00:48 | 1 |1048575| |
>
> |* 6 | SORT JOIN | |
> 8168 | 16336 | | 30 (4)| 00:00:01 | | | |
>
> | 7 | COLLECTION ITERATOR SUBQUERY FETCH| |
> 8168 | 16336 | | 29 (0)| 00:00:01 | | | |
>
> |* 8 | CONNECT BY WITHOUT FILTERING | |
> | | | | | | | PCWP |
>
> | 9 | FAST DUAL | |
> 1 | | | 2 (0)| 00:00:01 | | | PCWP |
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------
>
>
> Predicate Information (identified by operation id):
>
> ---------------------------------------------------
>
>
> 2 - filter("EFFECTIVE_DATE"<=TO_DATE('15-MAY-2010') AND
> NVL("EXPIRATION_DATE",SYSDATE_at_!)>=TO_DATE('15-MAY-2010'))
>
> 5 - filter("SRC_INTFC_INST"=4)
>
> 6 - access("SH"."SECURITY_ALIAS"=VALUE(KOKBF$))
>
> filter("SH"."SECURITY_ALIAS"=VALUE(KOKBF$))
>
> 8 - filter(LEVEL<=LENGTH( REGEXP_REPLACE (:IN_LIST,'[^,]+'))+1)
>
>
> 25 rows selected.
>
>
> I tried to hint the optimizer but nothing works :-(
>
>
> Any help is highly appreciated!
>
>
> Thanks in advance for your help!
>
>
> Senthil
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l