Inner Join on Sorted Columns

[Nobody]

Does an inner join on two columns ALWAYS match one record against the entire other table?



I have two temporary tables I am putting an INNER JOIN on two columns (both columns are numeric values, but column1 is stored as text). In TABLE1, these two columns identify unique combinations (i.e. could be used as primary key) but in TABLE2 these two columns are not unique. I am performing an inner join and pulling back various other columns from the two tables. I want to know if sorting each temporary table prior to the inner join will speed up the processing, or slow it down.

In other words, will the server acknowledge the fact that the tables have been sorted so it will take each unique two column combination from TABLE1 and only search TABLE2 until it has found the matching records (or it sees it has reached numbers that exceeds itself) and iterate to the next record from TABLE1?

Thanks!

[weekend00]

if you have really big tables, create index on the two columns on both table.

[Nobody]

if you have really big tables, create index on the two columns on both table.

Can you make an index on a temporary table? And would that be worth it, if you could?

I think that is the correct term, these tables are both being created from a master table (millions of records) and only used inside the query.
e.g.

Select columns
From (SELECT columns FROM mastertable WHERE criteria) AS t2 INNER JOIN (SELECT columns FROM mastertable WHERE criteria GROUP BY fields HAVING criteria) AS t1 ON (t1.column1=t2.column1) AND (t1.column2=t2.column2)
WHERE criteria
GROUP BY fields
HAVING criteria
ORDER BY fields

my question, is if some extra ORDER BY's would help, by sorting the temporary tables before looking for the join

Select columns
From (SELECT columns FROM mastertable WHERE criteria ORDER BY column1, column2) AS t2 INNER JOIN (SELECT columns FROM mastertable WHERE criteria GROUP BY fields HAVING criteria ORDER BY column1, column2) AS t1 ON (t1.column1=t2.column1) AND (t1.column2=t2.column2)
WHERE criteria
GROUP BY fields
HAVING criteria
ORDER BY fields

I know I can run this query on my own to test, but I was wondering if this sort of logic as able to be performed by the server or not. My case may make it not work because the column1 field is text, or because I'm using multiple columns, etc.

Sorry if this is confusing, I'm struggling to get everything straight.

So the question is, should I bother to sort the temporary tables, or would there be no point? (since the INNER JOIN will always attempt to match each record from t1 against the entire table of t2, so sorting t1 and t2 is a waste)

[weekend00]

don't use ORDER BY, that will make it more slow.
compare the efficiency between creating index and no index, then pick the better solution.

[Nobody]

I am a SQL novice, just enough to follow along and tweak. This is my most complicated query evar!

How do you create an index on a temporary table? (to try this) If it matters, this query is going from Access to an Oracle server via ODBC.

[weekend00]

did you try not to use sub query, just use the master table?

[Nobody]

did you try not to use sub query, just use the master table?

haha, no, I wish. The data is such that the master table doesn't easily part with my info... I only recently discovered that the query we had been using was wrong.

Let me try to explain my situation generally:
column1 is the account number for the customer
column2 being method of payment for the account number

I'm interested in the total amount paid for in each account number by method of payment, and if a single account uses the same method of payment over time (column3 tracks when the purchases are made). The results I want are to track the payments over time for those accounts-payment methods which have exceeded a certain cumulative payment threshold.

So my query has temporary table t1 as a simple (almost instantaneous, utilizing primary-key-index) query which grabs only the necessary fields and filters the master table, and INNER JOINs this to those [accounts]-[method of payment] combos which have reached my threshold (hence the GROUP BY and HAVING)

The actual query is a little more complicated, as the individual accounts are grouped into regions and I'm only concerned with when and how many accounts-method combos in a certain region meet the threshold. And to spice things up sometimes they customers return things and oscillate above and below the threshold.

Thanks for helping!