This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 14, 2013 9:34 AM by 899722 RSS

SQL Query : Order By issue with HUGE Table

899722 Newbie
Currently Being Moderated
Hello friends,
I have been through a terrible issue with order by. I would appreciate your help. Please let me know, your input for my case:

=> if i run select query it returns result quick in some milliseconds. (sql dev. fetches 50 rows at a time)
=> if i run select query with where condition and column (say A) in where condition is even indexed and i have order by and that order by column (say B) is also indexed.

Now, here is the issue:

1. if no. of rows with that where condition can filter yielding small result set then order by works fine .. 1-5 sec which is good.
2.*if no. of rows with that where condition can filter yielding Large result set, say more than 50,000 then with order by then the wait time is exponential.... i have even waited 10+ mins to get the result back for 120,000 records.*

Is order by takes that long for 100K records ... i think something else if wrong... your pointer will really be helpful... i am very new to sql and even newer for large table case.


I am using SQL Developer Version 2.1.1.64
and Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Thank you so much.

Edited by: 896719 on Jan 11, 2013 8:38 AM
  • 1. Re: SQL Query : Order By issue with HUGE Table
    sb92075 Guru
    Currently Being Moderated
    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 2. Re: SQL Query : Order By issue with HUGE Table
    6363 Guru
    Currently Being Moderated
    896719 wrote:
    Hello friends,
    I have been through a terrible issue with order by. I would appreciate your help. Please let me know, your input for my case:

    => if i run select query it returns result quick in some milliseconds. (sql dev. fetches 50 rows at a time)
    It isn't quick, you haven't finished running the query.

    You cannot say query returns first 50 rows out of 120,000 in milliseconds and say the query runs in milliseconds. It doesn't. Put your cursor in the data grid of SQL developer and fetch the last row. How long does that take?

    Because that is what needs to happen before you can order the results.

    http://jonathanlewis.wordpress.com/2010/08/29/fair-comparison/
  • 3. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    I have looked into these already and i have explain plan... and explain plan shows high cost 6 digits cost when rows return are more and decreases to 2 digits cost when rows are in below 10,000
  • 4. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    mistakenly i hit answered
  • 5. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    Thank you for the response but how do i do that.. can you please let me know do i will do that : Put your cursor in the data grid of SQL developer and fetch the last row.

    If i keep on fetching 50 rows at a time then it will take me hours may be to fetch all the rows ... i have more than millions records :(

    Edited by: 896719 on Jan 11, 2013 8:53 AM
  • 6. Re: SQL Query : Order By issue with HUGE Table
    EdStevens Guru
    Currently Being Moderated
    896719 wrote:
    I have looked into these already and i have explain plan... and explain plan shows high cost 6 digits cost when rows return are more and decreases to 2 digits cost when rows are in below 10,000
    Does the actual access plan change? I'd expect that with a very selective WHERE condition on an indexed column it might be using an index, whereas with less selectivity it will go for a FTS.

    But as 3360 said, with an unordered set, it can return a buffer full of the the first 'n' (random) rows to the client while it continues to get the remaining rows, whereas with an ORDERED set, it has to get every last row AND sort them before it can return anything to the client.
  • 7. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    Yes the plan changes ... that's what making me more confuse even....

    also How can i find how long it will take to query all the rows at once... since scrolling will not help me because i have more than millions of records....

    Edited by: 896719 on Jan 11, 2013 9:29 AM
  • 8. Re: SQL Query : Order By issue with HUGE Table
    rp0428 Guru
    Currently Being Moderated
    >
    How can i find how long it will take to query all the rows at once... since scrolling will not help me because i have more than millions of records....
    >
    Why would you try to retrieve millions of rows in a sql developer data grid? That can't possibly be useful.

    Why don't you tell us what you are REALLY trying to do. Are you trying to use sql developer to export the data and you want it in sorted order?
  • 9. Re: SQL Query : Order By issue with HUGE Table
    EdStevens Guru
    Currently Being Moderated
    896719 wrote:
    Yes the plan changes ... that's what making me more confuse even....

    also How can i find how long it will take to query all the rows at once... since scrolling will not help me because i have more than millions of records....

    Edited by: 896719 on Jan 11, 2013 9:29 AM
    "How long it will take" depends on more variables than you can account for.
    Who wants a report with "millions of records"? I certainly would not want to look at such a report. Just how practical is such a report? ... maybe this is where you need to step back and ask what the business requirement is.
  • 10. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    Yes you are correct, but my concentration was on order by thing, so it will do full scan of table so i was putting that ... and was also wondering if millions of record in table should not be a issue...???

    Any way for the explain plan , when just a value in the where changes there is the huge difference i want to point out too as below:
    SELECT 
       *
    FROM
        EES_EVT EES_EVT  where APLC_EVT_CD= 'ABC' ORDER BY  CRE_DTTM DESC 
    
    execution time : 0.047 sec 
    
    Plan hash value: 290548126
     
    --------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |    27 | 14688 |    25   (4)| 00:00:01 |
    |   1 |  SORT ORDER BY               |             |    27 | 14688 |    25   (4)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EES_EVT     |    27 | 14688 |    24   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | XIE1EES_EVT |    27 |       |     4   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - access("APLC_EVT_CD"='ABC')
     
    Note
    -----
       - SQL plan baseline "SYS_SQL_PLAN_6d41e6b91925c463" used for this statement
    
    
    
    =============================================================================================
    
    
    
    
    SELECT 
       *
    FROM
        EES_EVT EES_EVT  where APLC_EVT_CD= 'XYZ' ORDER BY  CRE_DTTM DESC 
    
    execution : 898.672 sec. 
    
    
    
    Plan hash value: 290548126
     
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |   121K|    62M|       |   102K  (1)| 00:11:02 |
    |   1 |  SORT ORDER BY               |             |   121K|    62M|    72M|   102K  (1)| 00:11:02 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EES_EVT     |   121K|    62M|       | 88028   (1)| 00:09:27 |
    |*  3 |    INDEX RANGE SCAN          | XIE1EES_EVT |   121K|       |       |   689   (1)| 00:00:05 |
    ----------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - access("APLC_EVT_CD"='XYZ')
     
    Note
    -----
       - SQL plan baseline "SYS_SQL_PLAN_ef5709641925c463" used for this statement
    Also Note this table contains 74328 MB data in it.

    Thanks
  • 11. Re: SQL Query : Order By issue with HUGE Table
    Martin Preiss Expert
    Currently Being Moderated
    your queries are using plan baselines that pin the plan to an index scan on XIE1EES_EVT, a following table access on EES_EVT and then a SORT ORDER BY. The CBO thinks there are only 27 rows for the first query and 121K rows for the second one: so it's not surprising that the second query is slower. Perhaps the (baseline enforced) plan for the second query is not fitting.

    Regards

    Martin

    Edited by: mpreiss on Jan 11, 2013 7:27 PM
  • 12. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    so is 1 min wait time ok for this explain plan ... what do you say ?

    P.S: when resultset gets more bigger it's getter more worst .... i even had wait time for 8 min in some cases with same sql query but different value for APLC_EVT_CD = ".... something else..." .

    what can i do to improve the speed...

    Thanks
  • 13. Re: SQL Query : Order By issue with HUGE Table
    Martin Preiss Expert
    Currently Being Moderated
    the "time" in the execution plan is the duration the optimizer expects: if the object statistics and the system statistics are accurate then the time value may be quite exact. In your plans at least there is a fitting relation beween cost, work and the time the execution takes - so it seems the statistics are at least not plain wrong.

    There are two issues:
    1. your query has to read a lot of data via the index. Only when the read is finished the sort can take place. For a small result the index access is a good idea and the sorting is cheap. But with lots of rows in your result both steps get more and more expansive. But if your table indeed has a size of 74328 MB then I suppose a full table scan will also take some time.

    If you only want to fetch a small number of rows then you could create an index to support the condition and the sorting on (APLC_EVT_CD, CRE_DTTM DESC). With this index the sort could be avoided and the first rows could be fetched almost imediately. But the fetching of the whole dataset would still need a lot of time.

    2. the optimizer used a sql baseline for the query. So even if you add an additional index (an operation that would also consume resources and take some time) the CBO would not use it as long as the baseline is pinning the plan.

    But like some others I don't understand why you have to fetch millions or rows in a sorted order in the sql developer.

    Regards

    Martin
  • 14. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    But if your table indeed has a size of 74328 MB then I suppose a full table scan will also take some time. 
    How long can we expect the wait time normally if this is the case ..
    If you only want to fetch a small number of rows then you could create an index to support the condition and 
    the sorting on (APLC_EVT_CD, CRE_DTTM DESC). With this index the sort could be avoided and the first rows 
    could be fetched almost imediately.
    I already have indexes on those columns and yes it works little good when u have same column in where condition
    and in order by clause. Also condition is result set should not be large.


    But like some others I don't understand why you have to fetch millions or rows in a sorted order in the sql developer.
    p.s. Just because user can search by name say "John" and in my table say 80% of names are john, so from millions of records
    i will always be showing like 90,000 records (just being practical) and i don't want all of them at once, it can come 50 in a set.


    my main concern here is if i don't do order by i get quick result back but with order by i even have to wait 8+ mins to get back the records. just as i phrased in the above example for the name of john.
    Just because user can search by name say "John" and in my table say 80% of names are john, so from millions of records
     i will always be showing like 90,000 records
    Thanks
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points