0
\$\begingroup\$

Is there a more efficient way to write the following PLSQL statement? It seems inefficient to me to have the SQL statement nested inside the where clause.

SELECT REQ_ID
FROM REQUESTS R
WHERE P_REQ_ID IS NULL AND 
      EXISTS (
        SELECT *
        FROM REQUESTS
        WHERE P_REQ_ID = R.REQ_ID
        )
\$\endgroup\$
1
\$\begingroup\$

Here is what I ended up with:

Select Distinct rParent.REQ_ID
From REQUESTS rParent
Inner Join REQUESTS rChild On rChild.P_REQ_ID = rParent.REQ_ID
Where rParent.P_REQ_ID IS NULL
\$\endgroup\$
6
  • \$\begingroup\$ Did you actually compare performance/resource usage for both Selects? I doubt that join plus distinct is more efficient than exists, in fact the optimizer might do the same execution plan for both... \$\endgroup\$ – dnoeth Aug 28 '18 at 7:23
  • \$\begingroup\$ Yes, obviously. as per my query understanding, table has request_id with its child entity. Parent record will have p_req_id = NULL and its child have recursive parent Request_id. So, in that case exists will give much read compare to INNER JOIN, If table is indexed very well. \$\endgroup\$ – JERRY Aug 28 '18 at 9:49
  • \$\begingroup\$ @JERRY What do you mean by "much read compare"? \$\endgroup\$ – ADH Aug 28 '18 at 14:42
  • \$\begingroup\$ @dnoeth The EXISTS query takes 378 msecs. The join plus distinct takes 608 msecs. But, the EXISTS query is nested inside a select statement in a larger query. And, the larger query is faster as far as I can tell right now. \$\endgroup\$ – ADH Aug 28 '18 at 14:51
  • \$\begingroup\$ Ok, then the optimizer might create a different plan, e.g. executing an additional join before the Exists. \$\endgroup\$ – dnoeth Aug 28 '18 at 17:29
0
\$\begingroup\$

You can do an INNER JOIN but not sure if it will change your performance significantly:

SELECT R.REQ_ID
FROM REQUESTS R
INNER JOIN (SELECT distinct P_REQ_ID 
        FROM REQUESTS) R2
ON R.P_REQ_ID IS NULL AND 
        R2.P_REQ_ID = R.REQ_ID
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.