Forum Stats

  • 3,826,744 Users
  • 2,260,702 Discussions
  • 7,897,069 Comments

Discussions

Categories

Why is index not used on update of an updatable view?

zsysdba
zsysdba Member Posts: 70 Blue Ribbon
edited Oct 27, 2013 6:04PM in SQL & PL/SQL

I need help understanding why Oracle 11.2.0.3 ignores an index for an updatable view.  When I reference the base table, offer$, I get index access.  When I reference the view, offer, I get a full scan of offer$.  There is an instead of trigger on the view.  Does that factor into it somehow?

> EXPLAIN PLAN FOR

UPDATE OFFER$ SET PRESENTABLE_FLAG = 'F', ACCEPTED_OFFER_FLAG ='T' WHERE ASSIGNED_OFFER_ID = (SELECT SOLICITATION_REF_ID FROM SOLICITATION WHERE SOLICITATION_ID = :B1 )

plan FOR succeeded.

> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3047622554

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT               |                            |     1 |    25 |     2   (0)| 00:00:01 |

|   1 |  UPDATE                        | OFFER$                     |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID  | OFFER$                     |     1 |    25 |     1   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN           | AK_OFFER_ASSIGNED_OFFER_ID |     1 |       |     1   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| SOLICITATION$              |     1 |    20 |     1   (0)| 00:00:01 |

|*  5 |      INDEX UNIQUE SCAN         | PK_SOLICITATION$           |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - UPD$1

   2 - UPD$1        / OFFER$@UPD$1

   3 - UPD$1        / OFFER$@UPD$1

   4 - SEL$F5BB74E1 / SOLICITATION$@SEL$2

   5 - SEL$F5BB74E1 / SOLICITATION$@SEL$2

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("ASSIGNED_OFFER_ID"= (SELECT "SOLICITATION_REF_ID" FROM KOHP."SOLICITATION$"

              "SOLICITATION$" WHERE "SOLICITATION_ID"=TO_NUMBER(:B1)))

   5 - access("SOLICITATION_ID"=TO_NUMBER(:B1))

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   2 - (upd=4,6,7; cmp=3,7; cpy=2,4,5) "OFFER$".ROWID[ROWID,10], "OFFER$"."DECISION_ID"[NUMBER,22],

       "ASSIGNED_OFFER_ID"[VARCHAR2,255], "ACCEPTED_OFFER_FLAG"[CHARACTER,1],

       "OFFER$"."CURRENT_OFFER_FLAG"[CHARACTER,1], "PRESENTABLE_FLAG"[CHARACTER,1],

       "OFFER$"."CHNG_DTTM"[TIMESTAMP,11]

   3 - "OFFER$".ROWID[ROWID,10], "ASSIGNED_OFFER_ID"[VARCHAR2,255]

   4 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22],

       "SOLICITATION_REF_ID"[VARCHAR2,50]

   5 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22]

40 rows selected

> EXPLAIN PLAN FOR

UPDATE OFFER SET PRESENTABLE_FLAG = 'F', ACCEPTED_OFFER_FLAG ='T' WHERE ASSIGNED_OFFER_ID = (SELECT SOLICITATION_REF_ID FROM SOLICITATION WHERE SOLICITATION_ID = :B1 )

plan FOR succeeded.

> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1083159671

--------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT              |                  |   958K|   987M|  9182   (1)| 00:01:51 |

|   1 |  UPDATE                       | OFFER            |       |       |            |          |

|*  2 |   VIEW                        | OFFER            |   958K|   987M|  9181   (1)| 00:01:51 |

|   3 |    TABLE ACCESS FULL          | OFFER$           |   958K|   127M|  9181   (1)| 00:01:51 |

|   4 |    TABLE ACCESS BY INDEX ROWID| SOLICITATION$    |     1 |    20 |     1   (0)| 00:00:01 |

|*  5 |     INDEX UNIQUE SCAN         | PK_SOLICITATION$ |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - UPD$1

   2 - SEL$1        / [email protected]$1

   3 - SEL$1        / OFFER$@SEL$1

   4 - SEL$335DD26A / SOLICITATION$@SEL$3

   5 - SEL$335DD26A / SOLICITATION$@SEL$3

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ASSIGNED_OFFER_ID"= (SELECT "SOLICITATION_REF_ID" FROM

              KOHP."SOLICITATION$" "SOLICITATION$" WHERE "SOLICITATION_ID"=TO_NUMBER(:B1)))

   5 - access("SOLICITATION_ID"=TO_NUMBER(:B1))

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   2 - (upd=13,45; cmp=1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,20,21,22,23,24,25,26,2

       7,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,46,47,48,49,50)

       "OFFER"."OFFER_ID"[NUMBER,22], "OFFER"."DECISION_ID"[NUMBER,22],

       "OFFER"."OFFER_TYPE_CD"[NUMBER,22], "OFFER"."PROMO_ID"[NUMBER,22],

       "OFFER"."PYMT_METHOD_TYPE_CD"[NUMBER,22], "OFFER"."CS_RESULT_ID"[NUMBER,22],

       "OFFER"."CS_RESULT_USAGE_TYPE_CD"[NUMBER,22], "OFFER"."RATE_INDEX_TYPE_CD"[NUMBER,22],

       "OFFER"."SUB_PRODUCT_ID"[NUMBER,22], "OFFER"."CAMPAIGN_ID"[NUMBER,22],

       "OFFER"."MARKET_CELL_ID"[NUMBER,22], "ASSIGNED_OFFER_ID"[VARCHAR2,255],

       "ACCEPTED_OFFER_FLAG"[CHARACTER,1], "OFFER"."CURRENT_OFFER_FLAG"[CHARACTER,1],

       "OFFER"."OFFER_GOOD_UNTIL_DATE"[DATE,7], "OFFER"."RESCINDABLE_DAYS"[NUMBER,22],

       "OFFER"."RESCINDED_DATE"[DATE,7], "OFFER"."AMOUNT"[NUMBER,22],

       "OFFER"."MAX_AMOUNT"[NUMBER,22], "OFFER"."AMOUNT_FINANCED"[NUMBER,22],

       "OFFER"."DOWN_PYMT"[NUMBER,22], "OFFER"."RATE"[NUMBER,22], "OFFER"."TERM_MM"[NUMBER,22],

       "OFFER"."ORIGINATION_FEE_AMOUNT"[NUMBER,22], "OFFER"."ORIGINATION_FEE_RATE"[NUMBER,22],

       "OFFER"."FINANCE_CHARGE"[NUMBER,22], "OFFER"."NBR_OF_PYMTS"[NUMBER,22],

       "OFFER"."PYMT"[NUMBER,22], "OFFER"."TOTAL_PYMTS"[NUMBER,22],

       "OFFER"."FIRST_PYMT_DATE"[DATE,7], "OFFER"."CONTRACT_DATE"[DATE,7],

       "OFFER"."ACCT_NBR"[VARCHAR2,20], "OFFER"."ACCT_NBR_ASSIGNED_DTTM"[TIMESTAMP,11],

       "OFFER"."ACCT_EXPIRATION_DTTM"[DATE,8], "OFFER"."OFFER_DESC"[VARCHAR2,255],

       "OFFER"."MIN_RATE"[NUMBER,22], "OFFER"."MAX_RATE"[NUMBER,22],

       "OFFER"."MIN_AMOUNT"[NUMBER,22], "OFFER"."ANNUAL_FEE_AMOUNT"[NUMBER,22],

       "OFFER"."ANNUAL_FEE_WAIVED_MM"[NUMBER,22], "OFFER"."LATE_FEE_PERCENT"[NUMBER,22],

       "OFFER"."LATE_FEE_MIN_AMOUNT"[NUMBER,22], "OFFER"."OFFER_SALES_SCRIPT"[VARCHAR2,500],

       "OFFER"."OFFER_ORDER"[NUMBER,22], "PRESENTABLE_FLAG"[CHARACTER,1],

       "OFFER"."INDEX_RATE"[NUMBER,22], "OFFER"."ACTV_FLAG"[VARCHAR2,1],

       "OFFER"."CORRELATION_ID"[VARCHAR2,64], "OFFER"."OFFER_STATUS_TYPE_CD"[NUMBER,22],

       "OFFER"."PRESENTATION_INSTRUMENT_NBR"[VARCHAR2,20]

   3 - "OFFER_ID"[NUMBER,22], "DECISION_ID"[NUMBER,22], "OFFER_TYPE_CD"[NUMBER,22],

       "PROMO_ID"[NUMBER,22], "PYMT_METHOD_TYPE_CD"[NUMBER,22], "CS_RESULT_ID"[NUMBER,22],

       "CS_RESULT_USAGE_TYPE_CD"[NUMBER,22], "RATE_INDEX_TYPE_CD"[NUMBER,22],

       "SUB_PRODUCT_ID"[NUMBER,22], "CAMPAIGN_ID"[NUMBER,22], "MARKET_CELL_ID"[NUMBER,22],

       "ASSIGNED_OFFER_ID"[VARCHAR2,255], "ACCEPTED_OFFER_FLAG"[CHARACTER,1],

       "CURRENT_OFFER_FLAG"[CHARACTER,1], "OFFER_GOOD_UNTIL_DATE"[DATE,7],

       "RESCINDABLE_DAYS"[NUMBER,22], "RESCINDED_DATE"[DATE,7], "AMOUNT"[NUMBER,22],

       "MAX_AMOUNT"[NUMBER,22], "AMOUNT_FINANCED"[NUMBER,22], "DOWN_PYMT"[NUMBER,22],

       "RATE"[NUMBER,22], "TERM_MM"[NUMBER,22], "ORIGINATION_FEE_AMOUNT"[NUMBER,22],

       "ORIGINATION_FEE_RATE"[NUMBER,22], "FINANCE_CHARGE"[NUMBER,22],

       "NBR_OF_PYMTS"[NUMBER,22], "PYMT"[NUMBER,22], "TOTAL_PYMTS"[NUMBER,22],

       "FIRST_PYMT_DATE"[DATE,7], "CONTRACT_DATE"[DATE,7], "ACCT_NBR$"[RAW,40],

       "ACCT_NBR_ASSIGNED_DTTM"[TIMESTAMP,11], "ACCT_EXPIRATION_DTTM$"[RAW,40],

       "OFFER_DESC"[VARCHAR2,255], "MIN_RATE"[NUMBER,22], "MAX_RATE"[NUMBER,22],

       "MIN_AMOUNT"[NUMBER,22], "ANNUAL_FEE_AMOUNT"[NUMBER,22],

       "ANNUAL_FEE_WAIVED_MM"[NUMBER,22], "LATE_FEE_PERCENT"[NUMBER,22],

       "LATE_FEE_MIN_AMOUNT"[NUMBER,22], "OFFER_SALES_SCRIPT"[VARCHAR2,500],

       "OFFER_ORDER"[NUMBER,22], "PRESENTABLE_FLAG"[CHARACTER,1], "INDEX_RATE"[NUMBER,22],

       "ACTV_FLAG"[VARCHAR2,1], "CORRELATION_ID"[VARCHAR2,64],

       "OFFER_STATUS_TYPE_CD"[NUMBER,22], "PRESENTATION_INSTRUMENT_NBR$"[RAW,40]

   4 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22],

       "SOLICITATION_REF_ID"[VARCHAR2,50]

   5 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22]

82 rows selected

CREATE OR REPLACE FORCE VIEW "OFFER" ("OFFER_ID","DECISION_ID","OFFER_TYPE_CD","PROMO_ID","PYMT_METHOD_TYPE_CD","CS_RESULT_ID","CS_RESULT_USAGE_TYPE_CD","RATE_INDEX_TYPE_CD","SUB_PRODUCT_ID","CAMPAIGN_ID","MARKET_CELL_ID","ASSIGNED_OFFER_ID","ACCEPTED_OFFER_FLAG","CURRENT_OFFER_FLAG","OFFER_GOOD_UNTIL_DATE","RESCINDABLE_DAYS","RESCINDED_DATE","AMOUNT","MAX_AMOUNT","AMOUNT_FINANCED","DOWN_PYMT","RATE","TERM_MM","ORIGINATION_FEE_AMOUNT","ORIGINATION_FEE_RATE","FINANCE_CHARGE","NBR_OF_PYMTS","PYMT","TOTAL_PYMTS","FIRST_PYMT_DATE","CONTRACT_DATE","ACCT_NBR","ACCT_NBR_ASSIGNED_DTTM","ACCT_EXPIRATION_DTTM","OFFER_DESC","MIN_RATE","MAX_RATE","MIN_AMOUNT","ANNUAL_FEE_AMOUNT","ANNUAL_FEE_WAIVED_MM","LATE_FEE_PERCENT","LATE_FEE_MIN_AMOUNT","OFFER_SALES_SCRIPT","OFFER_ORDER","PRESENTABLE_FLAG","INDEX_RATE","INSRT_DTTM","INSRT_USR_ID","CHNG_DTTM","CHNG_USR_ID","ACTV_FLAG","CORRELATION_ID","OFFER_STATUS_TYPE_CD","PRESENTATION_INSTRUMENT_NBR")

AS

  SELECT

    OFFER_ID

  ,DECISION_ID

  ,OFFER_TYPE_CD

  ,PROMO_ID

  ,PYMT_METHOD_TYPE_CD

  ,CS_RESULT_ID

  ,CS_RESULT_USAGE_TYPE_CD

  ,RATE_INDEX_TYPE_CD

  ,SUB_PRODUCT_ID

  ,CAMPAIGN_ID

  ,MARKET_CELL_ID

  ,ASSIGNED_OFFER_ID

  ,ACCEPTED_OFFER_FLAG

  ,CURRENT_OFFER_FLAG

  ,OFFER_GOOD_UNTIL_DATE

  ,RESCINDABLE_DAYS

  ,RESCINDED_DATE

  ,AMOUNT

  ,MAX_AMOUNT

  ,AMOUNT_FINANCED

  ,DOWN_PYMT

  ,RATE

  ,TERM_MM

  ,ORIGINATION_FEE_AMOUNT

  ,ORIGINATION_FEE_RATE

  ,FINANCE_CHARGE

  ,NBR_OF_PYMTS

  ,PYMT

  ,TOTAL_PYMTS

  ,FIRST_PYMT_DATE

  ,CONTRACT_DATE

  ,CAST ( zcrypto.decrypt ( ACCT_NBR$ ) AS VARCHAR2 ( 20 ) ) ACCT_NBR

  ,ACCT_NBR_ASSIGNED_DTTM

  ,TO_DATE ( zcrypto.decrypt ( ACCT_EXPIRATION_DTTM$ ) ) ACCT_EXPIRATION_DTTM

  ,OFFER_DESC

  ,MIN_RATE

  ,MAX_RATE

  ,MIN_AMOUNT

  ,ANNUAL_FEE_AMOUNT

  ,ANNUAL_FEE_WAIVED_MM

  ,LATE_FEE_PERCENT

  ,LATE_FEE_MIN_AMOUNT

  ,OFFER_SALES_SCRIPT

  ,OFFER_ORDER

  ,PRESENTABLE_FLAG

  ,INDEX_RATE

  ,INSRT_DTTM

  ,INSRT_USR_ID

  ,CHNG_DTTM

  ,CHNG_USR_ID

  ,ACTV_FLAG

  ,CORRELATION_ID

  ,OFFER_STATUS_TYPE_CD

  ,CAST ( zcrypto.decrypt ( PRESENTATION_INSTRUMENT_NBR$ ) AS VARCHAR2 ( 20 ) ) PRESENTATION_INSTRUMENT_NBR

  FROM

    OFFER$;

CREATE OR REPLACE TRIGGER "OFFER_IO" INSTEAD OF

  INSERT OR

  UPDATE

    ON OFFER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ( INSERTING ) THEN

  INSERT

  INTO

    OFFER$

    (

      OFFER_ID

    ,DECISION_ID

    ,OFFER_TYPE_CD

    ,PROMO_ID

    ,PYMT_METHOD_TYPE_CD

    ,CS_RESULT_ID

    ,CS_RESULT_USAGE_TYPE_CD

    ,RATE_INDEX_TYPE_CD

    ,SUB_PRODUCT_ID

    ,CAMPAIGN_ID

    ,MARKET_CELL_ID

    ,ASSIGNED_OFFER_ID

    ,ACCEPTED_OFFER_FLAG

    ,CURRENT_OFFER_FLAG

    ,OFFER_GOOD_UNTIL_DATE

    ,RESCINDABLE_DAYS

    ,RESCINDED_DATE

    ,AMOUNT

    ,MAX_AMOUNT

    ,AMOUNT_FINANCED

    ,DOWN_PYMT

    ,RATE

    ,TERM_MM

    ,ORIGINATION_FEE_AMOUNT

    ,ORIGINATION_FEE_RATE

    ,FINANCE_CHARGE

    ,NBR_OF_PYMTS

    ,PYMT

    ,TOTAL_PYMTS

    ,FIRST_PYMT_DATE

    ,CONTRACT_DATE

    ,ACCT_NBR$

    ,ACCT_NBR_ASSIGNED_DTTM

    ,ACCT_EXPIRATION_DTTM$

    ,OFFER_DESC

    ,MIN_RATE

    ,MAX_RATE

    ,MIN_AMOUNT

    ,ANNUAL_FEE_AMOUNT

    ,ANNUAL_FEE_WAIVED_MM

    ,LATE_FEE_PERCENT

    ,LATE_FEE_MIN_AMOUNT

    ,OFFER_SALES_SCRIPT

    ,OFFER_ORDER

    ,PRESENTABLE_FLAG

    ,INDEX_RATE

    ,ACTV_FLAG

    ,CORRELATION_ID

    ,OFFER_STATUS_TYPE_CD

    ,PRESENTATION_INSTRUMENT_NBR$

    )

    VALUES

    (

      :NEW.OFFER_ID

    ,:NEW.DECISION_ID

    ,:NEW.OFFER_TYPE_CD

    ,:NEW.PROMO_ID

    ,:NEW.PYMT_METHOD_TYPE_CD

    ,:NEW.CS_RESULT_ID

    ,:NEW.CS_RESULT_USAGE_TYPE_CD

    ,:NEW.RATE_INDEX_TYPE_CD

    ,:NEW.SUB_PRODUCT_ID

    ,:NEW.CAMPAIGN_ID

    ,:NEW.MARKET_CELL_ID

    ,:NEW.ASSIGNED_OFFER_ID

    ,:NEW.ACCEPTED_OFFER_FLAG

    ,:NEW.CURRENT_OFFER_FLAG

    ,:NEW.OFFER_GOOD_UNTIL_DATE

    ,:NEW.RESCINDABLE_DAYS

    ,:NEW.RESCINDED_DATE

    ,:NEW.AMOUNT

    ,:NEW.MAX_AMOUNT

    ,:NEW.AMOUNT_FINANCED

    ,:NEW.DOWN_PYMT

    ,:NEW.RATE

    ,:NEW.TERM_MM

    ,:NEW.ORIGINATION_FEE_AMOUNT

    ,:NEW.ORIGINATION_FEE_RATE

    ,:NEW.FINANCE_CHARGE

    ,:NEW.NBR_OF_PYMTS

    ,:NEW.PYMT

    ,:NEW.TOTAL_PYMTS

    ,:NEW.FIRST_PYMT_DATE

    ,:NEW.CONTRACT_DATE

    ,zcrypto.encrypt ( :NEW.ACCT_NBR )

    ,:NEW.ACCT_NBR_ASSIGNED_DTTM

    ,zcrypto.encrypt ( :NEW.ACCT_EXPIRATION_DTTM )

    ,:NEW.OFFER_DESC

    ,:NEW.MIN_RATE

    ,:NEW.MAX_RATE

    ,:NEW.MIN_AMOUNT

    ,:NEW.ANNUAL_FEE_AMOUNT

    ,:NEW.ANNUAL_FEE_WAIVED_MM

    ,:NEW.LATE_FEE_PERCENT

    ,:NEW.LATE_FEE_MIN_AMOUNT

    ,:NEW.OFFER_SALES_SCRIPT

    ,:NEW.OFFER_ORDER

    ,:NEW.PRESENTABLE_FLAG

    ,:NEW.INDEX_RATE

    ,:NEW.ACTV_FLAG

    ,:NEW.CORRELATION_ID

    ,:NEW.OFFER_STATUS_TYPE_CD

    ,zcrypto.encrypt ( :NEW.PRESENTATION_INSTRUMENT_NBR )

    );

ELSE

  UPDATE

    OFFER$

  SET

    DECISION_ID                 = :NEW.DECISION_ID

  ,OFFER_TYPE_CD                = :NEW.OFFER_TYPE_CD

  ,PROMO_ID                     = :NEW.PROMO_ID

  ,PYMT_METHOD_TYPE_CD          = :NEW.PYMT_METHOD_TYPE_CD

  ,CS_RESULT_ID                 = :NEW.CS_RESULT_ID

  ,CS_RESULT_USAGE_TYPE_CD      = :NEW.CS_RESULT_USAGE_TYPE_CD

  ,RATE_INDEX_TYPE_CD           = :NEW.RATE_INDEX_TYPE_CD

  ,SUB_PRODUCT_ID               = :NEW.SUB_PRODUCT_ID

  ,CAMPAIGN_ID                  = :NEW.CAMPAIGN_ID

  ,MARKET_CELL_ID               = :NEW.MARKET_CELL_ID

  ,ASSIGNED_OFFER_ID            = :NEW.ASSIGNED_OFFER_ID

  ,ACCEPTED_OFFER_FLAG          = :NEW.ACCEPTED_OFFER_FLAG

  ,CURRENT_OFFER_FLAG           = :NEW.CURRENT_OFFER_FLAG

  ,OFFER_GOOD_UNTIL_DATE        = :NEW.OFFER_GOOD_UNTIL_DATE

  ,RESCINDABLE_DAYS             = :NEW.RESCINDABLE_DAYS

  ,RESCINDED_DATE               = :NEW.RESCINDED_DATE

  ,AMOUNT                       = :NEW.AMOUNT

  ,MAX_AMOUNT                   = :NEW.MAX_AMOUNT

  ,AMOUNT_FINANCED              = :NEW.AMOUNT_FINANCED

  ,DOWN_PYMT                    = :NEW.DOWN_PYMT

  ,RATE                         = :NEW.RATE

  ,TERM_MM                      = :NEW.TERM_MM

  ,ORIGINATION_FEE_AMOUNT       = :NEW.ORIGINATION_FEE_AMOUNT

  ,ORIGINATION_FEE_RATE         = :NEW.ORIGINATION_FEE_RATE

  ,FINANCE_CHARGE               = :NEW.FINANCE_CHARGE

  ,NBR_OF_PYMTS                 = :NEW.NBR_OF_PYMTS

  ,PYMT                         = :NEW.PYMT

  ,TOTAL_PYMTS                  = :NEW.TOTAL_PYMTS

  ,FIRST_PYMT_DATE              = :NEW.FIRST_PYMT_DATE

  ,CONTRACT_DATE                = :NEW.CONTRACT_DATE

  ,ACCT_NBR$                    = zcrypto.encrypt ( :NEW.ACCT_NBR )

  ,ACCT_NBR_ASSIGNED_DTTM       = :NEW.ACCT_NBR_ASSIGNED_DTTM

  ,ACCT_EXPIRATION_DTTM$        = zcrypto.encrypt ( :NEW.ACCT_EXPIRATION_DTTM )

  ,OFFER_DESC                   = :NEW.OFFER_DESC

  ,MIN_RATE                     = :NEW.MIN_RATE

  ,MAX_RATE                     = :NEW.MAX_RATE

  ,MIN_AMOUNT                   = :NEW.MIN_AMOUNT

  ,ANNUAL_FEE_AMOUNT            = :NEW.ANNUAL_FEE_AMOUNT

  ,ANNUAL_FEE_WAIVED_MM         = :NEW.ANNUAL_FEE_WAIVED_MM

  ,LATE_FEE_PERCENT             = :NEW.LATE_FEE_PERCENT

  ,LATE_FEE_MIN_AMOUNT          = :NEW.LATE_FEE_MIN_AMOUNT

  ,OFFER_SALES_SCRIPT           = :NEW.OFFER_SALES_SCRIPT

  ,OFFER_ORDER                  = :NEW.OFFER_ORDER

  ,PRESENTABLE_FLAG             = :NEW.PRESENTABLE_FLAG

  ,INDEX_RATE                   = :NEW.INDEX_RATE

  ,ACTV_FLAG                    = :NEW.ACTV_FLAG

  ,CORRELATION_ID               = :NEW.CORRELATION_ID

  ,OFFER_STATUS_TYPE_CD         = :NEW.OFFER_STATUS_TYPE_CD

  ,PRESENTATION_INSTRUMENT_NBR$ = zcrypto.encrypt ( :NEW.PRESENTATION_INSTRUMENT_NBR )

  WHERE

    OFFER_ID = :NEW.OFFER_ID;

END IF;

END;

/

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    Answer ✓ 
    zsysdba wrote:

I need help understanding why Oracle 11.2.0.3 ignores an index for an updatable view.  When I reference the base table, offer$, I get index access.  When I reference the view, offer, I get a full scan of offer$.  There is an instead of trigger on the view.  Does that factor into it somehow?

    Almost certainly.

    Even if the view didn't use function-calls and the trigger simply had "null;" as the body, the presence of the trigger seems to stop the optimizer from pushing the subquery inside the view and down to the index (and there's no change in 12c).

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    Answer ✓ 
    zsysdba wrote:

I need help understanding why Oracle 11.2.0.3 ignores an index for an updatable view.  When I reference the base table, offer$, I get index access.  When I reference the view, offer, I get a full scan of offer$.  There is an instead of trigger on the view.  Does that factor into it somehow?

    Almost certainly.

    Even if the view didn't use function-calls and the trigger simply had "null;" as the body, the presence of the trigger seems to stop the optimizer from pushing the subquery inside the view and down to the index (and there's no change in 12c).

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
This discussion has been closed.