Re: Union
From: The Magnet <art_at_unsu.com>
Date: Sun, 11 Oct 2009 14:27:47 -0700 (PDT)
Message-ID: <4a396f9b-7859-4cff-886c-11928a1b8ba6_at_j4g2000yqa.googlegroups.com>
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 )?
Date: Sun, 11 Oct 2009 14:27:47 -0700 (PDT)
Message-ID: <4a396f9b-7859-4cff-886c-11928a1b8ba6_at_j4g2000yqa.googlegroups.com>
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. Received on Sun Oct 11 2009 - 16:27:47 CDT