Re: Union
From: The Magnet <art_at_unsu.com>
Date: Mon, 12 Oct 2009 10:53:23 -0700 (PDT)
Message-ID: <457fe801-ea40-4f54-a697-d67a79a9d252_at_j39g2000yqh.googlegroups.com>
On Oct 12, 11:59 am, joel garry <joel-ga..._at_home.com> wrote:
> On Oct 12, 9:39 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > 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.
>
> They tend to kill you with hard parsing.
>
> jg
> --
> _at_home.com is bogus.http://www.privateline.com/TelephoneHistory5/switchboardmemories.htm
Date: Mon, 12 Oct 2009 10:53:23 -0700 (PDT)
Message-ID: <457fe801-ea40-4f54-a697-d67a79a9d252_at_j39g2000yqh.googlegroups.com>
On Oct 12, 11:59 am, joel garry <joel-ga..._at_home.com> wrote:
> On Oct 12, 9:39 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > 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.
>
> They tend to kill you with hard parsing.
>
> jg
> --
> _at_home.com is bogus.http://www.privateline.com/TelephoneHistory5/switchboardmemories.htm
Well, guess it will take some playing around. Maybe with some analytical function or something. We'll see. Received on Mon Oct 12 2009 - 12:53:23 CDT