Re: Query question

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sun, 23 May 2004 15:36:37 +0100
Message-ID: <uad1b0huujvdjs1cai7ervapp9fmj0s5h1@4ax.com>

On Fri, 21 May 2004 11:37:08 +0100, "Mark" <mark.harris.nospam_at_ukonline.co.uk.nospam> wrote:

>I have 3 tables, A, B and C. There is a one-to many relationship between A
>and B and C and B. I'm trying to write a query which will get out all
>records in A and B for a given record in C, plus those which also *don't*
>exist in C.
>
>E.g. in A:
>
>a_id a_text
>---- ------
>1 a_one
>2 a_two
>3 a_three
>

[snip C]
>
>In B:
>
>b_id b_text a_id c_id
>---- ------ ---- ----
>1 b_one 1 1
>2 b_three 3 1
>
>I've tried:
>SELECT B.b_text FROM A, B
>WHERE A.a_id = B.a_id(+)
>AND B.c_id = 1
>
>... but this only displays "b_one" and "b_three". I'd also like to see a
>blank record for the missing record in A (a_id = 2). I understand I'm on the
>"wrong end" of an outer join, but wondered whether there was any way around
>this. The only way that I could think of was to SELECT DISTINCT a_id FROM A
>and MINUS all a_ids in B WHERE c_id = 1. However, the tables involved are
>likely to become large and as such will obviously degrade query performance.

 Do you simply want:

SELECT  B.b_text FROM A, B
WHERE   A.a_id = B.a_id(+)
AND     B.c_id (+) = 1;
               ^^^

 i.e., apply the outer join operator to the B.c_id condition as well. Otherwise, you're doing an outer join, but then filtering out the row that had NULLs in the B columns due to the outer join.

 You can see the difference from the execution plans:

SQL> explain plan for

  2  SELECT  B.b_text FROM A, B
  3  WHERE   A.a_id = B.a_id(+)
  4  AND     B.c_id = 1;

Explained.

SQL> select * from table(dbms_xplan.display());

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   MERGE JOIN OUTER   |             |       |       |       |
|   3 |    SORT JOIN         |             |       |       |       |
|   4 |     TABLE ACCESS FULL| A           |       |       |       |
|*  5 |    SORT JOIN         |             |       |       |       |
|   6 |     TABLE ACCESS FULL| B           |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter("B"."C_ID"=1)
   5 - access("A"."A_ID"="B"."A_ID"(+))
       filter("A"."A_ID"="B"."A_ID"(+))

Note: rule based optimization

SQL> explain plan for

  2  SELECT  B.b_text FROM A, B
  3  WHERE   A.a_id = B.a_id(+)
  4  AND     B.c_id (+) = 1;

Explained.

21 rows selected.

SQL> select * from table(dbms_xplan.display());

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  MERGE JOIN OUTER    |             |       |       |       |
|   2 |   SORT JOIN          |             |       |       |       |
|   3 |    TABLE ACCESS FULL | A           |       |       |       |
|*  4 |   SORT JOIN          |             |       |       |       |
|*  5 |    TABLE ACCESS FULL | B           |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   4 - access("A"."A_ID"="B"."A_ID"(+))
       filter("A"."A_ID"="B"."A_ID"(+))
   5 - filter("B"."C_ID"(+)=1)

Note: rule based optimization

20 rows selected.

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space

Received on Sun May 23 2004 - 09:36:37 CDT