ejemplos - select sort sql

¿Quitar la cláusula ORDER BY de la cláusula PARTITION BY?

Question

¿Hay alguna manera de reducir el impacto de la cláusula ''ORDER BY lro_pid'' en la parte OVER de la consulta interna a continuación?

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;

No me importa pedir este resultado dentro de la partición ya que quiero ordenarlo por una variable diferente por completo. lro_pid es una columna indexada, pero todavía parece una pérdida de recursos tal como está actualmente. (Tal vez haya una forma de limitar el orden a un rango de una sola fila) Espero que no se gaste tiempo / energía en ordenar dentro de la partición en absoluto)

Answer 1

El uso de una constante en la orden analítica ORDER BY como @Will A parece ser el método más rápido. El optimizador todavía realiza una clasificación, pero es más rápido que ordenar una columna. Además, es probable que desee eliminar el segundo ORDER BY, o al menos moverlo a la consulta externa.

A continuación está mi caso de prueba:

--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;

Answer 2

Para obtener la cláusula ORDER BY , puede usar ORDER BY rownum.

Answer 3

Un par de cosas para probar:

¿Puedes, por ejemplo, ORDER BY ''constant'' en la cláusula OVER?

Si no está permitido ordenar por una constante, ¿qué hay de ORDER BY (lro_pid * 0) ?

No soy un experto en Oracle (MSSQL es más de lo que me gusta), de ahí las preguntas para responder a su pregunta.