Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue with an update statement
Performance issue with an update statement [message #245437] Sun, 17 June 2007 03:58 Go to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
We have 2 tables. ILX_FCT_AR and ILX_FCT_AR_LN. Both have a column called AGING_BUCKET_KEY that shows the no of days since someone has not paid for a product.

The primary key set of AR table is INV_ID, RECORD_SOURCE_SYSTEM_NAME, SOURCE_RECORD_TYPE_NAME and has a unique index created on these columns.

The primary key set of AR_LN is different. But it has a foreign key constraint (INV_ID, RECORD_SOURCE_SYSTEM_NAME, SOURCE_RECORD_TYPE_NAME) referencing those from ILX_FCT_AR table.

AGING_BUCKET_KEY for both the tables is indexed (normal type). This column has low cardinality with only 15 possible distinct values (A to N for open buckets, Z for closed. Closed means the payment is received)

ILX_FCT_AR has 1.3 million rows and ILX_FCT_AR_LN has 96 million rows. A header in AR can have several line records in LINE table.

This AGING KEY is updated daily based on no of days passed. A is the lowest day count bucket and N is the highest.

I need to get all the records from ILX_FCT_AR that are still open (!='Z') and update its status everyday. So I use this query

UPDATE
ILX_FCT_AR A
SET
AGING_BUCKET_KEY = (SELECT
ILX_DIM_AR_AGING_BUCKET.AGING_BUCKET_KEY
FROM
ILX_DIM_AR_AGING_BUCKET
WHERE
ROUND(SYSDATE - A.INV_DUE_DATE) >= ILX_DIM_AR_AGING_BUCKET.FROM_PAST_DUE_DAYS AND
ROUND(SYSDATE - A.INV_DUE_DATE) <= ILX_DIM_AR_AGING_BUCKET.TO_PAST_DUE_DAYS
AND ILX_DIM_AR_AGING_BUCKET.AGING_BUCKET_KEY <> 'Z'
AND ILX_DIM_AR_AGING_BUCKET. BUCKET_TYPE = 'AR')
WHERE A.aging_bucket_key != 'Z'

This is somewhat fine.

The issue is AR_LN table doesnt have due date column and hence can be updated only from AR table. So I use this query.

UPDATE ILX_FCT_AR_LN T
SET AGING_BUCKET_KEY = (SELECT AGING_BUCKET_KEY
FROM ILX_FCT_AR
WHERE INV_ID = T.INV_ID
AND RECORD_SOURCE_SYSTEM_NAME = T.RECORD_SOURCE_SYSTEM_NAME
AND SOURCE_RECORD_TYPE_NAME = T.SOURCE_RECORD_TYPE_NAME)
WHERE aging_bucket_key != 'Z'

This query will pull around 3-4% records from ILX_FCT_AR_LN table everytime. So around 3-4 million rows will be updated everyday.

But it is taking too much time. It takes 2 hours sometimes. I checked the explain plan for the query. It does a full table scan on the ILX_FCT_AR_LN table, because we have the !='Z' condition. The CPU cost says 8726974892, IO COST says 55947, cardinality says 3322497 and the cost says 57008.

An additional piece of info. Since there is a foreign key constraint on the AR_LN table, and since AR_LN gets updated after AR table, every closed bucket value in AR will always be closed in AR_LN too.

These tables are updated daily. Since the cardinality is low on AGING_KEY and has only 15 distinct values, I have some questions
1. Will a bitmap index work?
2. Or will dropping and recreating indexes before and after update work better?
3. What is the implication of bitmap index in this case as its updated everyday?
4. Is there any other easier/faster way of achieving this?

5. Is this information enough for all the gurus out there? If you more information, please do let me know. Pls let me know your suggestion.
Re: Performance issue with an update statement [message #245438 is a reply to message #245437] Sun, 17 June 2007 04:00 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Missed one bit of info. The bitmap index I proposed is for AGING_BUCKET_KEY.
Re: Performance issue with an update statement [message #245566 is a reply to message #245438] Mon, 18 June 2007 02:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Two things you can do:
  • You are performing nested updates ie. SET col = (subquery). These are really inefficient for high volumes because they perform the subquery once for each row returned by the UPDATE WHERE clause. You would be better off using an Updateable Join View
  • You are probably updating rows that do not need to be udpated. eg. A row is currently 'C' and you update it to 'C'. There are two elements to the cost of this operation: identifying the rows to update, and then updating them. You will probably find that identifying the rows is not the problem. If you can cut down the number of rows updated then you will get a big improvement. After you put in the Updateable Join View, add another predicate to prevent needless updates.


Ross Leishman
Re: Performance issue with an update statement [message #247846 is a reply to message #245437] Wed, 27 June 2007 06:35 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
There is one more thing you could do.

list partition both tables. Place ato n in one partition and Z in other partition._LN

This will make partition1 will have only 3 to 4 million rows and then update it depending on other tables (AR)partition1 AR
Re: Performance issue with an update statement [message #248105 is a reply to message #245438] Thu, 28 June 2007 01:41 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
U have index on aging_bucket_key column
and u use aging_bucket_key != 'Z' . When u use != then the index is not used. So use aging_bucket_key between 'A' and 'N' instead of !='Z'
Re: Performance issue with an update statement [message #248746 is a reply to message #248105] Sun, 01 July 2007 09:53 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
HI,
We had tried A to N previously. Didnt help. We are trying to partition the table and test it. Will let you know the results.
Thanks
Prem
Re: Performance issue with an update statement [message #248748 is a reply to message #245437] Sun, 01 July 2007 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
I'm not sure the following would help
WHERE A.aging_bucket_key < 'O'
Re: Performance issue with an update statement [message #249030 is a reply to message #248746] Tue, 03 July 2007 02:08 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi

Partitioning is the best and i would suggest one more thing depending on the assumption i made.

I think you have values only A-N and Z

so rather than using !=z or in between A and N use <'z'

It would go for Index Scan and will make the performance faster.

This would be helpful even if u use partiotion.This statement will remove access of partition which have value Z and thus u will have 3 4 million records to process.

Re: Performance issue with an update statement [message #249752 is a reply to message #249030] Thu, 05 July 2007 14:57 Go to previous message
concorde800
Messages: 52
Registered: May 2007
Member
You can set Parallelism on DML statements at a session level

ALTER SESSION FORCE PARALLEL DML


Alternately, use parallel hints on the Update Statement to speed up the data retrieval. Parallel hints work best with large tables with millions of rows.
Previous Topic: How To Log a PL/SQL Transaction...
Next Topic: Approach to Purge Sensitive data periodically.
Goto Forum:
  


Current Time: Tue Dec 06 08:41:00 CST 2016

Total time taken to generate the page: 0.16431 seconds