Re: Inefficient query plan using large in-list

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

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

       secmaster_history sh

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