performance issue with outer joins
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: performance issue with outer joins

  1. #1
    Join Date
    Jul 2001
    Posts
    2

    Question

    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...


  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Jul 2001
    Posts
    2
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width