Oracle SQL Tuning - How to Combining Unrelated SQL

If you are running a number of simple database queries, you can improve performance by combining them into a single query, even if they are not related. This approach is particularly suited to client-server applications in which the network needs to be protected from excess data traffic. Combining unrelated SQL statements will not reduce the RDBMS database overheads (all tables still need to be read) but will limit the associated cursor and network overheads. Examples of queries that are well suited to this approach are those that set up default screen headers or report banners by obtaining program initialization information from a number of database tables. The usual approach is to perform one query after another, as shown below:

SELECT name
FROM   emp
WHERE  emp_no = 1234;
SELECT name
FROM   dpt
WHERE  dpt_no = 10;
SELECT  name
FROM   cat
WHERE  cat_type = 'RD'  ;

To combine all these separate queries into one SQL statement, you must perform an outer join on each table with a table that will always be valid (i.e., one that will return at least one row). The easiest way to ensure this is to set up a dummy outer join with the system table DUAL as shown in the following example:

SELECT E.name, D.name, C.name
FROM   cat  C,
       dpt  D,
       emp  E,
       DUAL X
WHERE  NVL('X', X.dummy) = NVL('X', E.rowid (+))
AND    NVL('X', X.dummy) = NVL('X', D.rowid (+))
AND    NVL('X', X.dummy) = NVL('X', C.rowid (+))
AND    E.emp_no   (+)    = 1234
AND    D.dept_no  (+)    = 10
AND    C.cat_type (+)    = 'RD'

This type of processing gives you the best performance payoff on machines that are connected to busy networks. Every time a SQL statement is executed, the RDBMS kernel is visited a number of times: at least once to parse the statement, once to bind the variables, and once to retrieve the selected rows. With this simple example, you reduce network overhead by two-thirds.