Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Query Optimization , avoid outer join

    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
    ------------------------------------------------
    AOLID
    1
    2
    3
    4

    INDIVIDUAL_TELEPHONE
    --------------------------------------------
    AOLID TYPECODE RANK INPUTPHONE
    1 'D' 01 111
    1 'N' 02 222
    1 'C' 04 234
    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 ..
    Last edited by himridul; 02-13-04 at 11:03.
    himridul

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Optimization , avoid outer join

    How about:

    PHP Code:
    select c.aol_id
    ,      max(DECODE(t.type_code,'D't.input_phone)) as dayphone
    ,      max(DECODE(t.type_code,'D't.rank)) as dayrank
    ,      max(DECODE(t.type_code,'N't.input_phone)) as nightphone
    ,      max(DECODE(t.type_code,'N't.rank)) as nightrank
    ,      max(DECODE(t.type_code,'C't.input_phone)) as csiphone
    ,      max(DECODE(t.type_code,'C't.rank)) as csirank
    from   customer c
    individual_telephone t
    where  c
    .aol_id t.aol_id (+)
    group by c.aol_id
    order by c
    .aol_id
    You can omit the outer join (and indeed, omit the customer table altogether) if you wantto exclude customers with no phones.

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    Andrewst , woh , It's mindblowing .
    I can't beleive this .....
    Ya , I remove the customer table from query and it's giving the minimal cost.

    Really , hat's off !!!
    himridul

Posting Permissions

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