Re: Union
From: The Magnet <art_at_unsu.com>
Date: Mon, 12 Oct 2009 09:39:16 -0700 (PDT)
Message-ID: <8b1035bf-c5cf-4cca-a07d-6a28890a5722_at_f16g2000yqm.googlegroups.com>
On Oct 12, 11:32 am, joel garry <joel-ga..._at_home.com> wrote:
> 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.png
Date: Mon, 12 Oct 2009 09:39:16 -0700 (PDT)
Message-ID: <8b1035bf-c5cf-4cca-a07d-6a28890a5722_at_f16g2000yqm.googlegroups.com>
On Oct 12, 11:32 am, joel garry <joel-ga..._at_home.com> wrote:
> 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.png
Yeah, me too. Wonder if some type of function call would be better with different logic. Although a dynamic SELECT, I forget if that is reparsed each time. Received on Mon Oct 12 2009 - 11:39:16 CDT