tune a query with left outer join

HI,

I have a query
select a.*,b.* from a left outer join b on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and a.col4=b.col4

This query takes around 2 hours...

Table a has around 50 million records and table b has 200000...I have indexes on col1,col2,col3,col4 on both the tables....

Is there anyway to improve the run time?....

Thanks

Sam

Run an EXPLAIN and post it's result here.
Also post the table structure.

Thanks for your response..Here is the explain plan...I have a index on table DEPARTMENT for the columns in the join condition. I also have an index on table DEPARTMENT but it has one index on extra columns as well...Doe it make any difference?...

Please suggest...


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 47 M 68141
HASH JOIN OUTER 47 M 6G 68141
TABLE ACCESS FULL COMPANY 47 M 3G 16638
TABLE ACCESS FULL DEPARTMENT 183 K 13 M 66

Can you post the exact query and table structure?

Thank You

SELECT COMPANY.col1, COMPANY.col2, COMPANY.col3, COMPANY.col4, COMPANY.col5, COMPANY.col6, COMPANY.col7, COMPANY.col8, COMPANY.col9, COMPANY.col10, COMPANY.col11, COMPANY.col12, COMPANY.col13, COMPANY.col14, COMPANY.col15, COMPANY.col16, COMPANY.col17, COMPANY.col18, COMPANY.col19, COMPANY.col20, COMPANY.col21, DEPARTMENT.col1, DEPARTMENT.col2, DEPARTMENT.col3, DEPARTMENT.col4, DEPARTMENT.col5, DEPARTMENT.col6, DEPARTMENT.col7, DEPARTMENT.col8, DEPARTMENT.col9, DEPARTMENT.col10, DEPARTMENT.col11, DEPARTMENT.col12
FROM
COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.col2 = DEPARTMENT.col1 AND
COMPANY.col3 = DEPARTMENT.col2 AND
COMPANY.col4 = DEPARTMENT.col3 AND
COMPANY.col14 = DEPARTMENT.col6


CREATE TABLE COMPANY
(
col1 VARCHAR2(10 BYTE),
col2 VARCHAR2(10 BYTE),
col3 VARCHAR2(6 BYTE),
col4 VARCHAR2(2 BYTE),
col5 VARCHAR2(8 BYTE),
col6 VARCHAR2(2 BYTE),
col7 VARCHAR2(2 BYTE),
col8 VARCHAR2(2 BYTE),
col9 VARCHAR2(2 BYTE),
col10 VARCHAR2(1 BYTE),
col11 VARCHAR2(1 BYTE),
col12 VARCHAR2(34 BYTE),
col13 VARCHAR2(1 BYTE),
col14 VARCHAR2(4 BYTE),
col15 VARCHAR2(10 BYTE),
col16 VARCHAR2(1 BYTE),
col17 VARCHAR2(3 BYTE),
col18 VARCHAR2(1 BYTE),
col19 VARCHAR2(3 BYTE),
col20 VARCHAR2(6 BYTE),
col21 VARCHAR2(10 BYTE)
)

CREATE UNIQUE INDEX COMPANY_KEY ON COMPANY
(col2, col3, col4, col14, col1,
col15)

CREATE TABLE DEPARTMENT
(
col1 VARCHAR2(30 BYTE),
col2 VARCHAR2(10 BYTE),
col3 VARCHAR2(6 BYTE),
col4 VARCHAR2(8 BYTE),
col5 VARCHAR2(30 BYTE),
col6 VARCHAR2(4 BYTE),
col7 VARCHAR2(3 BYTE),
col8 VARCHAR2(6 BYTE),
col9 VARCHAR2(2 BYTE),
col10 VARCHAR2(2 BYTE),
col11 VARCHAR2(8 BYTE),
col12 VARCHAR2(20 BYTE)
)

CREATE UNIQUE INDEX DEPT_KEy ON DEPARTMENT
(col1, col2, col3, col6)

don't you have any filters?
what do you to with a result set of 50 mio rows?

additionally, do you really need all columns returned?
if you return more columns, than the index has, there has to be a table access to get this data (either a fill table access or a table access by rowid).

i recommend, that you try filters (e.g if you show the data in an application, only query the first 50 data sets).