performance issue with outer joins

[KaiserSose]

I have two large select statements in a insert statement (Select 1 UNION ALL Select 2) and they have outer joins in their where clauses. (can't remove them). First I tried to write functions and remove outer joins but got poor performance, then use "USE_MERGE", "MERGE_JOIN" hints, but it didn't make any difference in performance. I'm stuck!! anybody can help me??

Insert into TEMP_TABLE
Select (40 columns) from TABLE a, table b, table c, table d
....

where b.field1 (+) = a.field1
And c.field2 (+) = a.field2.

Here table a is the largest table others have at most 20 rows.

Thanks in advance...

[chrisrlong]

Sorry, but you're going to need to provide a whole lot more info than that...

- Actual statement(s)
- Actual Plan(s)
- Tables sizes
- Indexes
- etc.

- Chris

[KaiserSose]

this is the trace report:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 NESTED LOOPS (OUTER)
2 1 NESTED LOOPS (OUTER)
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'BILL_INVOICE_DET
AIL'
6 5 INDEX (RANGE SCAN) OF 'BILL_INVOICE_DETAIL_1' (U
NIQUE)

7 4 TABLE ACCESS (BY INDEX ROWID) OF 'PACKAGE_DEFINITI
ON_VALUES'

8 7 INDEX (UNIQUE SCAN) OF 'PACKAGE_DEFINITION_VALUE
S_1' (UNIQUE)

9 3 TABLE ACCESS (BY INDEX ROWID) OF 'COMPONENT_DEFINITI
ON_VALUES'

10 9 INDEX (UNIQUE SCAN) OF 'COMPONENT_DEFINITION_VALUE
S_1' (UNIQUE)

11 2 TABLE ACCESS (BY INDEX ROWID) OF 'DESCRIPTIONS'
12 11 INDEX (UNIQUE SCAN) OF 'DESCRIPTIONS_XDESCR_KEY' (UN
IQUE)

13 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_LINES'
14 13 INDEX (UNIQUE SCAN) OF 'PRODUCT_LINE_ID_PK' (UNIQUE)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
3766 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed


this is the select statement:
SELECT 100, 1, b.subscr_no, b.subscr_no_resets,
bill_invoice_row, billing_level, billing_category,
type_code, subtype_code, tracking_id, tracking_id_serv,
b.package_id, pd.display_value, b.component_id, c.display_value, provider_id, element_id,
b.product_line_id, b.description_code, p.product_group_id, d.description_text ,
prep_sequence, prorate_code, rate_type, trans_date,
from_date, dateadd('dd', -1,to_date), tracking_date,
rate_currency_code, amount, rated_amount, secondary_amount,
units, tax, federal_tax, state_tax, county_tax,
city_tax, other_tax, tax_rate, tax_pkg_inst_id,
tax_type_code, discount, discount_id, annotation,
geocode, rate_period, 1, b.open_item_id,
tracking_date, tracking_date
FROM BILL_INVOICE_DETAIL b,
PRODUCT_LINES p,
DESCRIPTIONS d,
COMPONENT_DEFINITION_VALUES c,
PACKAGE_DEFINITION_VALUES pd
WHERE index_bill_ref = 2080375476
AND index_bill_ref_resets = 0
AND billing_level = 0
and p.product_line_id (+) = b.product_line_id
AND b.description_code = d.description_code (+)
AND d.language_code (+) =1
AND c.language_code (+) =1
AND c.component_id (+) = b.component_id
AND pd.language_code (+) = 1
AND pd.package_id (+) = b.package_id

Here BILL_INVOICE_DETAIL table has as many as 2000000 rows.

[chrisrlong]

Hmmm...

No real help, but a few points of note:

- The optimizer is set to rule. If you want to play with hints and such, you need to be CBO. You should be CBO anyway.

- 20 consistent gets
0 physical reads
Looks pretty good to me. What is the problem?

- I see no union

- The plan looks pretty clean to me. You might want to try Hash joins, but if the logical reads is truly 20 - you are *not* going to get better.

- Chris