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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #327944 is a reply to message #327942] Wed, 18 June 2008 07:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Do an explain plan on the both the queries in sqlplus and look for the predicate information. You will understand why. because they are two different queries.

Regards

Raj
Re: Pls tell me the difference between these 2 queries...?? [message #327951 is a reply to message #327944] Wed, 18 June 2008 07:35 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
I havent run explain plan before.
When i ran,
explain plan for <sql statement>, it just gives output as
Explained.
Pls give me the syntax
Also if you know the difference between queries, why cant you tell?
Re: Pls tell me the difference between these 2 queries...?? [message #327953 is a reply to message #327951] Wed, 18 June 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Syntax depends on the version (4 decimals) you didn't post.

Regards
Michel
Re: Pls tell me the difference between these 2 queries...?? [message #327959 is a reply to message #327951] Wed, 18 June 2008 07:47 Go to previous messageGo to next message
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 #327968 is a reply to message #327959] Wed, 18 June 2008 08:19 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Thanks for putting the explain plan
But sorry i can't understand it
Can someone tell me the difference in simple words if you know the answer?
Re: Pls tell me the difference between these 2 queries...?? [message #328099 is a reply to message #327934] Wed, 18 June 2008 23:14 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> Can someone tell me the difference in simple words if you know the answer?
It is written in the description of the (+) operator (section Outer Joins) in the documentation, found e.g. online on http://tahiti.oracle.com/.
As you did not post your Oracle version, I will let the search on you.
Re: Pls tell me the difference between these 2 queries...?? [message #328179 is a reply to message #328099] Thu, 19 June 2008 04:32 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
thanks for sending the link
I have one doubt -both queries have outer joins in it and how come the difference?
Re: Pls tell me the difference between these 2 queries...?? [message #328184 is a reply to message #328179] Thu, 19 June 2008 04:55 Go to previous messageGo to next message
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 #328190 is a reply to message #327934] Thu, 19 June 2008 05:41 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> thanks for sending the link
I am afraid it was useless as you do not seem to be able to find the suggested information.
Nevermind though, the outer join for 10gR2 version is described in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054062.
JRowbottom's statement is covered by this part:
Quote:
If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
Re: Pls tell me the difference between these 2 queries...?? [message #328199 is a reply to message #328184] Thu, 19 June 2008 06:11 Go to previous message
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
Previous Topic: How to change schema's tablespace
Next Topic: diff between natural and inner join
Goto Forum:
  


Current Time: Sat Feb 08 07:53:56 CST 2025