Re: How to get a distinct count of result set of multople table joins?

On Feb 27, 8:05 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Feb 26, 11:26 pm, "Chris ( Val )" <chris..._at_gmail.com> wrote:
>
> > Hi everyone,
>
> > I haven't played with SQL for a while, but the following SQL returns
> > ~700,000 records ( a lot of duplicates ), and I used a UNION with the
> > same query to reduce the records down to ~250,000. Is there a more
> > efficient solution, as the UNION takes some time to run? ~3 mins.
> []
>
> > Thanks in advance,
> > Chris
>
> What does the EXPLAIN PLAN show?
>
> Performance issues are always data dependent. IOW, the phrase:
> KNOW THY DATA
> is one to take to heart.
>
> Come back with the PLAN and we may have some suggestions.
>    Ed

Chris, if the query in the select distinct is the same as the first there should be no duplicates returned.

What is the full version of Oracle?
Does the explain plan Ed asked for show a hash group by?

If so, depending on your version of Oracle, there is a bug where queries solved with a hash group by can return wrong results; however, I would expect the distinct to eliminate duplicates still.

Are you sure you just do not have more rows than you expected?

HTH -- Mark D Powell -- Received on Wed Feb 27 2008 - 09:02:43 CST