Re: tune between query/join
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 20 Oct 2011 22:59:42 -0700 (PDT)
Message-ID: <1319176782.36251.YahooMailNeo_at_web65414.mail.ac4.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
Date: Thu, 20 Oct 2011 22:59:42 -0700 (PDT)
Message-ID: <1319176782.36251.YahooMailNeo_at_web65414.mail.ac4.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
Received on Fri Oct 21 2011 - 00:59:42 CDT