Re: tune between query/join

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 21 Oct 2011 08:41:03 -0700 (PDT)
Message-ID: <1319211663.7118.YahooMailNeo_at_web65410.mail.ac4.yahoo.com>

How can you even hope to relate what's in tmp_account with the data in account and make any sense of it?  I  hate to say this but if this is  how  he models processes you are much better off with him gone.  It appears  you need someone who can actually create a usable  process model and schema and start this process over.  We are in the midst of a major data  migration where I am employed but our process model makes sense and correctly maps data from one system to another because the proper relationships exist.

I can't see how you can undertake this migration when the schema is absent the necessary relations.

David Fitzjarrell

From: amonte <ax.mount_at_gmail.com>
To: David Fitzjarrell <oratune_at_yahoo.com> Cc: Oracle-L Group <oracle-l_at_freelists.org> Sent: Friday, October 21, 2011 7:09 AM
Subject: Re: tune between query/join

unfortunatey there arent any common columns, this is a pity really this is a data migration process and the guy who modelled this left the company :-(

and yes, it reads million of rows even the entire tables are cached, I dont think this would work even with exadata

let me get some test data...

2011/10/21 David Fitzjarrell <oratune_at_yahoo.com>

> That would be a merge join cartesian, not what you want, really as it
> returs 1,200,000,000,000 rows with at most 8,000,000 rows of actually
> useful results (the result set should be somewhere between 1 and 8,000,000
> depending upon how many rows in account match rows in tmp_account).  There
> must be some common column between the two tables to effect a proper join.
> If not then this query is useless and is simply consuming resources better
> used for more productive queries.
>
> Please provide create table statements for both tables and some sample
> data.
>
> David Fitzjarrell
>
>
>  *From:* amonte <ax.mount_at_gmail.com>
> *To:* Oracle-L Group <oracle-l_at_freelists.org>
> *Sent:* Thursday, October 20, 2011 10:41 PM
> *Subject:* tune between query/join
>
> Hello everyone
> I am running a query which looks like following (running in 10.2.0.4):
>
> select a.account_number, a.id, b.*
>  from account a, tmp_account b
> where b.amount between a.lowest_amount and a.highest_amount
>
> account has 8000000 rows and tmp_account 150000, the execution plan shows
> merge sort join as expected, this query takes around 5 hours to run, is
> there anyway to improve this?
>
>
> Alex
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 21 2011 - 10:41:03 CDT