Re: How to get a distinct count of result set of multople table joins?
Date: Wed, 27 Feb 2008 14:43:41 -0800 (PST)
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:
> > 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 :)
> I have found in some cases on
> Oracle 10.2.0.2 that listing all columns in a GROUP BY allows Oracle
> to retrieve a distinct list of rows more quickly than by using the
> DISTINCT syntax:
> GROUP BY
> Will sometimes/often execute faster than this:
> SELECT DISTINCT
> In the second of your examples, I do not see where you are using the
> UNION syntax - are you trying to do something different in that
Sorry, I meant that I tried to use the same query as I just posted to
but with the COUNT statement added as in my first post.
I am having trouble getting the right count returned.
> The DISTINCT in the second example only applies to the one
> SELECT in the inline view, which could be a problem if you are using a
> UNION ALL with additional SELECTs in the inline view.
> Explain plan, or better yet a DBMS Xplan showing the predicted and
> actual results would be helpful, as requested by Ed and Mark. If the
> explain plan shows nothing useful, turn on a 10046 trace at level 8
> and see what is happening behind the scenes.
Even though this is a DEV database, I am not allowed to go to those extreams.
My job is to automate a process of extracting data, of which this SQL
to me to use in my script.
Thanks for your help.
-- ChrisReceived on Wed Feb 27 2008 - 16:43:41 CST
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
> - Show quoted text -