| Toad's Analyze Table Tool [message #6774] |
Tue, 06 May 2003 00:25  |
Marjorin Mendoza
Messages: 15
Registered: September 2002
|
Junior Member |
|
|
I have an update statement that goes ...
UPDATE T_SUMTRANS HTD
SET (HTD.AMOUNT1, HTD.AMOUNT2) =
(SELECT HTD.AMOUNT1 + SUM(HTW.AMOUNT1), HTD.AMOUNT2 + SUM(HTW.AMOUNT2)
FROM T_DAILYTRANS HTW
WHERE HTD.CAMPAIGN = HTW.CAMPAIGN AND HTD.ACCOUNTNO = HTW.ACCOUNTNO
GROUP BY HTD.CAMPAIGN, HTD.ACCOUNTNO)
WHERE EXISTS (
SELECT * FROM T_DAILYTRANS HTW
WHERE HTD.CAMPAIGN = HTW.CAMPAIGN AND HTD.ACCOUNTNO = HTW.ACCOUNTNO
GROUP BY HTD.CAMPAIGN, HTD.ACCOUNTNO)
Since the table has close to a million records, it takes 4-6 hours to process this update statement but if I analyze the table first, it will just take 1 minute of processing. Oracle configuration (e.g. table, tablespace and indices) might be the problem but how will I know if what is alloted for that table is enough? Why when I analyze the table first, processing time is 1 minute? Can I call analyzed table in a procedure?
|
|
|
|
| Re: Toad's Analyze Table Tool [message #6776 is a reply to message #6774] |
Tue, 06 May 2003 02:29  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The purpose of analyzing the table is to gather statistics for the optimizer to use in choosing the best execution plan. Oracle's analyze table does this by populating certain columns in the data dictionary tables, like the num_rows column of the all_tables dictionary view, that tells it how many rows are in that table. That is why the execution is faster after you analyze the table.
Yes, you can call analyze table (Oracle's version, not Toad's) in a procedure, but only if you use dynamic sql, like:
EXECUTE IMMEDIATE 'ANALYZE TABLE table_name';
I don't know if you cut and pasted your query or just mistyped it, but it looks like each of your from clauses is missing one of the tables.
Just looking at your query, it looks like a composite index on (campaign, accountno) for each table would help, if you don't already have one.
You can use autotrace to see what plan the optimizer is choosing and how it is processing it. Please click on the link below for instructions on installation and usage:
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
