Query Optimization , avoid outer join

[himridul]

Hi,

I've one table called customer and one table , i.e, individual_telephone.
Now the data set of these table would be like this :

CUSTOMER INDIVIDUAL_TELEPHONE
------------- --------------------------------------------
AOLID AOLID TYPECODE RANK INPUTPHONE
1 1 'D' 01 111
2 1 'N' 02 222
3 1 'C' 04 234
4 2 'C' 03 345
3 'D' 02 456
8 'D' 03 767

Now the output result will be like this :

aolid dayphone dayrank nightphone nightrank csiphone csirank
---------------------------------------------------------------------------------------
1 111 01 222 02 234 04
2 null null null null 345 03
3 456 02 null null null null


In INDIVIDUAL_TELEPHONE table , one AOLID can have at most 3 records with typecode(C,D,N).
In this table (comination of AOLID and TYPECODE) is the primary key.
INPUTPHONE and AOLID and TYPECODE are NOT NULL fields.

INDIVIDUAL_TELEPHONE table has 600 millions of data.
CUSTOMER table has 200 millions of data.

Following is the my query , it's working fine , but the COST is very high.

SELECT
cust.aolid aolid
,ind_teld.inputphone dayphone
,ind_teld.rank dayrank
,ind_teln.inputphone nightphone
,ind_teln.rank nightrank
,ind_telc.inputphone csiphone
,ind_telc.erank csirank
FROM
customer cust
,(SELECT * FROM individual_telephone WHERE typecode='D') ind_teld
,(SELECT * FROM individual_telephone WHERE ypecode='N') ind_teln
,(SELECT * FROM individual_telephone WHERE typecode='C') ind_telc
WHERE
cust.aolid = ind_teld.aolid(+)
AND cust.aolid = ind_teln.aolid(+)
AND cust.aolid = ind_telc.aolid(+)
AND (
ind_teld.INPUTPHONE IS NOT NULL
OR ind_telC.INPUTPHONE IS NOT NULL
OR ind_telN.INPUTPHONE IS NOT NULL
)


Is there any better approach to solve this?
I need to avoid the outer join , for this it's taking too much cost and time.

Please help me to optimize the query , any suggestions will be highly appreciated.

One more thing , I've taken care of direct parallel select with nologiing option.
The tables are partitioned properly.
INDIVIDUAL_TELEPHONE table has BITMAP INDEX on typecode.
Don't worry about the indexing and other things .

Thanks ..