Home » SQL & PL/SQL » SQL & PL/SQL » RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT
RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575288] Mon, 21 January 2013 23:39 Go to next message
sss111ind
Messages: 496
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
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

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 Go to previous messageGo to next message
sss111ind
Messages: 496
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
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
Re: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575316 is a reply to message #575306] Tue, 22 January 2013 03:25 Go to previous messageGo to next message
sss111ind
Messages: 496
Registered: April 2012
Location: India
Senior Member

Hi Michel,

Thanks for your information ,so as per your view first is the better than second one.

Regards,
Nathan
Re: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575322 is a reply to message #575316] Tue, 22 January 2013 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT [message #575323 is a reply to message #575322] Tue, 22 January 2013 04:17 Go to previous message
sss111ind
Messages: 496
Registered: April 2012
Location: India
Senior Member

Thanks Michel,

Appreciated your help.

Regards,
Nathan
Previous Topic: External Tables (Oracle Loader)
Next Topic: insert mp3 file into BLOB
Goto Forum:
  


Current Time: Wed Dec 17 14:17:43 CST 2014

Total time taken to generate the page: 0.11723 seconds