Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unanswered: Sql Performance delay -Order BY clause

    Hi all,
    Coul danyone help me with the performance of this sql query.
    Without the Order by clause runs faster and uses the index properly,
    No of records retrieved are around 500,000 RECORDS.
    Without the Order by i get the records in 10 seconds, with Order bY, its running for ever, though the explain plan shows that it is using the index.

    Is there anyway i can improve the performance of this query.
    I have also attached the explain plan.
    Without the SORT ORDER BY it works fatser. Its a must that i have the order by clause in my query

    I would very much appreacite your help
    Saratha

    select
    /*+ INDEX(EB_READING EB_READING_IDX_PREMNUM) */ eb_reading.premnum,eb_reading.readnum,eb_reading.r eaddate,eb_reading.prevdate,
    eb_reading.prevreadnum,eb_reading.e_invnum,eb_read ing.reversed
    from eb_reading,tdw_null_premnum
    where eb_reading.premnum = tdw_null_premnum.premnum
    and eb_reading.servicenum = tdw_null_premnum.servicenum
    and (eb_reading.e_invnum <> 0 or eb_reading.e_invnum is null)
    and eb_reading.reversed = 'Y'
    order by eb_reading.premnum,eb_reading.readnum

    SELECT STATEMENT, GOAL = CHOOSE 158210 4337746 307979966
    SORT ORDER BY 158210 4337746 307979966
    NESTED LOOPS 108645 4337746 307979966
    INDEX FAST FULL SCAN ENERGYDB TDW_NULL_PREMNUM_IDX1 3 5718 148668
    TABLE ACCESS BY GLOBAL INDEX ROWID ENERGYDB EB_READING 19 7586124 341375580
    INDEX RANGE SCAN ENERGYDB EB_READING_IDX_PREMNUM 2 7586124

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Sql Performance delay -Order BY clause

    Check your sort_area_size init.ora parameter. If it is too small the data will be paged out into the TEMP tablespaces for a disk sort, and disk sorts can be thousands of times slower than memory sorts.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Statement demonstrates poor SQL coding technique.
    If/when a table does NOT contribute a column to the SELECT clause,
    the table should NOT be used in the FROM clause.

    Try the following instead

    select eb_reading.premnum,
    eb_reading.readnum,
    eb_reading.readdate,
    eb_reading.prevdate,
    eb_reading.prevreadnum,
    eb_reading.e_invnum,
    eb_reading.reversed
    from eb_reading
    where eb_reading.reversed = 'Y'
    and (eb_reading.e_invnum <> 0 or eb_reading.e_invnum is null)
    and exists ( select '1'
    from tdw_null_premnum
    where eb_reading.premnum = tdw_null_premnum.premnum
    and eb_reading.servicenum = tdw_null_premnum.servicenum
    )
    order by eb_reading.premnum,eb_reading.readnum;

  4. #4
    Join Date
    Nov 2003
    Posts
    33

    Re: Sql Performance delay -Order BY clause

    Originally posted by carloa
    Check your sort_area_size init.ora parameter. If it is too small the data will be paged out into the TEMP tablespaces for a disk sort, and disk sorts can be thousands of times slower than memory sorts.
    Thanks Caloa,
    I have suggested that to our DBAs. I shall wait for their reply.

    Saratha

  5. #5
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by anacedent
    Statement demonstrates poor SQL coding technique.
    If/when a table does NOT contribute a column to the SELECT clause,
    the table should NOT be used in the FROM clause.

    Try the following instead

    select eb_reading.premnum,
    eb_reading.readnum,
    eb_reading.readdate,
    eb_reading.prevdate,
    eb_reading.prevreadnum,
    eb_reading.e_invnum,
    eb_reading.reversed
    from eb_reading
    where eb_reading.reversed = 'Y'
    and (eb_reading.e_invnum <> 0 or eb_reading.e_invnum is null)
    and exists ( select '1'
    from tdw_null_premnum
    where eb_reading.premnum = tdw_null_premnum.premnum
    and eb_reading.servicenum = tdw_null_premnum.servicenum
    )
    order by eb_reading.premnum,eb_reading.readnum;
    Thanks for your help
    I shall try this as well.
    Saratha

  6. #6
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by Saratha
    Thanks for your help
    I shall try this as well.
    Saratha
    Thank you so much for your help. This reduced the time so drastically. And the 250,000 records got updated in less than 10 minues.

    I just used your modified query to create a temp table without the order by clause. and then used the simple select from the temp table with the order by clause in my Cursor.

    This forum is so good. I am getting replies so quick, i wonder even if our local DBAs would respond this quick.

    I appreaciate your help, you all
    Thanks
    Saratha

Posting Permissions

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