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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Senthil Subramanian <skumar.sen@xxxxxxxxx>
Sent: 16 May 2019 16:43
To: oracle-l@xxxxxxxxxxxxx
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@!)>=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
--
//www.freelists.org/webpage/oracle-l