Re: Union

From: The Magnet <art_at_unsu.com>
Date: Mon, 12 Oct 2009 09:39:16 -0700 (PDT)
Message-ID: <8b1035bf-c5cf-4cca-a07d-6a28890a5722_at_f16g2000yqm.googlegroups.com>



On Oct 12, 11:32 am, joel garry <joel-ga..._at_home.com> wrote:
> On Oct 11, 2:27 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > On Oct 11, 2:40 pm, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
>
> > > On Oct 11, 2:16 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > snip
>
> > > > This is good.  My client uses the following query below.  The tables
> > > > referenced do not have any indexes, at least right now.  But all the
> > > > date fields are in the WHERE, which means at least 3 maybe 4 indexes
> > > > on the tables.  This may not matter since this is really only updated
> > > > in batch.  But, there are 4 full table scans.  Got to figure out how
> > > > to cut the time down from 1.56 seconds to something reasonable.
>
> > > > I'm not sure a materialized view would help, since it is only a COUNT
> > > > (*).  I'm still tracing this and playing with the plan.  I will
> > > > continue to play.  But, any thoughts?  Maybe indexes is the best way
> > > > since it is only in batch right now.
>
> > > > SELECT count(1) FROM (
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_rate_per_share dividend, announcement_date event_date,
> > > >          announcement_date announcement_date, div_expected_date
> > > > ex_date, record_date record_date,
> > > >          payment_date payment_date FROM
> > > > data_holder.expected_dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_rate_per_share dividend, div_expected_date event_date,
> > > >          announcement_date announcement_date, div_expected_date
> > > > ex_date, record_date record_date, payment_date
> > > >          payment_date
> > > >   FROM data_holder.expected_dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_rate_per_share dividend, record_date event_date,
> > > >          announcement_date announcement_date, div_expected_date
> > > > ex_date, record_date record_date,
> > > >          payment_date payment_date
> > > >   FROM data_holder.expected_dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_rate_per_share dividend, payment_date event_date,
> > > >          announcement_date announcement_date, div_expected_date
> > > > ex_date, record_date record_date,
> > > >          payment_date payment_date
> > > >   FROM data_holder.expected_dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_declared_or_paid dividend,
> > > >          div_announcement_date event_date, div_announcement_date
> > > > announcement_date, div_expected_date ex_date,
> > > >          div_record_date record_date, div_pay_date payment_date
> > > >   FROM data_holder.dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_declared_or_paid dividend,
> > > >         div_expected_date event_date, div_announcement_date
> > > > announcement_date, div_expected_date ex_date,
> > > >         div_record_date record_date, div_pay_date payment_date
> > > >   FROM data_holder.dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_declared_or_paid dividend, div_record_date event_date,
> > > >          div_announcement_date announcement_date, div_expected_date
> > > > ex_date, div_record_date record_date,
> > > >          div_pay_date payment_date
> > > >   FROM data_holder.dividends
> > > >   UNION
> > > >   SELECT DISTINCT(ticker) ticker, company_name company,
> > > > div_declared_or_paid dividend, div_pay_date event_date,
> > > >          div_announcement_date announcement_date, div_expected_date
> > > > ex_date, div_record_date record_date,
> > > >          div_pay_date payment_date
> > > >   FROM data_holder.dividends)
> > > > WHERE TO_CHAR(event_date,'YYYYMMDD') = UPPER('09012009');
>
> > > > --------------------------------------------------------------------------------------------
> > > > | Id  | Operation             | Name               | Rows  | Bytes |
> > > > Cost (%CPU)| Time     |
> > > > --------------------------------------------------------------------------------------------
> > > > |   0 | SELECT STATEMENT      |                    |     1 |     9 |
> > > > 2870   (3)| 00:00:35 |
> > > > |   1 |  SORT AGGREGATE       |                    |     1 |     9
> > > > |            |          |
> > > > |   2 |   VIEW                |                    |  8508 | 76572 |
> > > > 2870   (3)| 00:00:35 |
> > > > |   3 |    SORT UNIQUE        |                    |  8508 |   465K|
> > > > 2870  (98)| 00:00:35 |
> > > > |   4 |     UNION-ALL         |                    |       |
> > > > |            |          |
> > > > |*  5 |      TABLE ACCESS FULL| EXPECTED_DIVIDENDS |   308 | 17248
> > > > |    70   (3)| 00:00:01 |
> > > > |*  6 |      TABLE ACCESS FULL| EXPECTED_DIVIDENDS |   308 | 17248
> > > > |    70   (3)| 00:00:01 |
> > > > |*  7 |      TABLE ACCESS FULL| EXPECTED_DIVIDENDS |   308 | 17248
> > > > |    70   (3)| 00:00:01 |
> > > > |*  8 |      TABLE ACCESS FULL| EXPECTED_DIVIDENDS |   308 | 17248
> > > > |    70   (3)| 00:00:01 |
> > > > |*  9 |      TABLE ACCESS FULL| DIVIDENDS          |  1819 |    99K|
> > > > 645   (2)| 00:00:08 |
> > > > |* 10 |      TABLE ACCESS FULL| DIVIDENDS          |  1819 |    99K|
> > > > 645   (2)| 00:00:08 |
> > > > |* 11 |      TABLE ACCESS FULL| DIVIDENDS          |  1819 |    99K|
> > > > 646   (2)| 00:00:08 |
> > > > |* 12 |      TABLE ACCESS FULL| DIVIDENDS          |  1819 |    99K|
> > > > 646   (2)| 00:00:08 |
> > > > --------------------------------------------------------------------------------------------
>
> > > How about rewriting the query without all the UNIONs?
>
> > > What is it trying to do ( in words ... what is the business logic )?
>
> > Well, from what it looks like, he is trying to get some counts from 2
> > different tables, however, the criteria is such that the date at the
> > end will be a parameter passed in.  And, it is compared to multiple
> > named date columns within the 2 tables.  So, it looks as though he is
> > creating a query where he is giving the same alias to all the
> > necessary date columns and then comparing that in the outer query.
>
> > Not sure if I can do some type of analytical function here that may
> > help.  I do not think a view will help here either.  Tried adding
> > indexes to the compared date columns with no improvement either.
> > Still full table scans.  And at this time the tables are not even
> > large, 200,000 records, though that will grow over time.
>
> I think those DISTINCTs will bone you every time.
>
> jg
> --
> _at_home.com is bogus.
> Pacman like pie!http://blogs.oracle.com/security/images/SecurityDefects2009.png

Yeah, me too. Wonder if some type of function call would be better with different logic. Although a dynamic SELECT, I forget if that is reparsed each time. Received on Mon Oct 12 2009 - 11:39:16 CDT

Original text of this message