Home » SQL & PL/SQL » SQL & PL/SQL » Explanation needed for a Sql query
Explanation needed for a Sql query [message #378396] Tue, 30 December 2008 04:27 Go to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi,

Emp table is consisting 14 rows but when i run this query with a cross join as shown in the query it is returning the count of rows as shown below

[QUOTE]SQL> Select Count(Empno)
  2  From
  3   (SELECT     empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS deptno
  4    FROM       emp
  5   Cross Join (select level*10000 AS x FROM dual CONNECT BY level <= 10));

COUNT(EMPNO)
------------
    19573400

Elapsed: 00:00:28.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS
   3    2       VIEW
   4    3         CONNECT BY (WITH FILTERING)
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (FULL) OF 'DUAL'
   7    5             TABLE ACCESS (BY USER ROWID) OF 'DUAL'
   8    4           NESTED LOOPS
   9    8             BUFFER (SORT)
  10    9               CONNECT BY PUMP
  11    8             FILTER
  12   11               TABLE ACCESS (FULL) OF 'DUAL'
  13    2       TABLE ACCESS (FULL) OF 'EMP'[/QUOTE]

Can anyone explain how it is executing.

Thanks for your help.
Re: Explanation needed for a Sql query [message #378409 is a reply to message #378396] Tue, 30 December 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Select Count(Empno)
  2  From
  3    (SELECT     empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS deptno
  4     FROM       emp
  5    Cross Join (select level*10000 AS x FROM dual CONNECT BY level <= 10));
COUNT(EMPNO)
------------
         140

1 row selected.

ALWAYS post your version (with 4 decimals).

Regards
Michel
Re: Explanation needed for a Sql query [message #378543 is a reply to message #378409] Tue, 30 December 2008 22:36 Go to previous messageGo to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi,

Thanks for your reply.

We are using Oracle Release 9.2.0.1.0.

The same query if i execute in my database it is returning count as 19573400 not as 140

  SQL> Select Count(Empno)
  2  From
  3   (SELECT     empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS deptno
  4    FROM       emp
  5   Cross Join (select level*10000 AS x FROM dual CONNECT BY level <= 10));

COUNT(EMPNO)
------------
    19573400


Why is that difference between your output and my output.

Thanks for your interest
Re: Explanation needed for a Sql query [message #378562 is a reply to message #378543] Tue, 30 December 2008 23:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Assuming that there REALLY are only 14 rows in EMP, this looks like a bug in v9.

v9 and 10.1 had some optimiser issues with ansi join syntax. Try this

Select Count(Empno)
From
 (SELECT     empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS deptno
  FROM       emp
 , (select level*10000 AS x FROM dual CONNECT BY level <= 10)
);


But do us all a favour and PROVE that you have only 14 rows by pasting the SQL
SELECT count(*) FROM emp


Ross Leishman
Re: v9 and 10.1 had some optimiser issues with ansi join syntax [message #378568 is a reply to message #378562] Wed, 31 December 2008 00:13 Go to previous messageGo to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi Ross,

For your reference i am attaching the screen shot. Please find the attachment
Re: Explanation needed for a Sql query [message #378593 is a reply to message #378543] Wed, 31 December 2008 01:07 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Select Count(Empno)
  2  From
  3    (SELECT     empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS deptno
  4     FROM       emp
  5     Cross Join (select level*10000 AS x FROM dual CONNECT BY level <= 10));
COUNT(EMPNO)
------------
         140

1 row selected.

SQL> @v

Version Oracle : 9.2.0.8.0

You need to upgrade to the latest patchset.

Regards
Michel
Previous Topic: Decode function in select statement
Next Topic: Reg Check constraint over a particular column
Goto Forum:
  


Current Time: Tue Dec 06 15:54:24 CST 2016

Total time taken to generate the page: 0.14523 seconds