Home » SQL & PL/SQL » SQL & PL/SQL » Huge Data extract-need suggestions
Huge Data extract-need suggestions [message #304614] Wed, 05 March 2008 17:29 Go to next message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
All,
I have a requirement like this
1. I will have upto 200 Ids[unique] in Table A, the extract should read the ids from Table A and then extract data from 5 other tables where id=ids from table A. I was thinking about creating a cursor and then iterate through the cursor and then extract data from other tables.
Curosr- select id from Table A
iterate thru the cursor and then
select * from table b where id=..
select * from table c where id =
select * from table d where id=
select * from table e where id=

2. Question is Is it a good appraoch ? For each id there can be tons of records in other tables
Example: Table b can have 30000 records for id1
Table C can have 35000 recods for id1
Table D can have 20000 records for id1
Table E can have 30000 records for id1

Similiarly there can be upto 200 Ids and then huge extracts for all the ids from different tables, This will be a batch extract and will be done during off hours.
How can this be handled efficiently ?

Can a cursor handle it ?
Can anyone suggest me some efficient ways ?

Thanks
Re: Huge Data extract-need suggestions [message #304623 is a reply to message #304614] Wed, 05 March 2008 21:36 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
and will be done during off hours.



have you done it?
I don't think so.


regards,
Re: Huge Data extract-need suggestions [message #304644 is a reply to message #304623] Thu, 06 March 2008 00:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No. If you are extracting large volumes of data, you want to do it in as FEW sqls as possible.

Use an IN sub-query and extract all 200 IDs in one SQL.

Ross Leishman
Re: Huge Data extract-need suggestions [message #304722 is a reply to message #304644] Thu, 06 March 2008 06:51 Go to previous messageGo to next message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
Ross,

Thanks for your reply, So you are saying for huge extracts using cursors is not a good appraoch and try to minimize SQLS as possible.
Ok, So I need to extract from 4 different tables, Can I use this ?

Select * from Table A where ID IN (select id from table A);
Select * from Table B where ID In (select id from table A); etc

Now with this approach, Can we insert these into remote tables after doing the selection ? after selection, the rows in these tables need to be inserted to same tables in a different server[a dblink has been established].

Thanks,
Maya
Re: Huge Data extract-need suggestions [message #304734 is a reply to message #304722] Thu, 06 March 2008 07:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You can do it but I will suggest is try to pull data rather than pushing data. We had more or less a similar kind of issue and we found that we cannot do direct path insert over dblink. So we did the other way around (i.e) as I mentioned earlier we decided to pull the data rather than pushing it.

Just a thought.

So in brief terms you can use your select statement to insert into a table via dblink but you cannot issue a direct path insert.

Hope that helps

Regards

Raj
Re: Huge Data extract-need suggestions [message #304736 is a reply to message #304614] Thu, 06 March 2008 07:41 Go to previous messageGo to next message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
Raj,

Can you give me a suggestion ?
This is what I tried for one table.

INSERT INTO
test_x(x1,x2,x3,x4,x5,x6,x7,x8)
SELECT
x1,x2,x3,x4,x5,x6,x7,x8 from x
where id in (select id from log_x);

Are you suggesting something like this ? If not, can you please make a modification for this ?

Thanks
Re: Huge Data extract-need suggestions [message #304738 is a reply to message #304736] Thu, 06 March 2008 07:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
this is code to push the data over dblink
insert /*+ append */ into target_table@target_db_link
select * from source_table where column in (select query)

In the above example the hint i have given will not work because direct path insert is not possible becuase as oracle documentation says :
Quote:
A transaction containing a direct-path INSERT statement cannot be or become distributed.

This is the code to pull the data
So what i am suggesting is
insert /*+ append */ into target_table
select * from source_table@source_db_link where column in (select query@source_db_link)

If it is going to be few thousand I won't be worried about pushing or pulling data. But if it is in millions then I would strongly advise to use the append hint. For more information read the oracle manual.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2191836
Regards

Raj

P.S : From next time could you please format the code like what I did. Check the forums guidelines how to format your post.
Re: Huge Data extract-need suggestions [message #304741 is a reply to message #304614] Thu, 06 March 2008 08:04 Go to previous messageGo to next message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
Raj, Thanks for the suggestion. However to pull the data based on your sample code, it needs to be done in the other application side right ?
The requirement is to run the batch on our end. So In our case Pushing is the only option.

And this is the requirement, I have a requirement to do a bulk extract,nightly extract from 5 of our tables and insert the data using a dblink to a remote database.

1. I will have upto 200 Ids[unique] in Table A, the extract should read the ids from Table A and then extract data from 5 other tables where id=ids from table A. After extracting the data needs to be loaded in remote tables[same tables as of table b,c,d and e] using a dblink and those ids which are succesfully extracted should be deleted from Table A.

Any sugegstions ? Ross mentioned above that a cursor wont be a good idea. But how will I keep track of the ids if i dont use a cursor ?

PS: Thanks for the comment regarding code snippets, I will read the guideline, didnt know.

Please give me any code samples for this
Thanks
Re: Huge Data extract-need suggestions [message #304747 is a reply to message #304741] Thu, 06 March 2008 08:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
insert into target_table_b@target_db_link
select * from table_b b where b.id in (select a.id from table_a a);
...
insert into targettable_e@target_db_link
select * from table_e e where e.id in (select a.id from table_a a);

Quote:
But how will I keep track of the ids if i dont use a cursor ?

Can you quantify more on the above statement.

Regards

Raj

P.S : Added some clarity to the code to avoid ambiguity

[Updated on: Thu, 06 March 2008 08:23]

Report message to a moderator

Re: Huge Data extract-need suggestions [message #304750 is a reply to message #304614] Thu, 06 March 2008 08:36 Go to previous messageGo to next message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
insert into target_table_b@target_db_link
select * from table_b b where b.id in (select a.id from table_a a);
...
insert into targettable_e@target_db_link
select * from table_e e where e.id in (select a.id from table_a a);


Thanks for the above code snippet,

Now for my comment regarding How to keep track of id,

Id is in Table A, After the rows are inserted into the remote tables, the Ids need to be deleted frm Table A. ie, if contents Tableb, c, d and e are inserted succesfully to target a, target b, target c and target d then a.id should be deleted from Table A. So for any reason an insert fails for an id, then that exception needs to be caught and the ID should NOT be deleted from Table A. Hope this is clear

Thanks
Re: Huge Data extract-need suggestions [message #304760 is a reply to message #304750] Thu, 06 March 2008 09:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
search for dml error logging in oracle 10gr2 reference manual. Alternatively here is the link from this very same site
http://www.orafaq.com/node/76

Google always helps

Regards

Raj
Re: Huge Data extract-need suggestions [message #304764 is a reply to message #304760] Thu, 06 March 2008 09:20 Go to previous messageGo to next message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
Thanks , I read the article, will try it in a procedure.

I am planning to use the following steps:
Execute DML statement.

Check error logging table for errors.

If no errors found in the error logging table, commit. Else, roll back.

You can now pass information on exactly which rows failed to your application or debug log.

First time try! Thanks again Raj
Appreciate your help, will let you know
Maya

[Updated on: Thu, 06 March 2008 09:22]

Report message to a moderator

Re: Huge Data extract-need suggestions [message #304766 is a reply to message #304764] Thu, 06 March 2008 09:23 Go to previous message
Maya Menon
Messages: 10
Registered: January 2005
Junior Member
Thanks , I read the article, will try it in a procedure.

I am planning to use the following steps:
Quote:

Execute DML statement.

Check error logging table for errors.

If no errors found in the error logging table, commit. Else, roll back.

You can now pass information on exactly which rows failed to your application or debug log.





First time try! Thanks again Raj
Appreciate your help, will let you know
Maya
Previous Topic: Really its a challenge for experts.....help please
Next Topic: ORA-22905 Problem
Goto Forum:
  


Current Time: Mon Dec 05 08:36:15 CST 2016

Total time taken to generate the page: 0.04724 seconds