Hello,
I am fairly new with ORacle SQL and am stuck at one point.
I am trying to run the following update statement on a daily fact table, and it is running for ever. Is there a way to optimise the below sql, do i need to do any preprocessing or add any idex?
Or is it just wrong to use such a long in-list on a substr function
UPDATE EDI_867_DLY_FACT a SET OVRD_CUST_CD = 3000, audt_upd_dt=sysdate
WHERE (EDI_SNDR_ID, CUST_CD) IN (SELECT SNDR_ID, SOLD_TO FROM EDI_RULE_SNDR WHERE RULE_KEY= 1)
AND SUBSTR(POC_ZIP_CD,1,3) IN ('500','501','502','503','504','505','506','507','511','513','514','515','520','522','523','524','525','526','527','528','620','622','628','630','631','633','634','635','636','640','641','644','645','647','648','650','651','652','653','654','655','656','657','658','660','661','662','665','666','667','668','670','671','672','673','674','675','676','678','679','680','681','684','685','686','687','688','689','716','717','719','720','721','722','725','726','728','740','741','743','744')
AND OVRD_INV_DT='23-SEP-16'Appreciate all your help! Thanks in advance
To help with SQL performance, we really need to see the execution plan. This contains details of what the query did.
You can generate one as follows:
set serveroutput off
update /*+ gather_plan_statistics */<your update>
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
You can find other ways to do this and read more about plans at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan do i need to do any preprocessing or add any index?An index may be helpful, but we really need to see the plan to answer that.
Or is it just wrong to use such a long in-list on a substr functionAn in-list is shorthand for a series of ORs. A large list (probably) means you're accessing more rows, which in turn makes an index less useful.
Substr isn't a problem in itself. But generally you need to create an index that includes substr exactly as you use it in your query.
Read more about indexes at:
https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database