Home » SQL & PL/SQL » SQL & PL/SQL » Update about 7500 row running long
Update about 7500 row running long [message #198835] Wed, 18 October 2006 15:09 Go to next message
JoeRoe
Messages: 3
Registered: October 2006
Junior Member
Hi, I'm trying to do an update on this table: hz_cust_accounts_all
There is about 150000 rows, and I want to update about 7500 rows.

Here is my query:

update apps.hz_cust_accounts_all a
set a.attribute13 = 'N'
where a.attribute13 = 'Y'

It's taking about 8:00 to complete, it's really too long. If I execute this query:

select * from apps.hz_cust_accounts_all where attribute13 = 'Y'

it takes a few seconds.

I did trace the query, and I notice it doing this query:

SELECT CODE_COMBINATION_ID
FROM
APPS.GL_CODE_COMBINATIONS WHERE
SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||
SEGMENT6||'.'||SEGMENT7||'.'||SEGMENT8 = :B1

Most of the time (about 99%) it's doing this query, and I don't know why, and I don't know how to solve the problem. I tried to create a concatenated index on GL_CODE_COMBINATIONS, but it did'nt solve the problem. Do you have an idea?

Thanks
Re: Update about 7500 row running long [message #198836 is a reply to message #198835] Wed, 18 October 2006 15:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Is there an update trigger(s) on this table?

Even a full table scan on a 150K row table should only take a few seconds, so something else is going on here.
Re: Update about 7500 row running long [message #198972 is a reply to message #198835] Thu, 19 October 2006 08:10 Go to previous messageGo to next message
JoeRoe
Messages: 3
Registered: October 2006
Junior Member
No, there is no trigger.

I don't no either what is going on, so It's why I'm asking the question, it's really strange, and i'm not familiar enough with Oracle to find what's the problem...

Thank you to help me!

Re: Update about 7500 row running long [message #198981 is a reply to message #198835] Thu, 19 October 2006 08:49 Go to previous messageGo to next message
JoeRoe
Messages: 3
Registered: October 2006
Junior Member
I tried something: I ran the same query in Toad (usually I ran my query in SQLPLUS) and it took less than 1 minutes in Toad and it took about 8 hours in SQLPLUS???

Where is the bug?
Re: Update about 7500 row running long [message #198996 is a reply to message #198981] Thu, 19 October 2006 09:51 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Joeroe,

I have very litte experience in the AR part of Apps (that's where this table resides), but some thoughts:
- maybe the database somehow "caught" some apps context, otherwise it's complete nonsens to have a query on gl_code_combinations whereas you're trying to update customers right? so check if you are sure you are connecting "sec", with no responsibilities, context, etc.
- the definition of the attributes (so, what does attribute13 in the customers table mean?) is stored in applsys.fnd_descr_flex_column_usages and applsys.fnd_descriptive_flexs. I have no clue how in sql*plus the definition of the flexfield could interfere with an update statement (and even more odd: how can toad surpass that??), but maybe you can find something in that definition?

One last remark: it's strongly recommended by Oracle not to update any contents of Apps other than through the forms or api-procedures... But that's up to you to decide...

Regards,
Sabine

Re: Update about 7500 row running long [message #199060 is a reply to message #198981] Thu, 19 October 2006 23:21 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Try this
SELECT *
FROM all_dependencies
WHERE referenced_name = 'HZ_CUST_ACCOUNTS_ALL'

The simple fact is that an update to one table cannot cause a query to another unless the column being updated is a foreign reference to another table, there is a trigger on the table, or it is part of a materialized view. I am thinking that it is the later.
Previous Topic: insert into .... select * from.....exception errors
Next Topic: MERGE with variables instead of table dat
Goto Forum:
  


Current Time: Thu Dec 08 12:38:44 CST 2016

Total time taken to generate the page: 0.07764 seconds