Re: Inefficient query plan using large in-list

Have you tried with the NOMERGE hint?

Regards

On 5/16/19 11:43 AM, Senthil Subramanian wrote:
> 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
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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