OUTER join? Post: 302454286

Sponsored Content

Top Forums Programming OUTER join? Post 302454286 by durden_tyler on Friday 17th of September 2010 01:59:14 PM

09-17-2010

Registered User

Quote:

Originally Posted by dvah

...I have a code where it traverses through each record froma table and creates records in another table. I use FOREACH cursor to do this, i'm using another cursor inside the FOREACH to fetch the details of other table. Please suggest me which would be more efficient, as this will run against millions of records, i have to make it efficient as much as i can.

--pseudo--
source tables - a, b
target table - c

code 1-
cursor a;
cursor b;
FOREACH a
fetch cursor b
insert to table c
END FOREACH

code 2-
cursor a {table a OUTER table b}
FOREACH a
insert to table c
END FOREACH

I'd *NEVER* go for this cursor (row-by-row) insert if the data set is millions of records.

I'd rather do this -

Code:

INSERT INTO c <column_list>
SELECT <column_list>
FROM a OUTER JOIN b
WHERE <predicates>;

And depending on the database, check for usage of hints/compiler directives, take into account undo segments, indexes, triggers etc.

tyler_durden

Last edited by durden_tyler; 09-17-2010 at 03:09 PM..

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

join (pls help on join command)

Hi, I am a new learner of join command. Some result really make me confused. Please kindly help me. input: file1: LEO oracle engineer 210375 P.Jones Office Runner ID897 L.Clip Personl Chief ID982 S.Round UNIX admin ID6 file2: Dept2C ID897 6 years Dept5Z ID982 1 year Dept3S ID6 2...

2. Programming

Oracle: fixing an outer join on subquery...

I have a stored procedure that is failing. The current query behind it is: SELECT DISTINCT <many, many values> FROM table1 LEFT OUTER JOIN table2 ON (table2.key = (select max (table2.key) from table2 where table2.key = table1.key) or ...

3. Programming

sql,multiple join,outer join issue

example sql: select a.a1,b.b1,c.c1,d.d1,e.e1 from a left outer join b on a.x=b.x left outer join c on b.y=c.y left outer join d on d.z=a.z inner join a.t=e.t I know how single outer or inner join works in sql. But I don't really understand when there are multiple of them. can...

4. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7...

5. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: Toronto:12439755:1076359:July 1, 1867:6 Quebec City:7560592:1542056:July 1, 1867:5 Halifax:938134:55284:July 1, 1867:4 Fredericton:751400:72908:July 1, 1867:3 Winnipeg:1170300:647797:July 15, 1870:7 Victoria:4168123:944735:July 20, 1871:10 Charlottetown:137900:5660:July 1, 1873:2...

6. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the...

7. Shell Programming and Scripting

Break the outer loop

Hi I'm comparing same files names which are in different folders . The first for loop for the files in DAY1 folder and the second for loop for the files in DAY2 folder . the first IF condition is for checking whether the file names are equal the second If condtion is for checking the...

8. UNIX for Dummies Questions & Answers

UNIX outer join

Hello.. I am trying to join two files of about 7000 records. it looked quite straight forward when i began, but i'm not getting the desired output. here is what i'm trying to do: cat xxx item,So,Mo,Tu aaa,1,1,1 bbb,1,1,4 ccc,1,1,0 ddd,1,1,1 cat yyy item,Tu,We aaa,1,1 bbb,4,0...

9. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi...

LEARN ABOUT DEBIAN

alzabo::runtime::rowcursor

Alzabo::Runtime::RowCursor(3pm) 			User Contributed Perl Documentation			   Alzabo::Runtime::RowCursor(3pm)

NAME

       Alzabo::Runtime::RowCursor - Cursor that returns "Alzabo::Runtime::Row" objects

SYNOPSIS

	 use Alzabo::Runtime::RowCursor;

	 my $cursor = $schema->table('foo')->all_rows;

	 while ( my $row = $cursor->next )
	 {
	     print $row->select('foo'), "
";
	 }

DESCRIPTION

       Objects in this class are used to return "Alzabo::Runtime::Row" objects for queries.  The cursor does not preload objects but creates them
       on demand, which is much more efficient.  For more details on the rational please see the RATIONALE FOR CURSORS section in Alzabo::Design.

INHERITS FROM

       "Alzabo::Runtime::Cursor"

METHODS

       next

       Returns the next "Alzabo::Runtime::Row" object or undef if no more are available.

       all_rows

       Returns all the rows available from the current point onwards.  This means that if there are five rows that will be returned when the
       object is created and you call "next" twice, calling all_rows after it will only return three.

       reset

       Resets the cursor so that the next "next" call will return the first row of the set.

       count

       Returns the number of rows returned by the cursor so far.

       next_as_hash

       Return the next row in a hash, where the hash key is the table name and the hash value is the row object.

AUTHOR

       Dave Rolsky, <autarch@urth.org>

perl v5.8.8							    2007-12-23					   Alzabo::Runtime::RowCursor(3pm)