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

#41

"Chris ( Val )" <chris... (AT) gmail (DOT) com> wrote:

Quote:

QUERY
* SELECT *
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(+)

Other comments notwithstanding, do you realise some of these
outer joins are negated by...

Quote:

* * * * * * * * 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');

You'll need additional (+)s on these clauses if you want
a genuine outer join.

Try the alternative syntax...

select distinct *
from
schema.tbl_a a
left outer join schema.tbl_b b on b.id = a.accountid
left outer join schema.tbl_c c on c.id = a.custom1id
left outer join schema.tbl_d d
on d.id = a.custom2id
and d.label in ('XXX', 'XXX')
join schema.tbl_e e
on e.id = a.custom3id
and e.label in ('XXX', 'XXX')
left outer join schema.tbl_f f
on f.id = a.custom4id
and f.label = 'X'
left outer join schema.tbl_g g on g.id = a.entityid
left outer join schema.tbl_h h
on h.id = a.icpid
and h.label = 'X'
left outer join schema.tbl_i i on i.id = a.parentid
left outer join schema.tbl_j j on j.id = a.periodid
left outer join schema.tbl_k k
on k.id = a.scenarioid
and k.label = 'XXX'
left outer join schema.tbl_l l
on l.id = a.valueid
and l.label = 'XXX'
left outer join schema.tbl_m m
on m.id = a.viewid
and m.label in ('X', 'X');

--
Peter

#42

On Feb 28, 9:43*am, "Chris ( Val )" <chris... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 28, 3:27*am, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:

On Feb 26, 11:26*pm, "Chris ( Val )" <chris... (AT) gmail (DOT) 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

#43

On Feb 28, 9:43*am, "Chris ( Val )" <chris... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 28, 3:27*am, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:

On Feb 26, 11:26*pm, "Chris ( Val )" <chris... (AT) gmail (DOT) 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

#44

On Feb 28, 9:43*am, "Chris ( Val )" <chris... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 28, 3:27*am, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:

On Feb 26, 11:26*pm, "Chris ( Val )" <chris... (AT) gmail (DOT) 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

#45

On Feb 28, 9:43*am, "Chris ( Val )" <chris... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 28, 3:27*am, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:

On Feb 26, 11:26*pm, "Chris ( Val )" <chris... (AT) gmail (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.oracle.misc comp.databases.oracle.misc

Re: How to get a distinct count of result set of multople tablejoins? - 02-28-2008 , 04:16 PM

Re: How to get a distinct count of result set of multople tablejoins? - 03-11-2008 , 03:28 AM

Re: How to get a distinct count of result set of multople tablejoins? - 03-11-2008 , 03:28 AM

Re: How to get a distinct count of result set of multople tablejoins? - 03-11-2008 , 03:28 AM

Re: How to get a distinct count of result set of multople tablejoins? - 03-11-2008 , 03:28 AM