Home » SQL & PL/SQL » SQL & PL/SQL » 2 FULL Table Access for the update statement
 2 FULL Table Access for the update statement [message #415033] |
Fri, 24 July 2009 07:57  |
rtnataraj
Messages: 102
Registered: December 2008
|
Senior Member |
|
|
Hi All,
CREATE TABLE HISTORY
(
KEY NUMBER,
MONTH NUMBER,
AMT NUMBER
);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(1, 1, 5);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(1, 2, 0);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(1, 3, 4);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(2, 2, 4);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(2, 5, 6);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(3, 4, 5);
Insert into HISTORY
(KEY, MONTH, AMT)
Values
(2, 3, 5);
COMMIT;
CREATE TABLE OFCD
(
KEY NUMBER
);
Insert into OFCD
(KEY)
Values
(1);
Insert into OFCD
(KEY)
Values
(2);
Insert into OFCD
(KEY)
Values
(3);
Insert into OFCD
(KEY)
Values
(4);
COMMIT;
UPDATE history h1
SET amt =
(SELECT amt
FROM (SELECT h2.KEY, h2.MONTH, h2.amt,
ROW_NUMBER () OVER (PARTITION BY KEY ORDER BY MONTH DESC)
fc
FROM history h2
WHERE h2.MONTH < 3) h3
WHERE h3.fc = 1 AND h3.KEY = h1.KEY)
WHERE MONTH = 3;
this update causes 2 FULL Table Access.
pls give me a better solution.
regards,
Nataraj.
|
|
|
|
|
|
| Re: 2 FULL Table Access for the update statement [message #415040 is a reply to message #415033] |
Fri, 24 July 2009 08:07  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Please use code tags, you should know how by now
2) What indexes? there aren't any in your example.
3) If your tables only contain a tiny amount of data, which is true in your example, then oracle will allways full table scan as it's quicker than using an index.
|
|
|
|
| Re: 2 FULL Table Access for the update statement [message #415044 is a reply to message #415033] |
Fri, 24 July 2009 08:33 |
rtnataraj
Messages: 102
Registered: December 2008
|
Senior Member |
|
|
Please find the explain plan.
this is just an example, i need to implement this in oracle datawarehouse DB, so we cant go for indexes.
UPDATE history h1
SET amt = (SELECT amt
FROM (SELECT h2.KEY,
h2.MONTH,
h2.amt,
ROW_NUMBER() OVER (PARTITION BY KEY
ORDER BY MONTH DESC) fc
FROM history h2
WHERE h2.MONTH < :param) h3
WHERE h3.fc = 1
AND h3.KEY = h1.KEY)
WHERE MONTH = :param
the logic is when ever we give a value parameter (:param),
eg. if u give 3 as param,
then i need to update the amt column (with the amt of the previous month's record) for all the records in the history table that has month = 3
regards,
Nataraj.
-
Attachment: untitled.JPG
(Size: 30.91KB, Downloaded 467 times)
[Updated on: Fri, 24 July 2009 08:41] Report message to a moderator |
|
|
|
| Re: 2 FULL Table Access for the update statement [message #415046 is a reply to message #415044] |
Fri, 24 July 2009 08:35 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
| rtnataraj wrote on Fri, 24 July 2009 14:33 | Please find the explain plan.
this is just an example, i need to implement this in oracle datawarehouse DB, so we cant go for indexes.
regards,
Nataraj.
|
1) Why not?
2) What else do you expect oracle do to?
|
|
|
|
| Re: 2 FULL Table Access for the update statement [message #415048 is a reply to message #415033] |
Fri, 24 July 2009 08:48 |
rtnataraj
Messages: 102
Registered: December 2008
|
Senior Member |
|
|
UPDATE history h1
SET amt = (SELECT amt
FROM (SELECT h2.KEY,
h2.MONTH,
h2.amt,
ROW_NUMBER() OVER (PARTITION BY KEY
ORDER BY MONTH DESC) fc
FROM history h2
WHERE h2.MONTH < :param) h3
WHERE h3.fc = 1
AND h3.KEY = h1.KEY)
WHERE MONTH = :param
the logic is when ever we give a value parameter (:param),
eg. if u give 3 as param,
then i need to update the amt column (with the amt of the previous month's record) for all the records in the history table that has month = 3
regards,
Nataraj.
[Updated on: Fri, 24 July 2009 08:50] Report message to a moderator |
|
|
|
| Re: 2 FULL Table Access for the update statement [message #415052 is a reply to message #415044] |
Fri, 24 July 2009 09:01 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you have no indexes on your tables, then all of your table access will be via full table scans.
It's that simple - no index => full table scan
Oracle has no other way to get at the data. If there isn't an index to say where the row(s) matching a specific criteria are, then it just has to read the whole table, from start to finish.
NOw, if you are access a significant percentage of the table, then it may well make sense to access via FTS rather than by an index, but the %age of the table that is accessed, and the distribution of your data are just some of the many, many things you haven't told us about your problem.
So, are you ready to consider indexes yet?
|
|
|
|
| Re: 2 FULL Table Access for the update statement [message #415053 is a reply to message #415048] |
Fri, 24 July 2009 09:03 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Actually - I have missed out one important feature - materialized views. They give you the ability to pre-generate sets of data for queries, and to optionally use those result sets automatically in queries.
I'm not convinced that they'd be a lot of use here though, as you'd end up replacing the wait for the query to run with a wait for the Mview to build.
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 05:04:08 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
