Re: Union
From: joel garry <joel-garry_at_home.com>
Date: Mon, 12 Oct 2009 09:32:13 -0700 (PDT)
Message-ID: <f76af856-a686-49b9-911f-018066151052_at_g1g2000pra.googlegroups.com>
On Oct 11, 2:27 pm, The Magnet <a..._at_unsu.com> wrote:
> 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.
Date: Mon, 12 Oct 2009 09:32:13 -0700 (PDT)
Message-ID: <f76af856-a686-49b9-911f-018066151052_at_g1g2000pra.googlegroups.com>
On Oct 11, 2:27 pm, The Magnet <a..._at_unsu.com> wrote:
> 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.
I think those DISTINCTs will bone you every time.
jg
-- _at_home.com is bogus. Pacman like pie! http://blogs.oracle.com/security/images/SecurityDefects2009.pngReceived on Mon Oct 12 2009 - 11:32:13 CDT
