December 24, 2009

Holiday Quiz

I have a table with one million rows, there are no indexes on the table. The table has a column called sortcode which has no nulls, and has been generated in a highly random way so that no value appears more than four times. Consider the following queries:

select
sortcode
from t1
order by
sortcode
;
select sortcode
from
(
select sortcode
from t1
order by
sortcode
)
where
rownum <= 10
;

How many rows are sorted in each of these two queries – and roughly how much memory would you expect Oracle to use ?

You are killing me. :)
I just tried to run the test and the results left me “shattered”… Here are the details:
First the spool file:
======================================================
SQL> select * from v$version ;

It would seem that both SQL statements would need to sort 1,000,000 rows. In the second case, the entire row source would need to be sorted so that the first 10 rows sorted in ascending order may be determined, and once sorted, all but the first 10 rows could be discarded before being passed beyond the inline view.

The question of how much memory would be needed is a bit more difficult to determine. What if the default character set requires more than one byte per character? If each character requires a single byte, I would estimate for an optimal execution 6 bytes per ROWID plus 7 bytes per SORTCODE for each of the 1,000,000 rows – roughly 13MB for both methods. The actual memory used by the first SQL statement is a bit higher for an optimal execution. The actual memory used for the second SQL statement for an optimal execution is significantly less – is it possible that Oracle uses an optimization, such as maintaining something like a linked list with 10 sorted elements when the ROWNUM predicate is specified outside the inline view? Of course, if multi-pass executions are permitted, both methods could require 64KB (or less).

A couple hours after I posted my reply I realized that Oracle needs to allocate memory for an array pointer for each row to be sorted in memory – so that it is able to jump to the memory position holding each data item to be sorted. On a 32 bit platform this should be a 32 bit unsigned integer (4 bytes). On a 64 bit platform this should be a 64 bit unsigned integer (8 bytes). Thus, the amount of memory required should be roughly 6 bytes for the ROWID plus 7 bytes for the SORTCODE value (assuming the default character set requires a single byte) plus 4 bytes or 8 bytes – so 17 bytes or 21 bytes per row multiplied by the number of rows.

For the first SQL statement, this calculation seems to be very close. On 32 bit Oracle DBMS_XPLAN reports 17MB used, and on 64 bit Oracle DBMS_XPLAN reports 21MB used when an optimal execution is permitted.

DBMS_XPLAN showed that roughly 2MB was required for an optimal execution for the second SQL statement. But, does this memory requirement change for older Oracle database releases (maybe the optimization is a recent addition)?

.. accessable transformations (e.g. pushed predicate) of the query by the optimizer
and
.. (after pushed predicate) algorithm of sorting. If there are sort and save first ten rows (10-list) from the very beginning, then each residual row of table is checked (and is replaced if needed) with that 10-list, then the number of sorted rows is 10.

With first query 1000000 rows are sorted. First query consumed something around 22M on 10GR2 running at 64-bit platform with “version 2 sort” for optimal sorting. Second query consumes just around 2048 bytes of memory (I suppose that performance and memory consumption can vary due row distribution in 2-nd query).

In the second case, the sort area will contain 10 rows sorted. Each row in the table will be traversed but only 10 rows will be sorted at a time, with the new row included or discarded,depending on the sortcode value.

Not sure about the exact calucualtion of memory needed but case 1 will take much . In case 2, sorting 10 rows in RAM will be far less.

Query-1 will sort all 1M rows while the query-2 will sort ONLY 10 rows. Both the queries will read all 1M rows but query-2 will be able to limit number of rows to sort by using “SORT ORDER BY STOPKEY” step.

Since this is a quiz for the holidays and it is Jonathan who is raising the question, I could imagine there are more subtle things to consider.

E.g. he says “I have a table with one million rows, there are no indexes on the table”, but what if the table was the index (IOT)?

Since SORTCODE “has no nulls” it could be the leading column of a composite primary key – so an IOT is a possible construct to avoid sort operations.

If you use now a suitable NLS_SORT = binary setting for the assumed CHAR(6) column, both statements could go along without any sort operation at all and no memory required for this.

What about an indexed cluster? Again the index is not on the table, but on the cluster… It’s a bit splitting hairs but just throwing out some ideas.

It is correct that with a heap table the second query can use a “SORT ORDER BY STOPKEY” operation that only needs to keep the top N rows as it goes along the unsorted row source and therefore requires a far smaller workarea size for the sort operation.

I think that Randolf has a point here – there must be more variables to consider. What if table T1 is range partitioned on the SORTCODE column? I put together a test case that shows that with range partitioning, only 12 rows will be sorted for the second SQL statement (the number varies based on the granularity of the partitioning) – without range partitioning, the second SQL statement sorts 1,000,000 rows. In either case, DBMS_XPLAN on 11.1.0.7 reports that the second SQL statement uses 2MB of memory, even though the actual amount used will likely be less (possibly close to what my other responses suggest).

Hm, interesting idea. Have you tried that? It would be interesting to see how this MV definition is supposed to look like to give the response directly in 1 gets, in particular for the first query, but also for the second one?

The first query is quite clear: Oracle read all the table, sort all the rows. Memory usage depends on Oracle version as different version of Oracle uses different sort algorithms, as the column is of 6 bytes (I’m assuming 1 char=8 bit) then you have more or less 6*1.000.000, so 6M of RAM, but if some kind of “hash sort” is used, this can be lower.

As stated by many here Oracle (from 9 and up?) can do a “SORT ORDER BY STOPKEY” so in the second case, it will use an array of dimension 10 (the “rownum” we want in the example) and sort/replace only 10 rows (full table scan still apply). Memory used 10*6=60 bytes.