Distinct keyword in Oracle SQL

Hi,

I have one doubts in using the Distinct keyword to suppress the duplicates from the performance point of view. Why we should not use the Distinct Keyword in the Select statement. To suppress the duplicate, which one is the best to use to avoid the duplication.

while using the Distinct keyword in select clause, I am getting an Performance problem.

If any body knows, please let me know.

Thanks in advance.


Thanks,
Saravanan.P

[QUOTE=ily_saravanan;28290]Hi,

I have one doubts in using the Distinct keyword to suppress the duplicates from the performance point of view. Why we should not use the Distinct Keyword in the Select statement. To suppress the duplicate, which one is the best to use to avoid the duplication.

while using the Distinct keyword in select clause, I am getting an Performance problem.

If any body knows, please let me know.

Thanks in advance.


Thanks,
Saravanan.P[/QUOTE]

To eliminate duplicate records you may use group by instead of using distinct as follows

SQL> SELECT DISTINCT DEPTNO,JOB FROM EMP;

DEPTNO JOB
--------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN

9 rows selected.

SQL> SQL> SELECT DEPTNO, JOB, COUNT(*) FROM EMP
2 GROUP BY DEPTNO,JOB;

DEPTNO JOB COUNT(*)
--------- --------- ---------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 CLERK 2
20 ANALYST 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4

9 rows selected.