Re: tune between query/join
Date: Fri, 21 Oct 2011 10:42:15 -0700 (PDT)
Message-ID: <1319218935.53380.YahooMailNeo_at_web121003.mail.ne1.yahoo.com>
Here's a funny question...
Is there a third table you can use to join the two table with and stop the cartesian join? If you aren't aware of one, have you taken the time to inspect the tables and columns in the database to see if one exists? How often one can solve a cartesian problem by simply doing a quick search via dba_tab_columns and discovering objects that many who thought they knew all in the database, sadly, were not aware of... :)
I know, I always good for the silly questions...LOL
Kellyn Pot'Vin
Sr. Database Administrator and Developer
DBAKevlar.com
From: David Fitzjarrell <oratune_at_yahoo.com> To: "ax.mount_at_gmail.com" <ax.mount_at_gmail.com> Cc: Oracle-L Group <oracle-l_at_freelists.org> Sent: Friday, October 21, 2011 9:41 AM
Subject: Re: tune between query/join
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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2011 - 12:42:15 CDT