Tengo una mesa como esta:

create table A (
    fieldA numeric,
    fieldB varchar(255),
    fieldC varchar(255),
... fields ... constraints ... 
);

Y tengo un índice B-tree único en fieldB. Entonces, cuando ejecuto una consulta como esta:

select /*+ index(a)*/ * from A a where fieldB > 'LOW' and fieldB < 'HIGH' order by fieldB 

Espero ver un plan de consulta con exploración de rango de índice y sin orden de clasificación por cláusula (o cualquier otro tipo de clasificación), debido al índice existente. De hecho, tengo sort order by en el plan de consulta

explain plan for select /*+ index(a)*/ * from A a where fieldB > 'LOW' and fieldB < 'HIGH' order by fieldB

Resultado:

SELECT STATEMENT                     
SORT ORDER BY                       
TABLE ACCESS BY INDEX ROWID BATCHED
INDEX RANGE SCAN

Si elimino order by en mi consulta, no obtendré los datos en el orden necesario (eso es extraño para mí, porque por defecto el índice B-Tree debería construir un árbol de filas en orden ascendente y en ese caso {{X1} } y select * from A order by fieldB deberían dar el mismo plan de consulta que pensé)

¿Cómo puedo evitar sort order by?

Versión de Oracle: 12.2

0
Nick Ryan 17 ene. 2018 a las 11:50

3 respuestas

La mejor respuesta

El problema estaba en los parámetros NLS_LANGUAGE y NLS_TERRITORY de la sesión. Por defecto, mi sesión tiene

NLS_LANGUAGE= 'RUSSIAN'

NLS_TERRITORY= 'RUSSIA'

Y cuando trato de obtener los datos en un orden particular usando el valor de indexación varchar y ordenar por consulta obtengo sort order by adicional en el plan de consulta.

Después de cambiar estos parámetros:

NLS_LANGUAGE= 'AMERICAN'

NLS_TERRITORY= 'AMERICA'

No obtengo sort order by adicional en mi plan de consultas.

Ahora, consultas:

EXPLAIN PLAN FOR select * from SIEBEL_CT.A a where fieldB > 'LOW' and fieldB < 'HIGH' order by fieldB

EXPLAIN PLAN FOR select * from SIEBEL_CT.A a where fieldB > 'LOW' and fieldB < 'HIGH'

Tener el mismo plan de consulta

0
Nikita Ryanov 17 ene. 2018 a las 12:22

La ruta de acceso TABLE ACCESS BY INDEX ROWID BATCHED es nueva en 12c; de los documentos:

La base de datos utiliza los ID de fila obtenidos del índice para buscar las filas correspondientes de la tabla ... y luego recuperarlas. El acceso BATCHED ... significa que la base de datos recupera algunos ID de fila del índice, y luego intenta acceder a las filas en orden de bloque para mejorar la agrupación y reducir el número de veces que la base de datos debe acceder a un bloque.

Está recuperando los datos reales de manera más eficiente al reducir el número de veces que golpea cada bloque (suponiendo que la distribución de datos real significa que hubo bloques con datos relevantes para más de una fila). Pero como resultado, los datos recuperados no están necesariamente en orden de índice, por lo que deben ordenarse explícitamente.

A medida que obtengamos el rowid usando el índice, obtendremos todos los datos de DB en el mismo orden. Esas son mis expectativas

Eso ya no es cierto con el acceso BATCHED.

El problema es que agrega un costo adicional incluso si no es necesario hacerlo.

Pero es necesario; y no está viendo la reducción de costos del acceso por lotes (que depende de la distribución de datos real, como se mencionó anteriormente). Realmente no puede mirar un valor de costo de forma aislada. El optimizador generalmente sabe lo que está haciendo. (No creo que la pista esté afectando esto en absoluto, pero estoy de acuerdo con @XING en que no debería tenerlo, a menos que esté realmente seguro de saber algo que el optimizador no sabe).


Vale la pena señalar que, aunque la versión 11g no mostró una clasificación explícita (según la demostración de @ XING), Oracle nunca ha garantizado que los resultados del pedido se devuelvan sin una cláusula order by y con una simple {{X1 }} (es decir, no BATCHED) el optimizador solo puede omitir la ordenación explícita porque ya sabe que los datos se recuperaron en ese orden. Que es a lo que aludiste en un comentario.

En 11g, probablemente recuperaría las filas en el orden deseado sin la cláusula order by, pero solo debido a los detalles de implementación de cómo Oracle está obteniendo los rowid indexados y luego la fila de datos correspondiente -por fila. Pero no está garantizado. Este tipo de cambio bajo el capó es exactamente la razón por la cual siempre ha sido prudente explícitamente order by incluso cuando parecía ser redundante. Como lo está haciendo en su código actual. Pero no es redundante, y nunca lo fue realmente.

(Hubo un 'problema' similar con group-by en una versión anterior; generalmente implicaba un orden de clasificación (hasta 9i, creo) y la gente omitió order by; pero luego el optimizador cambió (creo que en 10 g ) y eso atrapó a la gente ...)

1
Alex Poole 17 ene. 2018 a las 12:03

No es necesario utilizar la sugerencia /*+ index(a)*/ cuando ya tiene el índice en su columna. Deje que el optimizador elija el mejor plan de ejecución para su consulta. Vea abajo:

Mesa:

create table A (
    fieldA numeric,
    fieldB varchar(255),
    fieldC varchar(255)
);

create index indx on A(fieldB);

Consulta:

select * from A a where fieldB > 'LOW' and fieldB < 'HIGH' order by fieldB ;

Plan: no se utiliza Ordenar por orden.

Plan Hash Value  : 4131509220 

--------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    1 |   271 |    0 |          |
| * 1 |   FILTER                       |      |      |       |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID | A    |    1 |   271 |    1 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN           | INDX |    1 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(NULL IS NOT NULL)
* 3 - access("FIELDB">'LOW' AND "FIELDB"<'HIGH')


Note
-----
- dynamic sampling used for this statement
1
XING 17 ene. 2018 a las 11:16
48297005