OraFAQ Forum: SQL & PL/SQL » RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT

Home » SQL & PL/SQL » SQL & PL/SQL » RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575288]

Mon, 21 January 2013 23:39

sss111ind
Messages: 282
Registered: April 2012
Location: India

Senior Member

Hi All,

I WANT to RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT.is any OTHER WAY to DO THIS due to performance in Oracle 10g

SELECT *
FROM DEPT D,
  (SELECT DEPTNO,
    MAX(DECODE(RN,1,ENAME,NULL)) ENAME1,
    MAX(DECODE(RN,2,ENAME,NULL)) ENAME2
  FROM
    (SELECT DEPTNO,
      ENAME,
      ROW_NUMBER() over(partition BY DEPTNO order by ENAME) RN
    FROM emp
    )group by deptno
  )e
WHERE e.deptno=d.deptno;

Regards,
Nathan

[SPLIT from a hijacked topic by LF]

[Updated on: Tue, 22 January 2013 01:01] by Moderator

Report message to a moderator

Re: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575297 is a reply to message #575288]

Tue, 22 January 2013 01:25

Michel Cadot
Messages: 54675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There can't be any performances problem with SCOTT's tables as there are too few rows.
In addition, it should be an outer join and not an inner one.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575304 is a reply to message #575297]

Tue, 22 January 2013 02:20

sss111ind
Messages: 282
Registered: April 2012
Location: India

Senior Member

Hi Michel,

Which is the better way in persepctive of performace.

SELECT dname,
  deptno,
  loc,
  MAX(DECODE(RN,1,ENAME,NULL)) ENAME1,
  MAX(DECODE(RN,2,ENAME,NULL)) ENAME2
FROM
  (SELECT d.DNAME,
    d.deptno,
    d.loc,
    e.ENAME,
    ROW_NUMBER() over(partition BY e.DEPTNO order by e.ENAME) RN
  FROM DEPT D,
    EMP E
  WHERE D.DEPTNO=E.DEPTNO(+)
  )
GROUP BY dname,
  deptno,
  loc

Regards,
Nathan

Report message to a moderator

Re: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575306 is a reply to message #575304]

Tue, 22 January 2013 02:37

Michel Cadot
Messages: 54675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is always better to group the sooner possible to reduce the number of rows to handle and the width of the rows in the following steps.

Just have a look at the explain plan (replacing the inner join by an outer one in the first query).

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |   188 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |         |     4 |   188 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     3 |    81 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      |         |     3 |    81 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |         |     3 |    69 |     4  (25)| 00:00:01 |
|   7 |      VIEW                    |         |    15 |   345 |     4  (25)| 00:00:01 |
|   8 |       WINDOW SORT            |         |    15 |   135 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL     | EMP     |    15 |   135 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
       filter("E"."DEPTNO"(+)="D"."DEPTNO")

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |    14 |   574 |     8  (38)| 00:00:01 |
|   1 |  HASH GROUP BY                  |         |    14 |   574 |     8  (38)| 00:00:01 |
|   2 |   VIEW                          |         |    14 |   574 |     7  (29)| 00:00:01 |
|   3 |    WINDOW SORT                  |         |    14 |   406 |     7  (29)| 00:00:01 |
|   4 |     MERGE JOIN OUTER            |         |    14 |   406 |     6  (17)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | DEPT_PK |     4 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|*  8 |       TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("D"."DEPTNO"="E"."DEPTNO"(+))
       filter("D"."DEPTNO"="E"."DEPTNO"(+))
   8 - filter("E"."DEPTNO"(+) IS NOT NULL)

(Unexpectedly, the number of rows returned is wrong in the end of the second statement when it is correct inside it, a MERGE JOIN OUTER can't return more rows than the left/first table; I think it is a bug in explain plan.)

Regards
Michel

Report message to a moderator