Re: perl DBI multiple query problem

SAS wrote:
>
> Hi,
>
> I hope you can help me on how to extract data from the oracle database
> without going into so many loops. Because I execute, bind, and fetch
> in loops, my data extract is extremly slow. I can not do any joins
> because the tables are extremly large(total database has 3 million
> records) and one table is located in another database. Because each
> query needs data from another query, I am very clueless on how to
> speed up data extract. I would very much appreciate it, If someone can
> help me make the extract more efficient...Any example would be very
> helpful.
>
> I have total of 5 queries and each are depended upon one or another:
>
> $sth_A = $dbh->prepare(q{Select field1, field2 from tableA where
> field3 = '6'});
> $sth_A->execute;
> $sth_A->bind_columns(\$field1,\$field2);
>
> $sth_B = $dbh->prepare( q{Select field1, field2 from tableB where
> field3 = tableA.field1 and field4 = tabalA.field2});
> $sth_C = $dbh->prepare( q{Select field1, field2 from tableC where
> field1 = tableB.field1} ); (tableC is located on another database. I
> only have a link to it)
> $sth_D = $dbh->prepare( q{Select field1, field2 from tableD where
> field3 = tableA.field1 and field4 = tabalA.field2});
> $sth_E = $dbh->prepare( q{Select field1, field2 from tableE where
> field3 = tableA.field1 and field4 = tabalA.field2})
>

Apart from the fact that tableC is accessed via a dblink I cannot see any reason why you should not join tableA, tableB, tableD and tableE. Your sequence is a perfect victim for a star join. By executing joins Oracle internally performs more or less the same nested loops as your client logic. Make sure that tableB has an index on (field3, field4), tableD on (field3,field4)... This would save many, many roundtrips and reececutions.

3 Million records is not THAT much, is it?

U.

-- 
Uwe Schneider - WEB.DE AG  - Amalienbadstr. 41  - 76227 Karlsruhe 
F&E / Database & Software Technologies - Tel. (+49) 721 94329 8320