Oracle Performance and Engineering

This morning, I was forwarded an interesting problem. A hierarchical SQL query (i.e., with CONNECT BY - START WITH clause) operating on a view based on remote database table (DB Link) was failing on an Oracle 10.2.0.2 database.

The query looked like -

SELECT a.*
 FROM (SELECT     lineitem.*,
                  ROW_NUMBER () OVER (PARTITION BY lineitem.tran_id ORDER BY LEVEL) sequence_number
             FROM (SELECT *
                     FROM lineitem_vw
                    WHERE ID = 1122) lineitem
       START WITH lineitem.ID IN (SELECT ID
                                    FROM header_vw
                                   WHERE company_id = 1122)
       CONNECT BY PRIOR lineitem.next_id = lineitem.target_id) a;

Where the lineitem_vw is a view defined on remote table(s) accessed with database link(s).

The way I solved this problem -

(1) Find out what the ORA-2016 error is all about. Google is the usual favorite, though I really chasten myself to use Metalink for researching such errors. The main reason behind going to Metalink is, Google cannot search Metalink and thus cannot show me if this error is, say, because of a bug in a particular database version OR if there is already a workaround for this available.

The result was a crisp one record just stating the description of the error -

Error:  ORA 2016
Text:   cannot use a subquery in a START WITH on a remote database
-------------------------------------------------------------------------------
Cause:  An attempt was made to use a subquery in a START WITH clause.
      Such a subquery is not currently permitted if the table in the FROM
      clause of the tree query is in a remote database.
Action: It is necessary to connect directly to the remote database before
      using a subquery in the START WITH clause.

(2) Now that I knew the problem is because of the particular interaction between "CONNECT BY" and "Accessing remote data using DB link" happening together, my objective was to separate these two so each happens in a separate "thread" from the other.

Subquery-factoring or WITH clause came to use again. I took out and "materialized" the remote dataset ahead of the query execution so the CONNECT BY gets a ready-made dataset to build the tree upon.


The revised query looked like -

with prebuilt_remote_data as (SELECT /*+ MATERIALIZE */ *
                     FROM lineitem_vw
                    WHERE ID = 1122)
SELECT a.*
 FROM (SELECT     lineitem.*,
                  ROW_NUMBER () OVER (PARTITION BY lineitem.tran_id ORDER BY LEVEL) sequence_number
             FROM (select * from prebuilt_remote_data) lineitem
       START WITH lineitem.ID IN (SELECT ID
                                    FROM header_vw
                                   WHERE company_id = 1122)
       CONNECT BY PRIOR lineitem.next_id = lineitem.target_id) a;

To make sure that the subquery is indeed factored ahead, I added the MATERIALIZE hint.

The revised query worked like a charm!