SQL - slow response
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL - slow response

  1. #1
    Join Date
    Apr 2003
    Posts
    16

    SQL - slow response

    Hi,
    Need ur help
    I m using following query based on 3 table table
    mem has 2 million,
    act has 17 million and
    trx has 27 million records

    SELECT
    MEM.MEMHIP_NO,
    ACT.ACTIVITY_DATE,
    TRX.TRANSACTION_DATE,
    TRX.trx_fact,
    .....
    FROM
    MEM,
    ACT,
    TRX
    WHERE
    ( ACT.MEMBER_ACTIVITY_ID(+)=TRX.MEMBER_ACTIVITY_ID )
    AND ( MEM.MEMBER_ID=TRX.MEMBER_ID )
    AND ( TRX.USER_CREATED = ????)
    AND TRX.TRx_DATE BETWEEN '1-Jan-2005' AND '31-DEC-2005'
    AND
    .... some other conditions....
    )

    this query runs very very slow ..it takes 4-5 hrs to refresh data. all indexes and joins are proper. i m using normal as well as BITmap indexes....

    can anyone pls help me how to tune this query and suggetion to improve the performace. earlier i used hints also but in some case it might helpfull but not always...


    your early response would be appriciate.

    Regards,
    Avi.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    AND TRX.TRx_DATE BETWEEN '1-Jan-2005' AND '31-DEC-2005'

    Use to_Date fn. Compare always date col with date.

    Post tkprof output.

    Tamil

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