varias - pl sql ejemplos

¿Cuáles son las implicaciones de rendimiento de Oracle IN Clause sin combinaciones?

Question

Tengo una consulta en esta forma que tomará en promedio ~ 100 en elementos de la cláusula, y en algunos momentos raros> 1000 elementos. Si hay más de 1000 elementos, dividiremos la cláusula in en 1000 (un máximo de Oracle).

El SQL está en forma de

SELECT * FROM tab WHERE PrimaryKeyID IN (1,2,3,4,5,...)

Las tablas que selecciono son enormes y contendrán millones de filas más de lo que figura en mi cláusula in. Mi preocupación es que el optimizador pueda elegir hacer una exploración de tabla (nuestra base de datos no tiene estadísticas actualizadas, sí, lo sé ...)

¿Hay alguna pista que pueda pasar para forzar el uso de la clave principal, SIN saber el nombre del índice de la clave principal, tal vez algo así como ... / * + DO_NOT_TABLE_SCAN * /?

¿Hay algún enfoque creativo para reducir los datos de manera tal que

1. Realizamos la menor cantidad de viajes de ida y vuelta
2. ¿Leemos la menor cantidad de bloques (en el nivel de IO lógico?)
3. ¿Será esto más rápido ...

SELECT * FROM tab WHERE PrimaryKeyID = 1
UNION
SELECT * FROM tab WHERE PrimaryKeyID = 2
UNION
SELECT * FROM tab WHERE PrimaryKeyID = 2
UNION ....

Answer 1

Si las estadísticas en su tabla son precisas, es muy poco probable que el optimizador opte por hacer una exploración de tabla en lugar de utilizar el índice de clave primaria cuando solo tiene 1000 elementos codificados en la cláusula WHERE . El mejor enfoque sería recopilar (o establecer) estadísticas precisas sobre sus objetos, ya que eso debería hacer que sucedan cosas buenas automáticamente en lugar de tratar de hacer muchas gimnasias para evitar las estadísticas incorrectas.

Si suponemos que las estadísticas son inexactas en la medida en que el optimizador llevaría a creer que un escaneo de tabla sería más eficiente que usar el índice de clave principal, podría agregar una sugerencia DYNAMIC_SAMPLING que obligaría al optimizador a reunir más estadísticas precisas antes de optimizar la declaración o una sugerencia de CARDINALITY para anular la estimación de cardinalidad predeterminada del optimizador. Ninguno de los dos requeriría saber nada sobre los índices disponibles, solo requeriría conocer el alias de la tabla (o el nombre si no hay alias). DYNAMIC_SAMPLING sería el enfoque más seguro y más robusto, pero agregaría tiempo al paso de análisis sintáctico.

Si está creando una instrucción SQL con una cantidad variable de parámetros codificados en una cláusula IN , es probable que cree problemas de rendimiento inundando su grupo compartido con SQL no compartible y forzando a la base de datos a gastar mucho tiempo analizando cada variante por separado. Sería mucho más eficiente si creara una única declaración de SQL compartible que pudiera analizarse una vez. Dependiendo de dónde provengan sus valores de la cláusula IN , eso podría parecerse a algo

SELECT *
FROM table_name
WHERE primary_key IN (SELECT primary_key
FROM global_temporary_table);

o

SELECT *
FROM table_name
WHERE primary_key IN (SELECT primary_key
FROM TABLE( nested_table ));

o

SELECT *
FROM table_name
WHERE primary_key IN (SELECT primary_key
FROM some_other_source);

Si se redujo a una única declaración de SQL compartible, además de evitar el costo de volver a analizar constantemente la declaración, tendría varias opciones para forzar un plan en particular que no implique modificar la declaración de SQL. Las diferentes versiones de Oracle tienen diferentes opciones para la estabilidad del plan: hay esquemas almacenados , administración de planes SQL y perfiles SQL, entre otras tecnologías, según su versión. Puede usar estos para forzar planes particulares para declaraciones SQL particulares. Sin embargo, si sigue generando nuevas sentencias de SQL que deben volver a analizarse, es muy difícil utilizar estas tecnologías.