Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Query performance: DivideAndConquer vs. BruteForce
I have a question about what would be the best way to query an SQL
database (I am using Oracle) where many parent and child tables are
involved. I seek opinions on which of the following two methods would
require the least execution time:
Method 1 - BruteForce method:
Execute a single query where all the parent and child tables are
joined and all the data is returned at once. For example:
select *
from ParentTable P, Child1Table C1, Child2Table C2, ...
where P.Child1Key = C1.Key and P.Child2Key = C2.Key and ...
Method 2 - DivideAndConquer method:
Execute more than one query. The first query gets data from the parent
table:
select *
from ParentTable P
where ...
Next, in the front-end code, iterate through the parent table data received and execute queries into the children tables:
select *
from Child1Table C1
where C1.Key = varPChild1Key and ...
I am inclined to believe that the DivideAndConquer method would be faster, but I invite your comments, especially with respect to Oracle 9i.
The scenarios I have described are actually much simplified representations of my situation. Received on Fri Jan 04 2002 - 09:05:17 CST