| query doing full table scan [message #163468] |
Thu, 16 March 2006 17:09  |
kapilcool
Messages: 2
Registered: March 2006
Location: London
|
Junior Member |
|
|
Hi,
I am having some issues with one query, which has been running for 2 days now. It is doing continuous full table scan.
I am using Oracle 9i Linux system. I have collected the statistics as well using DBMS_stats, but it did not help.
The following is the query:
"UPDATE table1
SET (total_cost, total_duration, total_calls) =
(SELECT NVL
(SUM (table2.costofcall),
0
) AS total_cost,
NVL
(SUM (table2.chargetime),
0
) AS total_duration,
COUNT (table2.ID) AS total_calls
FROM table2
WHERE table2_no(+) =
table1_no
AND table2.cli(+) = table1.cli)
The explain plan gives the following information:
OPERATION OPTIONS OBJECT_NAME POSITION CARDINALITY
----------------------------------- -------------------- ------------------------------ ---------- -----------
UPDATE STATEMENT 58 41130
UPDATE TABLE2 1
TABLE ACCESS FULL TABLE2 1 41130
SORT AGGREGATE 2 1
TABLE ACCESS FULL TABLE1 1 694
5 rows selected.
The same query was fine last week, but has started full table scan suddenly!!
Can you please give me some suggestions? Can I force the CBO to use indexes instead of doing full table scan?
Thanks,
Kapil
|
|
|
|
| Re: query doing full table scan [message #163512 is a reply to message #163468] |
Fri, 17 March 2006 00:46  |
 |
Alien
Messages: 292
Registered: June 1999
|
Senior Member |
|
|
Hi,
Your explain plan doesn't look right. It shows an update on table2, instead of table1.
imho, it would only make sense for Oracle to use a full table-scan. You update every single row in table1. So a fts is needed on table1 anyway.
For every row in table1, you collect data from table2, with an outer join. So it's quite likely to do a fts there too. The use of indexes would be very dependent on the actual stats and size of the tables. My guess is that your tables grew a little bit. and Oracle considers a fts cheaper now.
I think the best option would be to rewrite the query. Do you need to update every row in table1, and do you need the outer join?
Otherwise, you might want to try using an index hint.
Regards,
Arian
p.s. what does your data look like? Null-values / skewed data?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
