alternative for 'Distinct'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: alternative for 'Distinct'

  1. #1
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Hi Friends,
    Could anyone plz tell me what is the alternative for 'Distinct' keyword in Oracle 9i.
    Thanks in advance
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select job
    from emp
    group by job


  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    here's an example.......

    instead of...........

    SELECT DISTINCT d.deptno ,
    d.dname ,
    FROM dept d ,
    emp e
    WHERE d.deptno = e.deptno ;

    write it this way...........


    SELECT d.deptno ,
    d.dname
    FROM dept d
    WHERE EXISTS ( SELECT e.deptno
    FROM emp e
    WHERE d.deptno = e.deptno ) ;


    and of course, don't take my word for it, generate an explain for both and compare.

    I in fact today took a query on my current project and reduced cost from 123,000 to 8, the query went from 8 minutes to 25 seconds by avoiding the select distinct.

    steve
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Thanks Pando and Steve for valuable suggestions. I'll go with Steve. Pando, your option takes same time as distinct...but still, an alternative. Thanks a lot...
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yes mine is exactly the same as distinct

    exists would be fast if outer query returns small number of rows, I am not so sure if itīs large .....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width