Re: Union
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