Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this query:

UPDATE   (
    SELECT   h.valid_through_dt, h.LAST_UPDATE_TMSTMP
    FROM   ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h
    WHERE       h.FUND_ID = d.FUND_ID
    AND h.FEETYPE_NAME = d.FEETYPE_NAME
    AND h.BREAKPOINT_TYPE = d.BREAKPOINT_TYPE
    AND h.BREAKPOINT_QTY = d.BREAKPOINT_QTY
    AND h.LOW_BREAKPOINT_AMT = d.LOW_BREAKPOINT_AMT
    AND h.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy')
    AND h.universe = 'DC'
    AND h.universe = d.universe
    AND EXISTS
    (
        SELECT 1
        FROM FEE_SCHEDULE s
        WHERE s.FUND_ID = h.FUND_ID
        AND s.FEETYPE_NAME = h.FEETYPE_NAME
        AND s.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE
        AND s.BREAKPOINT_QTY = h.BREAKPOINT_QTY
        AND s.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT
        AND s.universe = 'DC'
    )
) updateTable
SET     updateTable.VALID_THROUGH = (SYSDATE - 1),
updateTable.LAST_UPDATE_TMSTMP = SYSTIMESTAMP;

The trouble that I am having is that this query takes a long time to run. I don't know whether it is possible to run this on parallel, or it would be easier to update a cursor in a pipeline function.

What would you suggest?

This is all the information that I believe it is relevant.

This is the execution plan of the internal select:

Execution Plan
----------------------------------------------------------
Plan hash value: 57376096
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |   306 |  8427   (1)| 00:01:42 |
|   1 |  NESTED LOOPS                |                          |       |       |            |          |
|   2 |   NESTED LOOPS               |                          |     1 |    306|  8427   (1)| 00:01:42 |
|   3 |    MERGE JOIN CARTESIAN      |                          |     1 |    192|  8426   (1)| 00:01:42 |
|*  4 |     INDEX RANGE SCAN         | SYS_C000666              |     1 |     96|     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT              |                          |  3045K|   278M|  8425   (1)| 00:01:42 |
|   6 |      SORT UNIQUE             |                          |  3045K|   278M|  8425   (1)| 00:01:42 |
|*  7 |       TABLE ACCESS FULL      | FEE_SCHEDULE             |  3045K|   278M|  8425   (1)| 00:01:42 |
|*  8 |    INDEX RANGE SCAN          | FEE_SCHDL_IDX1           |     1 |       |     1   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID| FEE_SCHEDULE_HISTORICAL  |     1 |   114 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."UNIVERSE"='DC')
   7 - filter("S"."UNIVERSE"='DC')
   8 - access("H"."UNIVERSE"='DC' AND "S"."FUND_ID"="H"."FUND_ID" AND
              "S"."FEETYPE_NAME"="H"."FEETYPE_NAME" AND
              "S"."BREAKPOINT_TYPE"="H"."BREAKPOINT_TYPE" AND
              "S"."BREAKPOINT_QTY"="H"."BREAKPOINT_QTY" AND
              "S"."LOW_BREAKPOINT_AMT"="H"."LOW_BREAKPOINT_AMT")
       filter("H"."FUND_ID"="D"."FUND_ID" AND
              "H"."FEETYPE_NAME"="D"."FEETYPE_NAME" AND
              "H"."BREAKPOINT_TYPE"="D"."BREAKPOINT_UNIT_TY

Table data:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNIVERSE|FUND_ID   |FEETYPE_NAME |BREAKPOINT_TYPE|BREAKPOINT_QTY|LOW_BREAKPOINT_AMT|HIGH_BREAKPOINT_AMT|FEE_PCT|FEE_SCHDL_SEQ_ID|GROUP_ID|LAST_UPDATE_TMSTMP  |VALID_FROM|VALID_THROUGH|INSERT_TMSTMP        |JOB_ID|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DC      |DC9ZTPLPHO|DeferLoad    |Percentage     |4             |10000             |300000             |3.14   |780250          |null    |1/4/2012  3:59:54 PM|6/23/2012 |12/31/9999   |1/5/2011   3:59:54 PM|666   |
DC      |DCE86Y8XFU|RedemptionFee|Percentage     |9             |  100             |100500             |7.67   |780251          |null    |6/4/2012  4:49:54 PM|11/12/2011|12/31/9999   |8/17/2011  2:00:54 PM|666   |
DC      |DCAYL0KONA|FrontLoad    |Percentage     |2             |50000             |601500             |5.00   |780252          |null    |4/25/2012 4:49:54 PM|8/2/2012  |12/31/9999   |12/19/2012 9:59:00 PM|666   |
DC      |DC9ZTPLPHO|DeferLoad    |Percentage     |7             |80000             |900000             |2.24   |780252          |null    |4/25/2012 4:49:54 PM|8/2/2012  |12/31/9999   |12/19/2012 9:59:00 PM|666   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is the script of the historical table:

CREATE TABLE FEE_SCHEDULE_HISTORICAL
(
  UNIVERSE                        VARCHAR2(2 BYTE) NOT NULL,
  FUND_ID                         VARCHAR2(10 BYTE) NOT NULL,
  FEETYPE_NAME                    VARCHAR2(75 BYTE),
  BREAKPOINT_TYPE                 VARCHAR2(50 BYTE),
  BREAKPOINT_QTY                  VARCHAR2(10 BYTE),
  LOW_BREAKPOINT_AMT              NUMBER(19,6),
  HIGH_BREAKPOINT_AMT             NUMBER(19,6),
  FEE_PCT                         NUMBER(19,6),
  FEE_SCHDL_SEQ_ID                NUMBER        NOT NULL,
  GROUP_ID                        NUMBER,
  LAST_UPDATE_TMSTMP              DATE          NOT NULL,
  VALID_FROM                      DATE          NOT NULL,
  VALID_THROUGH                   DATE          NOT NULL,
  INSERT_TMSTMP                   DATE          NOT NULL,
  JOB_ID                          NUMBER        NOT NULL
);

CREATE UNIQUE INDEX FEE_SCHDL_PK ON FEE_SCHEDULE_HISTORICAL(FEE_SCHDL_SEQ_ID);

CREATE UNIQUE INDEX FEE_SCHDL_HST_IDX ON FEE_SCHEDULE_HISTORICAL (
    UNIVERSE,
    FUND_ID,
    FEETYPE_NAME,
    BREAKPOINT_TYPE,
    BREAKPOINT_QTY, 
    LOW_BREAKPOINT_AMT,
    VALID_FROM,
    JOB_ID
)

CREATE INDEX FEE_SCHEDULE_HST_IDX2 ON FEE_SCHEDULE_HISTORICAL(LAST_UPDATE_TMSTMP)

CREATE INDEX FEE_SCHEDULE_HST_IDX3 ON FEE_SCHEDULE_HISTORICAL(VALID_THROUGH)

ALTER TABLE FEE_SCHEDULE_HISTORICAL ADD (
    CONSTRAINT FEE_SCHDL_PK
    PRIMARY KEY
    (FEE_SCHDL_SEQ_ID)
);

This is the other table:

CREATE TABLE FEE_SCHEDULE
(
  UNIVERSE                        VARCHAR2(2 BYTE) NOT NULL,
  FUND_ID                         VARCHAR2(10 BYTE) NOT NULL,
  FEETYPE_NAME                    VARCHAR2(75 BYTE),
  BREAKPOINT_TYPE                 VARCHAR2(50 BYTE),
  BREAKPOINT_QTY                  VARCHAR2(10 BYTE),
  LOW_BREAKPOINT_AMT              NUMBER(19,6),
  HIGH_BREAKPOINT_AMT             NUMBER(19,6),
  FEE_PCT                         NUMBER(19,6),
  JOB_RUN_ID                      NUMBER        NOT NULL,
  FILE_DATE                       DATE          NOT NULL,
  CYCLE_DATE                      DATE          NOT NULL
)

The temporary table is the result of FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE

share|improve this question

migrated from stackoverflow.com Oct 2 '12 at 6:32

This question came from our site for professional and enthusiast programmers.

    
A question as detailed as this really should be on a DBA stack. –  pst Oct 1 '12 at 21:47
    
Wouldn't ..AND EXISTS (SELECT NULL.. always return false? Would this - ..AND NOT EXISTS (SELECT 1.. make more sense?? –  Annjawn Oct 1 '12 at 21:58
2  
@Annjawn Hi, according to this forums.oracle.com/forums/thread.jspa?threadID=353014 NULL or 1 are only "symbolic" name (they are never used outside the sub-select). –  StrayChild01 Oct 1 '12 at 22:06
    
Yes they are and I am aware of that. But its not what I asked? SELECT NULL... is plain confusing and shouldn't be used (unless you are using a UNION). –  Annjawn Oct 1 '12 at 22:09
3  
Did you try to add an index on fee_schedule (universe, fund_id, feetype_name, breakpoint_type, breakpoint_qty, low_breakpoint_amt). Maybe one on universe, fund_id is already good enough to turn that FTS into an index scan. –  a_horse_with_no_name Oct 2 '12 at 6:43
show 7 more comments

2 Answers 2

I'm assuming that your FEE_SCHEDULE table is a lot smaller than the FEE_SCHEDULE_HISTORICAL table, so you might want to exploit EXISTS a little more. Dipping into the FEE_SCHEDULE table on a row-by-row basis can be a relatively cheap operation compared to joining it to all the rows in the FEE_SCHEDULE_HISTORICAL .

Since the ETL_FEE_SCH_TMP table is the FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE, you can actually accomplish the update with just a couple of EXISTS statements, without all the joins and saving you the trouble of creating the temp table. You don't really need the temp table.

I think this might be worth a look :

update FEE_SCHEDULE_HISTORICAL H
set H.VALID_THROUGH = (sysdate - 1), H.LAST_UPDATE_TMSTMP = SYSTIMESTAMP
where 
    H.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy')
    AND H.universe = 'DC'
    AND NOT EXISTS
    (
    SELECT 1
        FROM FEE_SCHEDULE F
        WHERE 
            F.universe = H.Universe
            AND F.FUND_ID = H.FUND_ID
            AND F.FEETYPE_NAME = H.FEETYPE_NAME
            AND F.BREAKPOINT_TYPE = H.BREAKPOINT_TYPE
            AND F.BREAKPOINT_QTY = H.BREAKPOINT_QTY
            AND F.LOW_BREAKPOINT_AMT = H.LOW_BREAKPOINT_AMT
            AND F.HIGH_BREAKPOINT_AMT = H.HIGH_BREAKPOINT_AMT
            AND F.FEE_PCT = H.FEE_PCT
    )
    AND EXISTS
        (
        SELECT 1
        FROM FEE_SCHEDULE FF
        WHERE 
            FF.universe = 'DC'
            AND FF.FUND_ID = h.FUND_ID
            AND FF.FEETYPE_NAME = h.FEETYPE_NAME
            AND FF.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE
            AND FF.BREAKPOINT_QTY = h.BREAKPOINT_QTY
            AND FF.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT
  )

Also, consider adding an index to the FEE_SCHEDULE table, similar to the one on the FEE_SCHEDULE_HISTORICAL. That really helps the explain plan along.

CREATE UNIQUE INDEX FEE_SCHDL_IDX ON FEE_SCHEDULE (
    UNIVERSE,
    FUND_ID,
    FEETYPE_NAME,
    BREAKPOINT_TYPE,
    BREAKPOINT_QTY, 
    LOW_BREAKPOINT_AMT
);
share|improve this answer
add comment

Did you try running SQL Tuning Advisor for you statement? It usually finds missing indexes, statistics, costly operations etc. This is fairly easy to do using SQL developer eg. see http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.