Re: SQL question

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Wed, 24 Oct 2012 13:58:49 -0600
Message-ID: <50884879.3010804_at_gmail.com>

It would be interesting to see an explain plan on each one of those. The count(*) could conceivably use some shortcuts, as the returned data is thrown away, potentially giving significantly different explain plans if indexes are available.

It would also be interesting to see where NULLs might be involved - col1/2/3 and/or the A/B/C.IDs. In particular, is the combination A.COL1 = NULL , A.COL = NULL, B.COL3 = NULL being returned?

Finally, since you retrieve nothing from TABLE3, what happens to the count when you drop all reference to that table from either/both queries. Theoretically table3 should not be required in the join.

/Hans

On 24/10/2012 1:00 PM, Ramadoss, Karthik wrote:
> This is probably a simple one but definitely something new for me.
> Database: 11.2.0.3
> OS: Oracle Linux 5.6
>
> A SQL like
>
> SELECT A.COL1,
> A.COL2,
> B.COL3
> FROM TABLE1 A,
> TABLE2 B,
> TABLE3 C
> WHERE A.ID = B.ID AND B.ID = C.ID
>
> returns 1,192,940 rows.
>
> And
>
> SELECT COUNT(*) from
> (SELECT A.COL1,
> A.COL2,
> B.COL3
> FROM TABLE1 A,
> TABLE2 B,
> TABLE3 C
> WHERE A.ID = B.ID AND B.ID = C.ID)
>
> Returns 1,192,978 rows.
>
> Anyone know what is going on here? I would expect both to return the same number of rows.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 24 2012 - 21:58:49 CEST