Home » SQL & PL/SQL » SQL & PL/SQL » 2 FULL Table Access for the update statement
icon4.gif  2 FULL Table Access for the update statement [message #415033] Fri, 24 July 2009 07:57 Go to next message
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 #415039 is a reply to message #415033] Fri, 24 July 2009 08:05 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have not pasted the explain plan.
Not formated the queries.

Anyway, you don't have any indexes.
What do you want Oracle to do?

By
Vamsi
Re: 2 FULL Table Access for the update statement [message #415040 is a reply to message #415033] Fri, 24 July 2009 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 111 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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Function-base index's problem
Next Topic: Record insertion time
Goto Forum:
  


Current Time: Fri Dec 09 12:00:50 CST 2016

Total time taken to generate the page: 0.13868 seconds