Union
Date: Sun, 11 Oct 2009 11:16:53 -0700 (PDT)
Message-ID: <04b5beb2-996d-468d-94fa-c71f0ce1b8e0_at_a32g2000yqm.googlegroups.com>
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 | 17248646 (2)| 00:00:08 |
| 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|
Received on Sun Oct 11 2009 - 13:16:53 CDT