Re: Union

From: John Hurley <johnbhurley_at_sbcglobal.net>
Date: Sun, 11 Oct 2009 12:40:34 -0700 (PDT)
Message-ID: <5780a164-6f5f-435f-a664-cd0c562c6342_at_z24g2000yqb.googlegroups.com>



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 )? Received on Sun Oct 11 2009 - 14:40:34 CDT

Original text of this message