Excessive SORTs or natural reads if windowed functions are used in a query

[pavel-zotov]

Run DDL + prepare statements:

Database: /:employee, User: SYSDBA
SQL> recreate table test(x int);
SQL> insert into test select rand()*10000 from rdb$types rows 10;
SQL> commit;
SQL> create index test_x on test(x);
SQL> set explain on;
SQL> set per_tab on;

Then let's go:

SQL> select x from test order by x;

Select Expression
    -> Table "TEST" Access By ID
        -> Index "TEST_X" Full Scan
...
Per table statistics:
--------------------------------+---------+---------+---------+--
 Table name                     | Natural | Index   | Insert  | U
--------------------------------+---------+---------+---------+--
RDB$INDICES                     |         |        1|         |
TEST                            |         |       10|         |
--------------------------------+---------+---------+---------+--

(OK, expected)

But: Q-1:

SQL> select row_number()over(order by x) from test;

Select Expression
    -> Window
        -> Window Partition
            -> Record Buffer (record length: 25)
                -> Sort (record length: 28, key length: 8)
                    -> Window Buffer
                        -> Record Buffer (record length: 25)
                            -> Table "TEST" Full Scan

           ROW_NUMBER
=====================
                    1
                   ...
                   10

Per table statistics:
--------------------------------+---------+---------+-------
 Table name                     | Natural | Index   | Insert
--------------------------------+---------+---------+-------
TEST                            |       10|         |
--------------------------------+---------+---------+-------

-- why index not used here (to avoid external sort) ?

Q-2:

SQL> select row_number()over(order by x) as rn, x from test order by x;

Select Expression
    -> Sort (record length: 60, key length: 8) ---------------------------- [ 1 ]
        -> Window
            -> Window Partition
                -> Record Buffer (record length: 50)
                    -> Sort (record length: 52, key length: 8) ----------------------- [ 2 ]
                        -> Window Partition
                            -> Window Buffer
                                -> Record Buffer (record length: 25)
                                    -> Table "TEST" Full Scan

                   RN            X
===================== ============
                    1          414
                   ...             ...
                   10         9073

Per table statistics:
--------------------------------+---------+---------+---------+--------
 Table name                     | Natural | Index   | Insert  | Update
--------------------------------+---------+---------+---------+--------
TEST                            |       10|         |         |
--------------------------------+---------+---------+---------+--------

-- why two sorts here if they both must be done on the same key ("x") ?

PS. I could ask such questions long ago, in ~2013 ... 2014, but found them only in private talks with dimitr via email; also, some discussion was in sql.ru but its gone.

[livius2]

Nice analysis, I pray that the second sort will be less performance-intensive as the data is already sorted