My objective is simple, I have to create a temporary table with some random values from a employee table whenever the department is in some particular department (say 2). For the rest of departments I don't care the value, it can be NULL.
Currently I have the following :
create table test
as
select s.DEPTNAME,
cast (
(case when s.DEPTID in (2) then
(SELECT a.ENAME FROM
(SELECT b.ENAME, b.DEPTID FROM EMPLOYEE b
WHERE b.DEPTID IS NOT NULL
ORDER BY DBMS_RANDOM.VALUE) a
WHERE a.DEPTID = s.DEPTID AND ROWNUM = 1
)
END)
AS number) "ENAME" from DEPARTMENT s;
But the main issue here is related to performance. For every department value in 2 we do a sort of EMPLOYEE table to get a single random ENAME. Is there a better way to do this ? I know sample might work but I want to achieve more randomness.
Aucun commentaire:
Enregistrer un commentaire