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