Differences between two analytic queries

Question

Which are the diferrences between the following two queries? Both returns different rows:

with ordered_table as 
(
select * from table order by column1
)
select first_value(column2 ignore nulls) over (partition by column3)
from ordered_table;

and

select first_value(column2 ignore nulls) over (partition by column3 order by column1)
from table;

Note: I'll try to provide a test-case but I think that for someone having the concepts clear is not needed.

Answer 1

The ordering by column1 doesn't really do anything in the first query.

It create a result set in order, but I don't think that carries into your partition statement. The ordering belongs in the actual partition clause.

Answer 2

The first version orders the entire row set by column1, then partitions it by column3 and does the analytic calculation. Note that the initial ordering by column1 is not necessarily preserved through the partitioning -- which I think is what is probably hanging you up.

The second version partitions the data by column3, then sorts within each partition by column1, then determines the analytic output.

Answer 3

WITH ordered_table AS
        (
        SELECT  *
        FROM    table
        ORDER BY
                column1
        )
SELECT  FIRST_VALUE(column2 IGNORE NULLS) OVER (PARTITION BY column3)
FROM    ordered_table

This query will return the first per-partition value of column2 in no particular order (i. e. the CBO is free to choose any order it considers the best).

SELECT  FIRST_VALUE(column2 IGNORE NULLS) OVER (PARTITION BY column3 ORDER BY column1)
FROM    table;

This query will return the first per-partition value of column2, with partitions ordered by column1.

Since SQL language operates on sets, and your ORDER BY clause does nothing with the set returned in the CTE, Oracle just ignores the ORDER BY part.

CBO can choose to materialize, hash or in any other way mutilate the CTE before it's used by the outer query, so the ORDER BY used in the CTE will be lost for the outer query.