Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Why is index not used on update of an updatable view?

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;
/
Best 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
Answers
-
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