large table join problem

[navink123]

Hi,
I have 3 tables table1 a, table2 b and table3 c as follows

a.ssn
a.name1
a.name2
a.addr1
a.addr2
a.addr3
a.city
a.state
a.zip

b.somenumber

c.somecode

I have to pull the data from above 3 tables. I have to match the records based on the column 'ssn' which is present in all tables.

The 2nd condition will be for table c where c.somecode='0'

CREATE TABLE NEW_TABLE AS(SELECT a.ssn, a.name1, a.name2,a.addr1, a.addr2, a.addr3,
a.city, a.state, a.zip, b.somenumber from table1 a, table2 b, table3 c where
(a.ssn=b.ssn) and c.somecode=0
);

The problem is, each of these table have 80-90 million rows each. And my queries are taking a long time to run.

Can somebody please help me with a more efficient query.

Thanks,
N

 

[carp]

They're taking a long time to run because you have a cartesian join! You have not joined table3 to table1 or table2.

 

[navink123]

Can you please tell me how I can join table 3 to table 1.
In table c, I have to pull out the records which match with the ssn in table a and filter only those which has the somecode coulmn as '0'.

 

[carp]

If table3 has a ssn column, then your query would be something like:

CREATE TABLE NEW_TABLE AS(SELECT a.ssn, a.name1, a.name2,a.addr1, a.addr2, a.addr3,
a.city, a.state, a.zip, b.somenumber from table1 a, table2 b, table3 c
where
a.ssn=b.ssn
AND c.ssn = a.ssn
and c.somecode=0
);

Given the size of your tables, this may still take a while (especially if you don't have indexes on all ssn columns and the somecode column in table3).

 

[dbtoo2001]

What is the percentage of records that have c.somecode=0? If it is small, then you can change the order of the tables and provide a hint to oracle. Order the tables in the smallest number of rows expected to be returned:

CREATE TABLE NEW_TABLE AS(SELECT /*+ ORDERED */ a.ssn, a.name1, a.name2,a.addr1, a.addr2, a.addr3,
a.city, a.state, a.zip, b.somenumber
from table3 c, table1 a, table2 b
where
a.ssn=b.ssn
AND c.ssn = a.ssn
and c.somecode=0
);


This assumes that a has fewer records than b. If not, then reverse those two in the from clause
(i.e. from tale3 c, table2 b, table1 a.)

 

[carp]

If you are using Cost-Based optimizer (CBO)and have analyzed the tables, the optimizer will do this for you. Also, if you are using CBO but haven't analyzed the tables recently, you might want to reanalyze them.