CODEWITHSUNDEEP
sqloracle-databaseperformanceinformatica
sagi
sagi

Reputation: 40481

Improve SELECT and UPDATE performance

I'll try to explain my issue, since I'm not using SQL directly.

I'm using INFORMATICA tool by using mappings that process SQL data, so I'll try to explain the logic my map does into SQL.

My map basically select data from an SCD (slowly changing dimension) where start_date = sysdate and ind = 1 (this table has approximately 600mil records) using this query:

SELECT table.ACCOUNT_NUMBER, table.SUB_ACCOUNT_NUMBER, table.SUB_ACCOUNT_KEY 
FROM table
WHERE table.CURR_IND=1
  AND table.START_DATE=trunc(sysdate)

This table is indexes as following:

SUB_ACCOUNT_KEY - UNIQUE

Then add another column and update a different table that have approximately 8mil records . The query of that is probably update with join by 

SET table2.ind =The_New_Column,table_2.sub_account_key = table1.sub_account_key
WHERE Table.account_number = Table_2.account_number
  AND table.sub_account_number = table_2.sub_account_number

This table_2 is indexes as following:

(ACCOUNT_NUMBER, SUB_ACCOUNT_NUMBER) - UNIQUE

Both select and update take some time to process depending on the amount of data I get each day(We have 1 day each three month that the amount of data is about X30 of a normal day which take for ever.. about 2 hours)

So, my question is: How can I speed this process up having the following limitation :

I can't (unless given a very good reason) adding an index on the tables since it is being used in many other processes , so it can harm their performances

Upvotes: 0

Views: 178

Answers (2)

EdStevens
EdStevens

Reputation: 3872

This is essentially the same question you asked under "get current date fomatted". You are either going to have to modify your sql, or use a function based index. Yes, indexes can cause some additional overhead on DML, but can give dramatic improvement on SELECTs. Like all design decisions, you have weigh the benefit to cost and decide what is more important.

Upvotes: 0

mi_h
mi_h

Reputation: 54

suggestion 1: create a function based index:

CREATE INDEX index_name
          ON table (TRUNC(START_DATE));

as you mentioned, this might not be possible because you can't use indexes.

suggestion 2: use BETWEEN:

SELECT table.ACCOUNT_NUMBER, table.SUB_ACCOUNT_NUMBER, table.SUB_ACCOUNT_KEY 
  FROM table
 WHERE table.CURR_IND=1
   AND table.START_DATE BETWEEN TO_DATE('2016.02.14 12:00:00 AM', 'YYYY.MM.DD HH:MI:SS AM') 
                            AND TO_DATE('2016.02.15 11:59:59 PM', 'YYYY.MM.DD HH:MI:SS PM');

(see also http://oraclecoder.com/tutorials/quick-tip-do-not-use-trunc-to-filter-on-a-date-and-time-field--2120)

Upvotes: 0

Related Questions