Home » SQL & PL/SQL » SQL & PL/SQL » Design Question (12c )
Design Question [message #675863] Fri, 26 April 2019 10:25 Go to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Hi Guys,

I have a requirement to send an extract of data (in xml format) to a 3rd party every day. After the initial extract with all data, subsequent extracts should only be for any data that has changed (from yesterday's extract).

I am trying to decide what is the best way to get only changed data. I will be using a pretty simple query that uses 3 tables for the data.

e.g


SELECT forename, surname, address, course_name
  FROM student s, address a, courses c
 WHERE s.id = a.id
   AND s.id = c.id


So I only want it to return records where forename, surname, address or course_name may have changed from the previous day.

Does anyone have an efficient way of doing this? I was thinking of doing something like>

Above query populates a table each day, then the next day's query compares against the table for any changed data.

Thanks
Re: Design Question [message #675864 is a reply to message #675863] Fri, 26 April 2019 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ How may rows (total and changing each day)?
2/ First thought: MINUS
3/ Second thought MVIEW LOG
4/ Third thought trigger filling the today table

Re: Design Question [message #675865 is a reply to message #675864] Fri, 26 April 2019 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

FWIW - 12c is a marketing alias & NOT acceptable. Always post Oracle version to 4 decimal places.

>So I only want it to return records where forename, surname, address or course_name may have changed from the previous day.
What should be done for new rows (INSERT) being added?
What should be done when old rows are removed (DELETE)?


post SQL & results that show only changed data.


>So I only want it to return records where forename, surname, address or course_name may have changed from the previous day.
Re: Design Question [message #675866 is a reply to message #675864] Fri, 26 April 2019 10:52 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Initial will be about 20,000 records. After that daily changes will be few hundred records. But at certain times in year when students enrol they will all need to be sent in the extract, which may be about 10,000

Minus - Do you mean using my approach of creating a table and then querying that using Minus?

Thanks

Re: Design Question [message #675867 is a reply to message #675865] Fri, 26 April 2019 10:59 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
New Rows also need to be included in the extract. There will be no deletes.
Re: Design Question [message #675868 is a reply to message #675867] Fri, 26 April 2019 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
> There will be no deletes.
You are a naive newbie.
Do you believe that the initial data entry will always be 100% perfect?

When table alias exist in FROM clause, then every column in SELECT clause should be qualified by table alias.

You need to post Test Case if you desire actual SQL solution.
Re: Design Question [message #675869 is a reply to message #675868] Fri, 26 April 2019 11:17 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
> There will be no deletes

Sorry I meant from the student, address, course tables. These student records are never deleted from the db.

I will create some test data.

Thanks
Re: Design Question [message #675870 is a reply to message #675869] Fri, 26 April 2019 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
BTW, I find single character table alias to be an anathema, because searching for them in long procedure can result in many false positives.
SSS, AAA, or CCC almost NEVER result in false positive.

Consider that if on day 1234 after initial data dump & asked to produce current table state, you have to apply only 1233 incremental restores.

I suggest that you should consider to additionally do periodic (weekly or monthly) full table exports using expdp.

>These student records are never deleted from the db.
Except data entry error occurs or the "rules" change to save disk space.
Re: Design Question [message #675871 is a reply to message #675866] Fri, 26 April 2019 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora9a wrote on Fri, 26 April 2019 17:52
...
Minus - Do you mean using my approach of creating a table and then querying that using Minus?
...
Yes.
Given the tiny number of rows you have this seems to be the best way.

Re: Design Question [message #675872 is a reply to message #675870] Fri, 26 April 2019 12:10 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
CREATE TABLE student (ID NUMBER, FORENAME VARCHAR2(50), SURNAME VARCHAR2(50));

CREATE TABLE address (ID NUMBER, LINE1 VARCHAR2(50), CITY VARCHAR2(50), ZIPCODE VARCHAR2(20), STU_ID NUMBER);

INSERT INTO student VALUES (1, 'Joe','Bloggs');
INSERT INTO student VALUES (2, 'Adam','Smith');

INSERT INTO address VALUES (1,'15 New Avenue','London','SE1XAD',1);
INSERT INTO address VALUES (2,'25 Bond Street','London','SWBAJ',2);

SELECT stu.id AS Student_id, stu.forename, stu.surname, adr.line1,adr.city
  FROM student stu, address adr
 WHERE stu.id = adr.stu_id; 

So on Day 1 this would return 2 rows.
On Day 2, a student's address is updated:

UPDATE address SET LINE1 = '80 Harvey Drive', CITY = 'Leeds', ZIPCODE = 'L1BXU'
 WHERE stu_id = 1;

When the query is executed on day 2, it should only return this student that has been updated, and not both records as in the first day.

Hope that makes sense!

Thanks.

(And the aliases etc. are only for brevity and not according to standards).





Re: Design Question [message #675873 is a reply to message #675872] Fri, 26 April 2019 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

On day one you fill the stage table with the table, on day 2 you minus the current table with the stage one.

Re: Design Question [message #675915 is a reply to message #675873] Mon, 29 April 2019 05:37 Go to previous messageGo to next message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Michel,

I am assuming I will have to keep updating the staging table everyday. e.g.

CREATE TABLE Staging AS (
SELECT stu.id AS Student_id, stu.forename, stu.surname, adr.line1,adr.city
  FROM student stu, address adr
 WHERE stu.id = adr.stu_id);

DECLARE

CURSOR Cur1 IS 
SELECT stu.id AS Student_id, stu.forename as forename, stu.surname as surname, adr.line1 as line1,adr.city as city
  FROM student stu, address adr
 WHERE stu.id = adr.stu_id
MINUS 
SELECT Student_id,forename,surname,line1,city
  FROM Staging;

BEGIN
  
 FOR idx IN cur1 LOOP

 UPDATE Staging 
   SET forename = idx.forename,
       surname = idx.surname,
       line1 = idx.line1,
       city = idx.city
  WHERE student_id = idx.student_id;
 END LOOP;
END;
Re: Design Question [message #675917 is a reply to message #675915] Mon, 29 April 2019 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13626
Registered: September 2008
Location: Rainy Manchester
Senior Member
2 problems:
1) you aren't accounting for new rows - you'll need to do an insert as well. You could replace both update and insert with a single merge.
2) staging will include rows that haven't changed. You could get round this by adding a date column and setting it to trunc(sysdate) whenever a row is inserted or updated.
Then the process that actually creates the xml can just look at rows where date column = trunc(sysdate).
Re: Design Question [message #675920 is a reply to message #675915] Mon, 29 April 2019 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You do NOT update staging, after generating your report, the only things you do on it are:
TRUNCATE
INSERT SELECT

Re: Design Question [message #675922 is a reply to message #675920] Mon, 29 April 2019 11:40 Go to previous messageGo to next message
Bill B
Messages: 1868
Registered: December 2004
Senior Member
Include the student ID and a last_modification date in the staging table and then use a merge statement ones that change get updated with the modification_date being set to sysdate (or timestamp. New rows get inserted. You then simply query the staging table where the modification_date is the current day. Push all those through the XML file
Re: Design Question [message #675931 is a reply to message #675920] Tue, 30 April 2019 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13626
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Mon, 29 April 2019 17:23

You do NOT update staging, after generating your report, the only things you do on it are:
TRUNCATE
INSERT SELECT

If you truncate it what are you doing a minus against?
Re: Design Question [message #675932 is a reply to message #675931] Tue, 30 April 2019 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The steps are:
Generate report using minus
TRUNCATE staging table
INSERT staging table SELECT FROM current tables, then the staging table is ready for the next day.

Re: Design Question [message #675941 is a reply to message #675932] Wed, 01 May 2019 05:47 Go to previous message
ora9a
Messages: 26
Registered: June 2010
Junior Member
Thanks Michel.
Previous Topic: Converting returned rows into columns
Next Topic: Runtime data stored in tables
Goto Forum:
  


Current Time: Sat Jun 15 19:56:04 CDT 2019