Forum Stats

  • 3,751,461 Users
  • 2,250,366 Discussions
  • 7,867,433 Comments

Discussions

Plan generates a filter that prevents me to using index

950472
950472 Member Posts: 10
edited Aug 24, 2012 3:40AM in SQL & PL/SQL
Hi guys,

for a couple of days a select statement is giing me head akes.
Plan generates with a "MP"."REGISTRATION_ID" LIKE '_____-______' filter,but I cannot figure out how to avoid it (what hint to use), not how should I implement the advisor suggestion

Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.

I would like to use a function index, but I don't know how I should create it (why is it using such a filter?... there is an index on REGISTRATION_ID)
The resultset is used by a webservice and it times out because it takes about 120 sec (but it should be no more then 3 secs such a select)
Can you please guys have a glimpse... maybe you can give me a suggestion

10x
SQL Text   : SELECT  RPIT.REPORT_PRODUCTION_ID, 
                    TO_DATE(rpit.request_datetime, 'DD/MM/RRRR') AS
             Date_Of_Issue, 
                    iead.customer_ref AS Reference_No, 
                    CASE 
                      WHEN INITCAP(g.group_name) IS NULL THEN 
                       (INITCAP(mem.given_name) || ' ' ||
             INITCAP(mem.family_name)) 
                      ELSE 
                       g.group_name 
                    END AS Customer_Name, 
                    iead.stage_no AS Credit_Control_Stage, 
                    Swn_DocumentIndexing.getDocNewPath(rpit.pdf_filename) AS
             Document_Link 
                    
               FROM report_production_items rpit 
               JOIN system_events syse 
                 ON rpit.event_id = syse.event_id 
               JOIN ID_EVENTS_ADDITIONAL_DATA IEAD 
                 ON syse.event_id = NVL(iead.EVENT_ID,0) and iead.event_id =
             syse.event_id 
               LEFT JOIN member_events mev 
                 ON syse.event_id = mev.event_id 
               LEFT JOIN members mem 
                 ON mev.member_id = mem.member_id 
               LEFT JOIN member_registrations mreg 
                 ON mem.member_id = mreg.member_id 
               LEFT JOIN group_events gev 
                 ON syse.event_id = gev.event_id 
               LEFT JOIN groups g 
                 ON gev.group_id = g.group_id 
               JOIN registrations reg 
                 ON gev.group_id = reg.group_id 
                 OR mreg.registration_id = reg.registration_id 
               JOIN contracts con 
                 ON gev.group_id = con.group_id 
                 OR reg.registration_id = con.registration_id 
               JOIN member_products mp 
                 ON reg.registration_id = mp.registration_id 
                 AND con.contract_pk = mp.contract_pk 
                 
                 and mp.product_id = rpit.product_id 
              WHERE SYSE.EVENT_NAME_CODE IN ('DNS', 'DNI') 
                AND mp.intermediary_id = 1004 
                AND Swn_Dgp_Idgp.iscurrentcontract(con.contract_pk) = 'Y' 
                AND rpit.request_datetime >= ADD_MONTHS(swn_datetime.systemdat
             e, -1) 
                AND (rpit.report_print_status IN ('P', 'I', 'S') OR 
                    rpit.notification_status_code IN ('E', 'P', 'B')) 
                AND rpit.pdf_filename IS NOT NULL 
              GROUP BY iead.customer_ref, 
                       rpit.request_datetime, 
                       g.group_name, 
                       mem.given_name, 
                       mem.family_name, 
                       iead.stage_no, 
                       rpit.pdf_filename, 
                       RPIT.REPORT_PRODUCTION_ID 
              ORDER BY 2 DESC

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate "MP"."REGISTRATION_ID" LIKE '_____-______' used at line ID 20
  of the execution plan contains an expression on indexed column
  "REGISTRATION_ID". This  expression prevents the optimizer from efficiently
  using indices on table "AOSWN"."MEMBER_PRODUCTS".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

  Rationale
  ---------
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- The current operation was interrupted because it timed out.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1847043961
 
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |     1 |   296 |  1911   (1)| 00:00:23 |
|   1 |  SORT ORDER BY                          |                           |     1 |   296 |  1911   (1)| 00:00:23 |
|   2 |   HASH GROUP BY                         |                           |     1 |   296 |  1911   (1)| 00:00:23 |
|*  3 |    FILTER                               |                           |       |       |            |          |
|   4 |     NESTED LOOPS OUTER                  |                           |     1 |   296 |  1909   (1)| 00:00:23 |
|   5 |      NESTED LOOPS OUTER                 |                           |     1 |   265 |  1907   (1)| 00:00:23 |
|   6 |       NESTED LOOPS OUTER                |                           |     1 |   229 |  1905   (1)| 00:00:23 |
|   7 |        NESTED LOOPS OUTER               |                           |     1 |   204 |  1901   (1)| 00:00:23 |
|*  8 |         FILTER                          |                           |       |       |            |          |
|   9 |          NESTED LOOPS OUTER             |                           |     1 |   174 |  1900   (1)| 00:00:23 |
|  10 |           NESTED LOOPS                  |                           |     1 |   161 |  1897   (1)| 00:00:23 |
|  11 |            NESTED LOOPS                 |                           |     1 |   142 |  1895   (1)| 00:00:23 |
|* 12 |             HASH JOIN                   |                           |     1 |   123 |  1893   (1)| 00:00:23 |
|* 13 |              TABLE ACCESS BY INDEX ROWID| REPORT_PRODUCTION_ITEMS   |     1 |    67 |     3   (0)| 00:00:01 |
|  14 |               NESTED LOOPS              |                           |     3 |   291 |  1690   (1)| 00:00:21 |
|* 15 |                HASH JOIN                |                           |     3 |    90 |  1683   (1)| 00:00:21 |
|  16 |                 TABLE ACCESS FULL       | ID_EVENTS_ADDITIONAL_DATA |   935 | 17765 |     6   (0)| 00:00:01 |
|  17 |                 INLIST ITERATOR         |                           |       |       |            |          |
|* 18 |                  INDEX RANGE SCAN       | SYEV_EVNA_FK_I            |   432K|  4643K|  1674   (1)| 00:00:21 |
|* 19 |                INDEX RANGE SCAN         | RPIT_EVEN_FK_I            |     3 |       |     2   (0)| 00:00:01 |
|* 20 |              TABLE ACCESS BY INDEX ROWID| MEMBER_PRODUCTS           |    91 |  2366 |   202   (0)| 00:00:03 |
|* 21 |               INDEX RANGE SCAN          | MEPR_INTE_FK_I            |  1818 |       |     8   (0)| 00:00:01 |
|  22 |             TABLE ACCESS BY INDEX ROWID | CONTRACTS                 |     1 |    19 |     2   (0)| 00:00:01 |
|* 23 |              INDEX UNIQUE SCAN          | CNTR_PK                   |     1 |       |     1   (0)| 00:00:01 |
|  24 |            TABLE ACCESS BY INDEX ROWID  | REGISTRATIONS             |     1 |    19 |     2   (0)| 00:00:01 |
|* 25 |             INDEX UNIQUE SCAN           | REGI_PK                   |     1 |       |     1   (0)| 00:00:01 |
|  26 |           TABLE ACCESS BY INDEX ROWID   | GROUP_EVENTS              |     1 |    13 |     3   (0)| 00:00:01 |
|* 27 |            INDEX RANGE SCAN             | GREV_EVEN_FK_I            |     1 |       |     2   (0)| 00:00:01 |
|  28 |         TABLE ACCESS BY INDEX ROWID     | GROUPS                    |     1 |    30 |     1   (0)| 00:00:01 |
|* 29 |          INDEX UNIQUE SCAN              | GROU_PK                   |     1 |       |     0   (0)| 00:00:01 |
|  30 |        TABLE ACCESS BY INDEX ROWID      | MEMBER_EVENTS             |     2 |    50 |     4   (0)| 00:00:01 |
|* 31 |         INDEX RANGE SCAN                | MEEV_EVEN_FK_I            |     2 |       |     3   (0)| 00:00:01 |
|  32 |       TABLE ACCESS BY INDEX ROWID       | MEMBERS                   |     1 |    36 |     2   (0)| 00:00:01 |
|* 33 |        INDEX UNIQUE SCAN                | MEMB_PK                   |     1 |       |     1   (0)| 00:00:01 |
|* 34 |      INDEX RANGE SCAN                   | MERE_PK                   |     1 |    31 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("GEV"."GROUP_ID"="REG"."GROUP_ID" OR "MREG"."REGISTRATION_ID"="REG"."REGISTRATION_ID")
   8 - filter("GEV"."GROUP_ID"="CON"."GROUP_ID" OR "REG"."REGISTRATION_ID"="CON"."REGISTRATION_ID" AND 
              "CON"."REGISTRATION_ID" IS NOT NULL)
  12 - access("MP"."PRODUCT_ID"="RPIT"."PRODUCT_ID")
  13 - filter("RPIT"."PDF_FILENAME" IS NOT NULL AND (("RPIT"."NOTIFICATION_STATUS_CODE"='B' OR 
              "RPIT"."NOTIFICATION_STATUS_CODE"='E' OR "RPIT"."NOTIFICATION_STATUS_CODE"='P') OR 
              ("RPIT"."REPORT_PRINT_STATUS"='I' OR "RPIT"."REPORT_PRINT_STATUS"='P' OR "RPIT"."REPORT_PRINT_STATUS"='S')) 
              AND "RPIT"."REQUEST_DATETIME">=ADD_MONTHS("SWN_DATETIME"."SYSTEMDATE"(),-1))
  15 - access("SYSE"."EVENT_ID"=NVL("IEAD"."EVENT_ID",0) AND "IEAD"."EVENT_ID"="SYSE"."EVENT_ID")
  18 - access("SYSE"."EVENT_NAME_CODE"='DNI' OR "SYSE"."EVENT_NAME_CODE"='DNS')
  19 - access("RPIT"."EVENT_ID"="SYSE"."EVENT_ID")
  20 - filter("MP"."REGISTRATION_ID" LIKE '_____-______')
  21 - access("MP"."INTERMEDIARY_ID"=1004)
  23 - access("CON"."CONTRACT_PK"="MP"."CONTRACT_PK")
       filter("SWN_DGP_IDGP"."ISCURRENTCONTRACT"("CON"."CONTRACT_PK")='Y')
  25 - access("REG"."REGISTRATION_ID"="MP"."REGISTRATION_ID")
  27 - access("SYSE"."EVENT_ID"="GEV"."EVENT_ID"(+))
  29 - access("GEV"."GROUP_ID"="G"."GROUP_ID"(+))
  31 - access("SYSE"."EVENT_ID"="MEV"."EVENT_ID"(+))
  33 - access("MEV"."MEMBER_ID"="MEM"."MEMBER_ID"(+))
  34 - access("MEM"."MEMBER_ID"="MREG"."MEMBER_ID"(+))
       filter("MREG"."MEMBER_ID"(+) LIKE '__-____-____-____')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) TO_DATE(INTERNAL_FUNCTION("RPIT"."REQUEST_DATETIME"),'DD/MM/RRRR')[7], 
       "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], TO_DATE(INTERNAL_FUNCTION("RPIT"."REQUEST_DATETIME"),'DD/MM/RRRR')[
       8], "IEAD"."CUSTOMER_REF"[VARCHAR2,12], CASE  WHEN INITCAP("G"."GROUP_NAME") IS NULL THEN 
       INITCAP("MEM"."GIVEN_NAME")||' '||INITCAP("MEM"."FAMILY_NAME") ELSE "G"."GROUP_NAME" END [81], 
       "IEAD"."STAGE_NO"[NUMBER,22], "SWN_DOCUMENTINDEXING"."GETDOCNEWPATH"("RPIT"."PDF_FILENAME")[4000]
   2 - (#keys=8) "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REQUEST_DATETIME"[DATE,7], 
       "G"."GROUP_NAME"[VARCHAR2,40], "MEM"."GIVEN_NAME"[VARCHAR2,30], "MEM"."FAMILY_NAME"[VARCHAR2,50], 
       "IEAD"."STAGE_NO"[NUMBER,22], "RPIT"."PDF_FILENAME"[VARCHAR2,100], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22]
   3 - "IEAD"."STAGE_NO"[NUMBER,22], "IEAD"."CUSTOMER_REF"[VARCHAR2,12], 
       "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], "RPIT"."REQUEST_DATETIME"[DATE,7], 
       "RPIT"."PDF_FILENAME"[VARCHAR2,100], "G"."GROUP_NAME"[VARCHAR2,40], "MEM"."GIVEN_NAME"[VARCHAR2,30], 
       "MEM"."FAMILY_NAME"[VARCHAR2,50]
   4 - (#keys=0) "IEAD"."STAGE_NO"[NUMBER,22], "IEAD"."CUSTOMER_REF"[VARCHAR2,12], 
       "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], "RPIT"."REQUEST_DATETIME"[DATE,7], 
       "RPIT"."PDF_FILENAME"[VARCHAR2,100], "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5], 
       "GEV"."GROUP_ID"[VARCHAR2,5], "G"."GROUP_NAME"[VARCHAR2,40], "MEM"."GIVEN_NAME"[VARCHAR2,30], 
       "MEM"."FAMILY_NAME"[VARCHAR2,50], "MREG"."REGISTRATION_ID"[VARCHAR2,12]
   5 - (#keys=0) "IEAD"."STAGE_NO"[NUMBER,22], "IEAD"."CUSTOMER_REF"[VARCHAR2,12], 
       "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], "RPIT"."REQUEST_DATETIME"[DATE,7], 
       "RPIT"."PDF_FILENAME"[VARCHAR2,100], "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5], 
       "GEV"."GROUP_ID"[VARCHAR2,5], "G"."GROUP_NAME"[VARCHAR2,40], "MEM"."MEMBER_ID"[VARCHAR2,17], 
       "MEM"."GIVEN_NAME"[VARCHAR2,30], "MEM"."FAMILY_NAME"[VARCHAR2,50]
   6 - (#keys=0) "IEAD"."STAGE_NO"[NUMBER,22], "IEAD"."CUSTOMER_REF"[VARCHAR2,12], 
       "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], "RPIT"."REQUEST_DATETIME"[DATE,7], 
       "RPIT"."PDF_FILENAME"[VARCHAR2,100], "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5], 
       "GEV"."GROUP_ID"[VARCHAR2,5], "G"."GROUP_NAME"[VARCHAR2,40], "MEV"."MEMBER_ID"[VARCHAR2,17]
   7 - (#keys=0) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], 
       "RPIT"."REQUEST_DATETIME"[DATE,7], "RPIT"."PDF_FILENAME"[VARCHAR2,100], 
       "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5], "GEV"."GROUP_ID"[VARCHAR2,5], 
       "G"."GROUP_NAME"[VARCHAR2,40]
   8 - "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], 
       "RPIT"."REQUEST_DATETIME"[DATE,7], "RPIT"."PDF_FILENAME"[VARCHAR2,100], 
       "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5], "GEV"."GROUP_ID"[VARCHAR2,5]
   9 - (#keys=0) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], 
       "RPIT"."REQUEST_DATETIME"[DATE,7], "RPIT"."PDF_FILENAME"[VARCHAR2,100], "CON"."GROUP_ID"[VARCHAR2,5], 
       "CON"."REGISTRATION_ID"[VARCHAR2,12], "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5], 
       "GEV"."GROUP_ID"[VARCHAR2,5]
  10 - (#keys=0) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], 
       "RPIT"."REQUEST_DATETIME"[DATE,7], "RPIT"."PDF_FILENAME"[VARCHAR2,100], "CON"."GROUP_ID"[VARCHAR2,5], 
       "CON"."REGISTRATION_ID"[VARCHAR2,12], "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5]
  11 - (#keys=0) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], 
       "RPIT"."REQUEST_DATETIME"[DATE,7], "RPIT"."PDF_FILENAME"[VARCHAR2,100], "MP"."REGISTRATION_ID"[VARCHAR2,12], 
       "CON"."GROUP_ID"[VARCHAR2,5], "CON"."REGISTRATION_ID"[VARCHAR2,12]
  12 - (#keys=1) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], 
       "RPIT"."REQUEST_DATETIME"[DATE,7], "RPIT"."PDF_FILENAME"[VARCHAR2,100], "MP"."REGISTRATION_ID"[VARCHAR2,12], 
       "MP"."CONTRACT_PK"[NUMBER,22]
  13 - "RPIT"."REPORT_PRODUCTION_ID"[NUMBER,22], "RPIT"."REQUEST_DATETIME"[DATE,7], 
       "RPIT"."PRODUCT_ID"[NUMBER,22], "RPIT"."PDF_FILENAME"[VARCHAR2,100]
  14 - (#keys=0) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "RPIT".ROWID[ROWID,10]
  15 - (#keys=2) "SYSE"."EVENT_ID"[NUMBER,22], "SYSE"."EVENT_ID"[NUMBER,22], "IEAD"."STAGE_NO"[NUMBER,22], 
       "IEAD"."CUSTOMER_REF"[VARCHAR2,12]
  16 - "IEAD"."EVENT_ID"[NUMBER,22], "IEAD"."CUSTOMER_REF"[VARCHAR2,12], "IEAD"."STAGE_NO"[NUMBER,22]
  17 - "SYSE"."EVENT_ID"[NUMBER,22]
  18 - "SYSE"."EVENT_ID"[NUMBER,22]
  19 - "RPIT".ROWID[ROWID,10]
  20 - "MP"."REGISTRATION_ID"[VARCHAR2,12], "MP"."CONTRACT_PK"[NUMBER,22], "MP"."PRODUCT_ID"[NUMBER,22]
  21 - "MP".ROWID[ROWID,10]
  22 - "CON"."GROUP_ID"[VARCHAR2,5], "CON"."REGISTRATION_ID"[VARCHAR2,12]
  23 - "CON".ROWID[ROWID,10]
  24 - "REG"."REGISTRATION_ID"[VARCHAR2,12], "REG"."GROUP_ID"[VARCHAR2,5]
  25 - "REG".ROWID[ROWID,10], "REG"."REGISTRATION_ID"[VARCHAR2,12]
  26 - "GEV"."GROUP_ID"[VARCHAR2,5]
  27 - "GEV".ROWID[ROWID,10]
  28 - "G"."GROUP_NAME"[VARCHAR2,40]
  29 - "G".ROWID[ROWID,10]
  30 - "MEV"."MEMBER_ID"[VARCHAR2,17]
  31 - "MEV".ROWID[ROWID,10]
  32 - "MEM"."MEMBER_ID"[VARCHAR2,17], "MEM"."GIVEN_NAME"[VARCHAR2,30], "MEM"."FAMILY_NAME"[VARCHAR2,50]
  33 - "MEM".ROWID[ROWID,10], "MEM"."MEMBER_ID"[VARCHAR2,17]
  34 - "MREG"."REGISTRATION_ID"[VARCHAR2,12]

-------------------------------------------------------------------------------
Edited by: BluShadow on 23-Aug-2012 13:28
code tags edited

Answers

  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    Edit your post, change
     into [code] (that is: lowercase) so we're able to read your sample.
    Also post the results of: select * from v$version;                                                                                                                                                                                                                                                                                                            
  • chris227
    chris227 Member Posts: 3,513 Bronze Crown
    edited Aug 23, 2012 6:54AM
    hi,

    does the problem reside in this row?
    ON syse.event_id = NVL(iead.EVENT_ID,0) and iead.event_id =
    syse.event_id 
    There is a full table scan on ID_EVENTS_ADDITIONAL_DATA IEAD probably because of the NVL. (nulls are not indexed)
    So do you need this in fact or could you use an outer join here?

    regards
  • 950472
    950472 Member Posts: 10
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE 10.2.0.5.0 Production
    TNS for Solaris: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production

    Initial I thought that there is the problem; I noticed that the query runs much faster if added
    iead.event_id =syse.event_id
    although there is no index on iead.EVENT_ID instead there is a function index on
    NVL(iead.EVENT_ID,0) 
    I would expect a full scan because this table is only 10k rows

    Regards
This discussion has been closed.