Best approach to massive data shift

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Best approach to massive data shift

Postby mode09 » Tue May 28, 2013 6:06 pm

I have 5 weeks of data in a table. Weekly the table must roll, so week 4 ago = week 3 ago, etc. Is this the best approach in a table with millions of records? Is there a better approach?

UPDATE CLEARANCE_STG
SET clearance_04ago = clearance_03ago,
clearance_03ago = clearance_02ago,
clearance_02ago = clearance_01ago,
clearance_01ago = clearance;

COMMIT;
mode09
Member
 
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

Re: Best approach to massive data shift

Postby Tim... » Tue May 28, 2013 6:45 pm

Hi.

It is rare that I would recommend large-scale updates of data. This is because updates are usually a very expensive operation from a resource perspective. Often is is more efficient to create a new version of the table and rename it back to the original. For example,

Code: Select all
create clearance_stg_temp as
select clearance_03ago AS clearance_04ago,
          clearance_02ago AS clearance_03ago,
          clearance_01ago AS clearance_02ago,
          clearance AS clearance_01ago
from   clearance_stg;
commit;

drop table clearance_stg;
rename clearance_stg_temp to clearance_stg;


I'm sure you have other columns to consider, not sent in the example, but you get the idea.

The CTAS method is often the best solution to any update or delete on a large table.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Best approach to massive data shift

Postby mode09 » Wed May 29, 2013 12:05 pm

Thanks, this definitely seems the way to go for the large data volume!
mode09
Member
 
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

Re: Best approach to massive data shift

Postby Tim... » Wed May 29, 2013 12:37 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests