Re: Union

> 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 |
> --------------------------------------------------------------------------------------------

Is that the actual SQL? Seems a bit odd or I'm missing something

The outer query has

As there are no where conditions in the sub clauses, indexes will make no difference, they will all be full table scans because the outer where clause is only applied after all the sub-queries have been done. So, you could start by filtering at the sub-query level. In that case, an index could be used. However, as you also have a function on the date value, Oracle probably won't use the index anyway (unless a functional index is used?), so you would want to turn that around and compare your date column with an Oracle date object.

I think you really need to nail down exactly what the result requirements are and re-write the query from scratch. The one you are working with really seems like it was written in a very lazy manner. From a cursory glance, it looks like all that it is doing is counting the number of records from the identified tables that match a specific date after eliminating duplicates.

Depending on requirements and Oracle version, some of the analytical fuctions could be useful here.

-- 
tcross (at) rapttech dot com dot au