This forum is currently locked. You can't register or post questions at this time. (read more)

Best approach to massive data shift

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

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;

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

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

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!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 7 guests

cron