Home » SQL & PL/SQL » SQL & PL/SQL » Pls tell me the difference between these 2 queries...?? (oracle,pi,windowsxp professional)
Pls tell me the difference between these 2 queries...?? [message #327934] |
Wed, 18 June 2008 07:09  |
aviana
Messages: 106 Registered: July 2007
|
Senior Member |
|
|
Hi,
I am pasting here the 2 queries where i get a difference in the no.of records in the output.I can't really understand the difference.Can someone pls explain it for me?
SELECT COUNT(B.BUILDING_ID)
FROM T_BUILDINGS B,
(SELECT * FROM T_SUB_BUILDINGS s WHERE NVL(invalid,'N') = 'N') s
WHERE b.building_id = s.building_id(+)
AND NVL(b.invalid,'N') = 'N' --2119846 records
-----------------------------------------------------
SELECT COUNT(B.BUILDING_ID)
FROM T_BUILDINGS B,T_SUB_BUILDINGS s
WHERE b.building_id = s.building_id(+)
NVL(s.invalid,'N') = 'N' AND
AND NVL(b.invalid,'N') = 'N' --2094440 records
------------------------------------------------
|
|
|
Re: Pls tell me the difference between these 2 queries...?? [message #327937 is a reply to message #327934] |
Wed, 18 June 2008 07:12   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, the main difference is that the second query is invalid and wouldn't run at all.
Please copy and paste the output from SQL*Plus showing the actual queries running and their results.
Is it possible that you are running these on a live system, and that the data has changed in between the queries
|
|
|
Re: Pls tell me the difference between these 2 queries...?? [message #327942 is a reply to message #327937] |
Wed, 18 June 2008 07:20   |
aviana
Messages: 106 Registered: July 2007
|
Senior Member |
|
|
Hi Jrowbottom
thanks much for a fastest reply...
why did you say that the second query is invalid?pls explain?Sorry there was a syntax mistake and i have corrected it now and ran again.
No the data has not changed as I run both one after the other
I ran using TOAD but now I am pasting the output in sqlplus for you...
Pls let me know the difference as I need to explain this to someone and i have no clue:(
SQL> SELECT COUNT(B.BUILDING_ID)
2 FROM T_BUILDINGS B,
3 (SELECT * FROM T_SUB_BUILDINGS s WHERE NVL(invalid,'N') = 'N') s
4 WHERE b.building_id = s.building_id(+)
5 AND NVL(b.invalid,'N') = 'N';
COUNT(B.BUILDING_ID)
--------------------
2119856
SQL> SELECT COUNT(B.BUILDING_ID)
2 FROM T_BUILDINGS B,T_SUB_BUILDINGS s
3 WHERE b.building_id = s.building_id(+) AND
4 NVL(s.invalid,'N') = 'N'
5 AND NVL(b.invalid,'N') = 'N' ;
COUNT(B.BUILDING_ID)
--------------------
2094449
|
|
|
|
|
|
Re: Pls tell me the difference between these 2 queries...?? [message #327959 is a reply to message #327951] |
Wed, 18 June 2008 07:47   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
O.K I will tell you today but not from next time.
SQL> l
1 with t_buildings
2 as
3 (select 1 id, 'N' invalid from dual union all
4 select 2, null from dual union all
5 select 3, 'N' from dual
6 ),
7 t_sub_buildings
8 as
9 (select 1 id, 'N' invalid from dual union all
10 select 2, 'Y' from dual union all
11 select 3,'Y' from dual
12 )
13 select count(*) from t_buildings b, t_sub_buildings s
14 where
15 b.id = s.id (+)
16 and
17 nvl(s.invalid,'N') = 'N'
18 and
19* nvl(b.invalid,'N') = 'N'
SQL> /
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1832122311
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN OUTER| | 3 | 33 | 13 (8)| 00:00:01 |
| 4 | VIEW | | 3 | 15 | 6 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | VIEW | | 3 | 18 | 6 (0)| 00:00:01 |
| 10 | UNION-ALL | | | | | |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - filter(NVL("S"."INVALID",'N')='N')
3 - access("B"."ID"="S"."ID"(+))
SQL> l
1 with t_buildings
2 as
3 (select 1 id, 'N' invalid from dual union all
4 select 2, null from dual union all
5 select 3, 'N' from dual
6 ),
7 t_sub_buildings
8 as
9 (select 1 id, 'N' invalid from dual union all
10 select 2, 'Y' from dual union all
11 select 3,'Y' from dual
12 )
13 select count(*) from t_buildings b,
(select * from t_sub_buildings where nvl(invalid,'N') = 'N') s
14 where
15 b.id = s.id (+)
16 and
17* nvl(b.invalid,'N') = 'N'
SQL> /
COUNT(*)
----------
3
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3412672360
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | HASH JOIN OUTER| | 3 | 33 | 9 (12)| 00:00:01 |
| 3 | VIEW | | 3 | 15 | 6 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | VIEW | | 3 | 18 | 2 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID"="T_SUB_BUILDINGS"."ID"(+))
11 - filter(NULL IS NOT NULL)
13 - filter(NULL IS NOT NULL)
Especially look at the predicate information where the filter is getting applied. Hope you understand it.
Regards
Raj
[Edit:] Added plan information.
[Updated on: Wed, 18 June 2008 07:50] Report message to a moderator
|
|
|
|
|
|
Re: Pls tell me the difference between these 2 queries...?? [message #328184 is a reply to message #328179] |
Thu, 19 June 2008 04:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you outer join to a table, and do not append the (+) to all references to the columns of that table, then the outer join has no effect.
Observe the difference between these two queries: with t_buildings
as
(select 1 id, 'N' invalid from dual union all
select 2, null from dual union all
select 3, 'N' from dual
),
t_sub_buildings
as
(select 1 id, 'N' invalid from dual union all
select 2, 'Y' from dual union all
select 3,'Y' from dual
)
select count(*) from t_buildings b, t_sub_buildings s
where
b.id = s.id (+)
and
nvl(s.invalid(+),'N') = 'N'
and
nvl(b.invalid,'N') = 'N'
and
with t_buildings
as
(select 1 id, 'N' invalid from dual union all
select 2, null from dual union all
select 3, 'N' from dual
),
t_sub_buildings
as
(select 1 id, 'N' invalid from dual union all
select 2, 'Y' from dual union all
select 3,'Y' from dual
)
select count(*) from t_buildings b, t_sub_buildings s
where
b.id = s.id (+)
and
nvl(s.invalid,'N') = 'N'
and
nvl(b.invalid,'N') = 'N'
|
|
|
|
Re: Pls tell me the difference between these 2 queries...?? [message #328199 is a reply to message #328184] |
Thu, 19 June 2008 06:11  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Without the outer join operator on all the columns.
SQL> explain plan
2 for
3 with t_buildings
4 as
5 (select 1 id, 'N' invalid from dual union all
6 select 2, null from dual union all
7 select 3, 'N' from dual
8 ),
9 t_sub_buildings
10 as
11 (select 1 id, 'N' invalid from dual union all
12 select 2, 'Y' from dual union all
13 select 3,'Y' from dual
14 )
15 select count(*) from t_buildings b, t_sub_buildings s
16 where
17 b.id = s.id (+)
18 and
19 nvl(s.invalid,'N') = 'N'
20 and
21 nvl(b.invalid,'N') = 'N';
Explained.
SQL> /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1832122311
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN OUTER| | 3 | 33 | 13 (8)| 00:00:01 |
| 4 | VIEW | | 3 | 15 | 6 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | VIEW | | 3 | 18 | 6 (0)| 00:00:01 |
| 10 | UNION-ALL | | | | | |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("S"."INVALID",'N')='N')
3 - access("B"."ID"="S"."ID"(+))
26 rows selected.
With the outer join operator on all the columns.
SQL> l
1 explain plan
2 for
3 with t_buildings
4 as
5 (select 1 id, 'N' invalid from dual union all
6 select 2, null from dual union all
7 select 3, 'N' from dual
8 ),
9 t_sub_buildings
10 as
11 (select 1 id, 'N' invalid from dual union all
12 select 2, 'Y' from dual union all
13 select 3,'Y' from dual
14 )
15 select count(*) from t_buildings b, t_sub_buildings s
16 where
17 b.id = s.id (+)
18 and
19 nvl(s.invalid (+),'N') = 'N'
20 and
21* nvl(b.invalid,'N') = 'N'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3412672360
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | HASH JOIN OUTER| | 3 | 33 | 9 (12)| 00:00:01 |
| 3 | VIEW | | 3 | 15 | 6 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | VIEW | | 3 | 18 | 2 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID"="S"."ID"(+))
11 - filter(NULL IS NOT NULL)
13 - filter(NULL IS NOT NULL)
28 rows selected.
Without the outer join operator on all the columns and also no nvl function around it.
1 explain plan
2 for
3 with t_buildings
4 as
5 (select 1 id, 'N' invalid from dual union all
6 select 2, null from dual union all
7 select 3, 'N' from dual
8 ),
9 t_sub_buildings
10 as
11 (select 1 id, 'N' invalid from dual union all
12 select 2, 'Y' from dual union all
13 select 3,'Y' from dual
14 )
15 select count(*) from t_buildings b, t_sub_buildings s
16 where
17 b.id = s.id (+)
18 and
19 s.invalid = 'N'
20 and
21* nvl(b.invalid,'N') = 'N'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 4102668923
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | HASH JOIN | | 1 | 11 | 9 (12)| 00:00:01 |
| 3 | VIEW | | 3 | 15 | 6 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | VIEW | | 3 | 18 | 2 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID"="S"."ID")
11 - filter(NULL IS NOT NULL)
13 - filter(NULL IS NOT NULL)
28 rows selected.
I agree with your quote but only partially not completely in this context
Quote: |
If you outer join to a table, and do not append the (+) to all references to the columns of that table, then the outer join has no effect.
|
Based on OP's query my assumption is as follows
Count the number of rows in t_buildings where the the invalid column is null or equal to 'N'. If the building id is exists in sub_buildings table then it should also equates to Null or 'N'.
As far as I know to achieve this output the only way to get around it is avoid the outer join operator on the column on which you are equating to a specific value. If you include the outer join operator on all the columns of the outer joined table then it defeats the whole purpose of outer joining atleast in this case.
To backup my explanation I have attached the plan. You could see in the plan oracle is doing an outer join on the first two queries but in the third query since I don't have a function around the column of the outer joined table it opts to do a hash join rather than hash join outer.
Regards
Raj
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:53:56 CST 2025
|