3 Replies Latest reply on Jun 1, 2020 9:32 AM by Jonathan Lewis

    Estimated cost of query is lower after adding ORDER BY?

    mathguy

      Sorry if this has been asked and answered before; about five minutes of Google searching didn't bring anything back.

       

      I just noticed this today, as I was testing a statement I made in another thread - about needing (or not needing) ORDER BY in a query where there is already an analytic function that orders by the exact same criterion. As an aside to what I said there, I saw something I hadn't noticed before, and I am puzzled.

       

      Why is it that, given ANY query whatsoever, the estimated cost goes DOWN when we add an ORDER BY clause, and otherwise don't change the query in any way? Clearly that can't be the case for the TRUE cost, but why does the ESTIMATED cost (as reported by EXPLAIN PLAN) go down? The plan is otherwise identical (and the statistics are the same); the only difference is the estimated cost, and estimated CPU percentage.

       

      Or, if you prefer - although the meaning is exactly the same - why is it possible, for any query whatsoever, that the estimated cost goes UP when we remove the ORDER BY clause at the end?

       

      I use SCOTT.EMP for this. I checked to make sure I didn't add any indices on the table. (In fact I had, so I dropped the PK on EMPNO before running these tests.)

       

       

      SQL> select row_number() over (order by empno) rn, ename from scott.emp;

       

      14 rows selected.

       

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 3145491563

       

      ---------------------------------------------------------------------------

      | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------

      |   0 | SELECT STATEMENT   |      |    14 |   140 |     4  (25)| 00:00:01 |

      |   1 |  WINDOW SORT       |      |    14 |   140 |     4  (25)| 00:00:01 |

      |   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |

      ---------------------------------------------------------------------------

       

       

      Statistics

      ----------------------------------------------------------

            1  recursive calls

            0  db block gets

            7  consistent gets

            0  physical reads

            0  redo size

          837  bytes sent via SQL*Net to client

          608  bytes received via SQL*Net from client

            2  SQL*Net roundtrips to/from client

            1  sorts (memory)

            0  sorts (disk)

           14  rows processed

       

       

       

       

       

      SQL> select row_number() over (order by empno) rn, ename from scott.emp

        2  order by empno;

       

      14 rows selected.

       

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 3145491563

       

      ---------------------------------------------------------------------------

      | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------

      |   0 | SELECT STATEMENT   |      |    14 |   140 |     3   (0)| 00:00:01 |

      |   1 |  WINDOW SORT       |      |    14 |   140 |     3   (0)| 00:00:01 |

      |   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |

      ---------------------------------------------------------------------------

       

       

      Statistics

      ----------------------------------------------------------

            1  recursive calls

            0  db block gets

            7  consistent gets

            0  physical reads

            0  redo size

          837  bytes sent via SQL*Net to client

          608  bytes received via SQL*Net from client

            2  SQL*Net roundtrips to/from client

            1  sorts (memory)

            0  sorts (disk)

           14  rows processed

       

        • 1. Re: Estimated cost of query is lower after adding ORDER BY?
          Jonathan Lewis

          I didn't notice this when it first appeared, but it's just popped up to the top of the list so I took a quick look at it.

           

          You said:

          "Why is it that, given ANY query whatsoever, the estimated cost goes DOWN when we add an ORDER BY clause, and otherwise don't change the query in any way?"

          I assum that what you had in mind was

          "any query with a WINDOW sort that makes sorting for an order by clause redundant"

           

          It's a bug and, working through a CBO trace, I think that what's happening is that the optimizer is recognising the opportunity to do "order by elimination" because of the matching window function - and then forgets to include the cost of sorting for the window function. (There's nothing under the heading "WiF sort" where the calculation ought to be).

           

          I'll see if I can find time to write up a note about it and let Oracle know about it.

           

          Regards

          Jonathan Lewis

          • 2. Re: Estimated cost of query is lower after adding ORDER BY?
            mathguy

            It popped to the top when I marked it "assumed answered". I don't like to keep questions unanswered for too long. If I had a more meaningful question, I would have left the thread as "unanswered"; but since no one seemed interested in this question, I simply assumed that what I saw must be a bug, so I assumed it's "answered".

             

            Perhaps my English is not very clear, but you did understand what I meant. What I meant to say was, "how is it possible that there is even one query whose estimated cost goes down when we add an ORDER BY clause? The set of such queries should be empty, no matter what the query does."

             

            Cheers,    -    mathguy

            • 3. Re: Estimated cost of query is lower after adding ORDER BY?
              Jonathan Lewis

              Thanks for the reply.

              Sorry about misunderstading your original statement.

               

              You've also answered a question that I've wondered about for some time viz: how does a question get to "Assumed Answered"; I hadn't realised that that was an option available to the owner of the question.

               

              Regards

              Jonathan Lewis