Geek asks and answers

Sort Statistics Optimizer

I am new to Oracle 11g and I have one question regarding the execution plan. I fulfilled the request and even though there is no ORDER BY clause in the request, statistics on the execution plan still shows that there are 6 types in memory. Can someone explain to me why this happened? Below is the query and statistics:

SQL> SELECT ZIP FROM NOZIPSORT WHERE ZIP BETWEEN '10000' AND '29999' AND rownum < 26;

25 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 4176934817

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    25 |   150 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |           |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| NOZIPSORT |    28 |   168 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<26)
   2 - filter("ZIP"<='29999' AND "ZIP">='10000')


Statistics
----------------------------------------------------------
        311  recursive calls
          0  db block gets
         47  consistent gets
         38  physical reads
        520  redo size
        805  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         25  rows processed
+3
source share
1 answer

I would say that these will be recursive calls that do some sorting - for example. when a query is parsed, you need to query the data dictionary to check the definitions of tables and columns, and some of these queries will include DISTINCT or other operations that require sorting.

+3
source

More articles:


All Articles