Re: SQL question
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 24 Oct 2012 13:58:49 -0600
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.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
