¿ocupa o no el indice?

Este es uno de los blogs que me gusta seguir http://www.depesz.com/ habla sobre todo de queries sql y esas cosas, pero todo basado en postgres. Me gusta seguirlo y hacer lo posible por imitar sus problemas pero en db2, he aqui un intento:HOW TO ORDER BY SOME RANDOM – QUERY DEFINED – VALUES?

Esta funcion viene por defecto en postgres y se ocupa demasiado, te da muchas ventajas no la eh optimizado ni nada, y por ahi pienso que se puede usar metaprogramación para que quede más rápida, pero ahí va.

CREATE OR REPLACE FUNCTION generate_series (ini integer, fin integer, inc integer)
RETURNS TABLE (val integer)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
     with dummy(id) as (
         select generate_series.ini from sysibm.sysdummy1
         union all
         SELECT id + generate_series.inc FROM dummy WHERE id < generate_series.fin
     )
     select id from dummy@ 

Las pruebas de rigor

select * from TABLE(generate_series(1,20,2)) as X@
--SELECT substr(funcschema, 1, 12) as funcs, substr(funcname, 1, 30) as func from syscat.functions  ORDER BY 1,2 @

Empezamos con el tutorial.

CREATE TABLE test_data (
id INT NOT NULL PRIMARY KEY, --te pide afuerza el not null
codename VARCHAR(254)
)@

SELECT * FROM test_data @

INSERT INTO test_data (id, codename)
SELECT i.val, 'codename for:' || i.val
FROM TABLE(generate_series(1,100,1)) as i @ -- pequeño truco, hay que poner TABLE si no no jala

Y este es el query que nos interesa, muy sencillo, pero me trae sus consecuencias.

SELECT  id, cast(codename as varchar(15)) FROM test_data WHERE id IN (3, 71, 5, 16);@
ID          2 
----------- ---------------
3 codename for:3
71 codename for:71
5 codename for:5
16 codename for:16
* 4 row(s) fetched, 4 row(s) output.
* Elapsed Time is:       0.001058 seconds

Y sorpresa, los valores nos los da ya ordenados. No hay que hacer más trucos, pero a mi experiencia esto esta mal.

SELECT id, cast(codename as varchar(15)) FROM test_data WHERE id IN (71, 3, 5, 16);@
ID          2 
----------- ---------------
71 codename for:71
3 codename for:3
5 codename for:5
16 codename for:16
* 4 row(s) fetched, 4 row(s) output.

Pequeñas variaciones y hace lo mismo. Ahora bien, ¿esto esta bien? segun yo no, los datos deberian de aparecer de acuerdo al orden de inserción, si no abría que asumir que la db esta haciendo algo que no le hemos indicado, en este caso ordenar. Pero el error sigue siendo otro, en esta consulta no se ocupa el indice

De Miguel Angel Huerta Gonzalez

SELECT id, cast(codename as varchar(15)) FROM test_data WHERE id IN (71, 3, 5, 16, 27, 19, 2);@
ID          2 
----------- ---------------
2 codename for:2
3 codename for:3
5 codename for:5
16 codename for:16
19 codename for:19
27 codename for:27
71 codename for:71
* 7 row(s) fetched, 7 row(s) output.
* Elapsed Time is:       0.050610 seconds

Y aqui esta lo que esperamos!!!, porque lo hizo? porque hay más datos?, si parece que ocupa el indice solo cuando hay muchos datos. Este es un comportamiento normal, las base de datos solo van al índice cuando hay que ir al índice.

De Miguel Angel Huerta Gonzalez

Imaginemos el seq scan, va comparando 1 por 1, y es por esto que los datos nos los da como aparecen en el IN. Cuando va al indice, rearma la consulta y los datos no los trae de acuerdo a como aparecen en el ínce.

Realizé otra prueba en sqlite, donde solo inserte 10 datos y ahi lo tienen, los datos aparecen en el orden del indice y no en el de insercion como pensaba...

sqlite> SELECT * FROM test_data WHERE id IN (3, 1, 5, 6);
1|codename for 1
3|codename for 3
5|codename for 5
6|codename for 6
sqlite>

-- Un select sin nada si nos da por orden de insercion.
sqlite>  SELECT * FROM test_data ;
10|codename for 10
9|codename for 9
8|codename for 8
7|codename for 7
6|codename for 6
5|codename for 5
4|codename for 4
3|codename for 3
2|codename for 2
1|codename for 1

-- Mis intentos de sacar un explain en consola no prosperaron
-- EXPLAIN PLAN SET QUERYNO = 13
-- EXPLAIN PLAN SET QUERYNO = 1 FOR SELECT * FROM test_data WHERE id IN (3, 71, 5, 16);