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

On Feb 28, 9:43 am, "Chris ( Val )" <chris..._at_gmail.com> wrote:
> On Feb 28, 3:27 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > On Feb 26, 11:26 pm, "Chris ( Val )" <chris..._at_gmail.com> wrote:
>
> [ snip]
>
> > > How do I get a count of the distinct records / result set?
> > > I tried with the UNION as above, as well as the follwoing, but still
> > > get a count which includes the duplicates.
>
> > > SELECT COUNT(*) FROM
> > >  (
> > >   SELECT DISTINCT* FROM schema.tbl_A a, schema.tbl_B b, schema.tbl_C
> > > c,
> > >                         schema.tbl_D d, schema.tbl_E e, schema.tbl_F
> > > f,
> > >                         schema.tbl_G g, schema.tbl_H h, schema.tbl_I
> > > i,
> > >                         schema.tbl_J j, schema.tbl_K k, schema.tbl_L
> > > l,
> > >                         schema.tbl_M m
> > >               WHERE  a.accountid = b.ID(+)
> > >                 AND  a.custom1id = c.ID(+)
> > >                 AND  a.custom2id = d.ID(+)
> > >                 AND  a.custom3id = e.ID
> > >                 AND  a.custom4id = f.ID(+)
> > >                 AND  a.entityid = g.ID(+)
> > >                 AND  a.icpid = h.ID(+)
> > >                 AND  a.parentid = i.ID(+)
> > >                 AND  a.periodid = j.ID(+)
> > >                 AND  a.scenarioid = k.ID(+)
> > >                 AND  a.valueid = l.ID(+)
> > >                 AND  a.viewid = m.ID(+)
> > >                 AND  d.label IN ('XXX', 'XXX')
> > >                 AND  e.label IN ('XXX', 'XXX')
> > >                 AND  f.label = 'X'
> > >                 AND  k.label = 'XXX'
> > >                 AND  l.label = 'XXX'
> > >                 AND  h.label = 'X'
> > >                 AND  m.label IN ('X', 'X')
> > >  );
>
> > > Thanks in advance,
> > > Chris
>
> > I suspect that Oracle is taking a long time to remove the duplicate
> > rows due to a combination of the number of rows, the number of columns
> > (you are specifying to retrieve all columns from the tables), and the
> > amount of memory available for sorting unique (or hash unique) the
> > rows to produce a unique list of rows.  Are you able to better define
> > the columns that are interest, and eliminate any columns that are
> > common between the various tables.  
>
> Unfortunatley I did not write the SQL, and I don't understand the
> data requirements enough to manipulate it. I can ask for it to be
> changed, but that can take ages to happen :)

[snip]

Hi everyone, sorry for the delay. I was sick and then got thrown into something else.

Just wanted to let you know that I got the guys who wrote the SQL to have another
look at it, of whcih they altered it slightly with different join criteria. I have also done
a little differently. Since this script will run daily, I created a view with the distinct
values, and obtain the count(*) directly off that - It is much faster too, so I will not
look further at any optimisation.

Thanks again to everyone for all of your help.

I hadn't played with SQL for a long time, and I learn't something new from your
advice.

Cheers,
Chris Val Received on Tue Mar 11 2008 - 04:28:29 CDT