1 2 Previous Next 20 Replies Latest reply: Jan 14, 2013 11:34 AM by 899722 RSS

    SQL Query : Order By issue with HUGE Table

    899722
      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
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: SQL Query : Order By issue with HUGE Table
            6363
            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
              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
                mistakenly i hit answered
                • 5. Re: SQL Query : Order By issue with HUGE Table
                  899722
                  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
                    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
                      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
                        >
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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