Re: tune between query/join

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 21 Oct 2011 16:32:56 +0100
Message-ID: <CABe10sYwob3CpCy29aeWsQtkacdzFE-EvvFuzB0u=WmLKPjujQ_at_mail.gmail.com>



What in words then is the goal of the select? You seem to be asking for every row in table a returned once for every row in b that has an amount in the range of values that there are for amounts in a. That's assuming that lowest_amount and highest_amount aren't actually columns in table a.

So if you had table A

 ID        Account_Number      AMOUNT
1           200a                        10
2           300a                        1000

and table B

NAME                  AMOUNT
Mr Jones             20
Mr Smith             2000

Captain America 200

Then the results you seem to be asking for are

1,200a,Mr Jones,20
1,200a,Captain America,200
2,300a,Mr Jones,20
2,300a,Captain America,200

I'm struggling to see what this select gives you?

On Fri, Oct 21, 2011 at 3:09 PM, amonte <ax.mount_at_gmail.com> wrote:

> 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
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 21 2011 - 10:32:56 CDT

Original text of this message