Remove ORDER BY clause from PARTITION BY clause?

Question

Is there a way I can reduce the impact of the 'ORDER BY lro_pid' clause in the OVER portion of the inner query below?

SELECT *
  FROM (SELECT a.*, 
               Row_Number() over (PARTITION BY search_point_type 
                                      ORDER BY lro_pid) spt_rank
          FROM lro_search_point a
      ORDER BY spt_rank)
 WHERE spt_rank = 1;

I don't care to order this result within the partition since I want to order it by a different variable entirely. lro_pid is an indexed column, but this still seems like a waste of resources as it currently stands. (Perhaps there is a way to limit the ordering to a range of a single row?? Hopefully no time/energy would be spent on sorting within the partition at all)

Footton

+1, interesting. I do wonder if it really costs much to do the ordering within the partitions. – Celestial

judging by @jonearles's test, I think the answer is 'yes'. Given a big enough data set where the column to be partitioned over is non-indexed, this could make a big difference! Thanks for your comments and answers! – Footton

Answer 1

Using a constant in the analytic ORDER BY as @Will A suggested appears to be the fastest method. The optimizer still performs a sort, but it's faster than sorting a column. Also, you probably want to remove the second ORDER BY, or at least move it to the outer query.

Below is my test case:

--Create table, index, and dummy data.
create table lro_search_point(search_point_type number, lro_pid number, column1 number
    ,column2 number, column3 number);
create index lro_search_point_idx on lro_search_point(lro_pid);
insert /*+ append */ into lro_search_point
select mod(level, 10), level, level, level, level from dual connect by level <= 100000;
commit;


--Original version.  Averages 0.53 seconds.
SELECT * FROM 
(
    SELECT a.*, Row_Number() over (PARTITION BY search_point_type ORDER BY lro_pid) spt_rank
    FROM lro_search_point a
    ORDER BY spt_rank
)
WHERE spt_rank=1;


--Sort by constant.  Averages 0.33 seconds.
--This query and the one above have the same explain plan, basically it's
--SELECT/VIEW/SORT ORDER BY/WINDOW SORT PUSHED RANK/TABLE ACCESS FULL.
SELECT * FROM 
(
    SELECT a.*, Row_Number() over (PARTITION BY search_point_type ORDER BY -1) spt_rank
    FROM lro_search_point a
    ORDER BY spt_rank
)
WHERE spt_rank=1;


--Remove the ORDER BY (or at least move it to the outer query).  Averages 0.27 seconds.
SELECT * FROM 
(
    SELECT a.*, Row_Number() over (PARTITION BY search_point_type ORDER BY -1) spt_rank
    FROM lro_search_point a
)
WHERE spt_rank=1;


--Replace analytic with aggregate functions, averages 0.28 seconds.
--This idea is the whole reason I did this, but turns out it's no faster.  *sigh*
--Plan is SELECT/SORT GROUP BY/TABLE ACCESS FULL.
--Note I'm using KEEP instead of just regular MIN.
--I assume that you want the values from the same row.
SELECT a.search_point_type
    ,min(lro_pid) keep (dense_rank first order by -1)
    ,min(column1) keep (dense_rank first order by -1)
    ,min(column2) keep (dense_rank first order by -1)
    ,min(column3) keep (dense_rank first order by -1)
FROM lro_search_point a
group by a.search_point_type;

Unthrone

Answer 2

A couple of things to try:

Can you e.g. ORDER BY 'constant' in the OVER clause?

If ordering by a constant is not permitted, how about ORDER BY (lro_pid * 0)?

I'm not an Oracle expert (MSSQL is more my thing) - hence questions to answer your question!

Ten

+1, Clever! It appears that it works, i.e., if I just issue a select from a table and order by '1', the results are seemingly unordered and appear to be in the same order as without the order by. I have to wonder though if the order by clause is really costing much extra, if anything. A timing test is in order! – Celestial

@Celestial - thanks, I'd hope either of these approaches are able to be "optimized away" by the query optimizer. – Ten

Answer 3

To obmit the clause ORDER BY you could use ORDER BY rownum.

Syndactyl