Home » SQL & PL/SQL » SQL & PL/SQL » sql for incremental load (10g)
sql for incremental load [message #407106] Mon, 08 June 2009 08:28 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Is there any way i could to an incremental load with following data. I have a few source tables, which in return i want to populate data to base table, on incremental basis, but each table i have last_update_date. I am not sure how to proceed. I have identified the sql, but is there any way i could capture data incrementally?
create table student (
  student_id number,
  first_name varchar2(10),
  last_name varchar2(10),
  flag char(1),
  last_update_date date);
  
create table guardian (
  student_id number,
  first_name varchar2(10),
  last_name varchar2(10),
  relationship varchar2(10),
  primary char(1),
  last_update_date date);
  
create table address (
  student_id number,
  street varchar2(10),
  state varchar2(10),
  city varchar2(10),
  country varchar2(10),
  primary char(1),
  last_update_date date);
  




Sql to populate the base table

select a.student_id, a.first_name || a.last_name as full_name,
b.first_name || b.last_name as primary_guardian,
c.street || c.state || c.city || c.country as address
from student a, guardian b, address c
where a.student_id = b.student_id
and a.student_id = c.student_id
and b.primary = 'Y'
and c.primary = 'Y'




create table master_student (
  student_id number,
  full_name varchar2(10),
  primary_guardian  varchar2(10),
  primary_address  varchar2(10));


Re: sql for incremental load [message #407143 is a reply to message #407106] Mon, 08 June 2009 12:27 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
do you mean load or update?

One way would be to have last_updated_date in the master table too, then

where a.last_update_date >= {some date}
   or b.last_update_date >= {some date}
   or c.last_update_date >= {some date}


Sorry to say that if you do not have a date in the master table, how would you know when it was loaded?

[Updated on: Mon, 08 June 2009 12:30]

Report message to a moderator

Re: sql for incremental load [message #407144 is a reply to message #407106] Mon, 08 June 2009 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
does last_update_date include INSERT & DELETE operations?

Why violate Third Normal Form by having MASTER_STUDENT table?

Why not

CREATE VIEW MASTER_STUDENT against 3 existing tables?
Re: sql for incremental load [message #407168 is a reply to message #407144] Mon, 08 June 2009 22:27 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Blackswan and joy_division,

Previously we had a sql created on this 3 tables, and due to the size of this table, it takes some time to return data, and then we plan to create a master student table, and which suits our needs.

Yes, this new table will be populated based on update and insert.

where a.last_update_date >= {some date}
   or b.last_update_date >= {some date}
   or c.last_update_date >= {some date}



Thats good to add last_update_date in master_student.

The only problem i see with this way, if the guardian name for an existing student has changes, then the last_update_date column for this student in guardian table will change, but since there was no change in the student table, the last_update_date in student table wont change, and i wont be able to pick up this change
Re: sql for incremental load [message #407245 is a reply to message #407168] Tue, 09 June 2009 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll have to either:
1) Check the last update dates on all 3 tables
or
2) Have triggers on Address and Guardian to set the date on student if they are updated.
Re: sql for incremental load [message #407273 is a reply to message #407245] Tue, 09 June 2009 07:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Have you considered a fast-refresh materialized view.

Ross Leishman
Re: sql for incremental load [message #407299 is a reply to message #407273] Tue, 09 June 2009 08:47 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you guys for the suggestions, i will check the options recommended..
Previous Topic: Select for update where current of problem
Next Topic: Reading a CLOB object
Goto Forum:
  


Current Time: Thu Dec 08 04:11:08 CST 2016

Total time taken to generate the page: 0.09118 seconds