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

    Unanswered: How to optimize this Query

    nFromID
    Last edited by himridul; 03-24-04 at 01:05.
    himridul

  2. #2
    Join Date
    Jan 2004
    Posts
    84
    Instead of Normal Index , create bit map index and see the difference!
    I am sure it will help.

  3. #3
    Join Date
    Jan 2004
    Posts
    84
    I mean bit map indexes on columns used for AND condition.

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: How to optimize this Query

    try avoiding outer join, if its possible.


    Originally posted by himridul
    Hi ALL,

    SELECT /* USE_HASH(A,B,C,D) PARALLEL(A,16) PARALLEL(B,16) PARALLEL(C,16) PARALLEL(D,16) */
    A.INDIVIDUALID,
    C.BESTCONTACTADDRESSID,
    B.ACCOUNTID,
    B.SERVICEID,
    D.ADDRESSID
    FROM
    CUSTOMER A,
    CUSTOMERACCOUNT B,
    INDIVIDUAL C,
    ADDRESS D
    WHERE
    A.INDIVIDUALID=B.INDIVIDUALID(+)
    AND NVL(B.REGISTRATIONDATE,to_date('12312099','mmddyyy y')) =
    (SELECT /* PARALLEL(E,16) */ nvl(MAX(REGISTRATIONDATE),to_date('12312099','mmdd yyyy'))
    FROM
    CUSTOMERACCOUNT E
    WHERE
    B.INDIVIDUALID=E.INDIVIDUALID
    )
    AND A.INDIVIDUALID=C.INDIVIDUALID
    AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)

    The query is working fine for low volume of data . But for high volume of data it's taking too much time.
    The data volume might be in range of 100-200 millions of data . The tables are properly indexed . I think
    the joining of 4 master tables should be avoided to optimize the query .
    If anyone has any idea , plz help me out.
    Thanks in advance ....
    Oracle can do wonders !

  5. #5
    Join Date
    Jan 2004
    Posts
    66
    Outer join is needed in the query .
    Is View will be helpful in this case?
    himridul

  6. #6
    Join Date
    Jan 2004
    Posts
    66
    preetikate , Bitmap index will not be useful in my case. Because it helps when the fields have less distinct values.But in my case , all the fields mentioned in the where clause have greater distinct values , may be millions . So I don't think that bitmap index will help me.

    Anyway thank u .....
    himridul

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Try performance with the following 2 approaches..

    I've checked neither the syntax nor the explain-plan. so not sure if these help and to what extent....Try them out!!!


    Code:
    ------Approach 1------------------------------
    SELECT 
        A.INDIVIDUALID,
        C.BESTCONTACTADDRESSID,
        B.ACCOUNTID,
        B.SERVICEID,
        D.ADDRESSID
    FROM 
        CUSTOMER A,
        CUSTOMERACCOUNT B,
        INDIVIDUAL C,
        ADDRESS D,
        (SELECT INDIVIDUALID, max(REGISTRATIONDATE) REGISTRATIONDATE from CUSTOMERACCOUNT) E
    WHERE
        A.INDIVIDUALID=B.INDIVIDUALID(+)
        AND (B.INDIVIDUALID = E.INDIVIDUALID AND B.REGISTRATIONDATE = E.REGISTRATIONDATE)
        AND A.INDIVIDUALID=C.INDIVIDUALID
        AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)
    
    ------Approach 2------------------------------
    
    SELECT 
        A.INDIVIDUALID,
        C.BESTCONTACTADDRESSID,
        B.ACCOUNTID,
        B.SERVICEID,
        D.ADDRESSID
    FROM 
        CUSTOMER A,
        CUSTOMERACCOUNT B,
        INDIVIDUAL C,
        ADDRESS D,
        (SELECT INDIVIDUALID, max(REGISTRATIONDATE) REGISTRATIONDATE from CUSTOMERACCOUNT) E
    WHERE
        A.INDIVIDUALID=B.INDIVIDUALID
        AND (B.INDIVIDUALID = E.INDIVIDUALID AND B.REGISTRATIONDATE = E.REGISTRATIONDATE)
        AND A.INDIVIDUALID=C.INDIVIDUALID
        AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)
    UNION
    SELECT 
        A.INDIVIDUALID,
        C.BESTCONTACTADDRESSID,
        NULL,
        NULL,
        D.ADDRESSID
    FROM 
        CUSTOMER A,
        INDIVIDUAL C,
        ADDRESS D
    WHERE
        A.INDIVIDUALID in (SELECT INDIVIDUALID from CUSTOMER MINUS SELECT INDIVIDUALID from CUSTOMERACCOUNT)
        AND A.INDIVIDUALID=C.INDIVIDUALID
        AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)
    Oracle can do wonders !

  8. #8
    Join Date
    Jan 2004
    Posts
    66
    The first one is really helpful . I did some modification in the code. The cost is really low in case of first one.But the second one is really horrible.

    Thanks cmasharma , you are really genious .

    The modified code:

    SELECT
    A.INDIVIDUALID,
    C.BESTCONTACTADDRESSID,
    B.ACCOUNTID,
    B.SERVICEID,
    D.ADDRESSID
    FROM
    CUSTOMER A,
    CUSTOMERACCOUNT B,
    INDIVIDUAL C,
    ADDRESS D,
    (SELECT INDIVIDUALID, max(REGISTRATIONDATE) REGISTRATIONDATE
    from CUSTOMERACCOUNT group by INDIVIDUALID ) E
    WHERE
    A.INDIVIDUALID=B.INDIVIDUALID(+)
    AND (A.INDIVIDUALID = E.INDIVIDUALID(+)
    AND NVL(B.REGISTRATIONDATE,to_date('12312099','mmddyyy y')) = NVL(E.REGISTRATIONDATE,to_date('12312099','mmddyyy y')))
    AND A.INDIVIDUALID=C.INDIVIDUALID
    AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)
    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
  •