Hola, Tengo problema con indices con función
No se como funcionan los indices con función en Oracle 8i.
Para optimizar la siguiente consulta yo cree un indice con función de la siguiente manera :
Create index idx_tabla001 on tabla to_char(fecha,'dd/mm/yyyy');
Pero al hacer la consulta de esta forma (abajo) y usar la función no utiliza el indice (según el Explain plan).
Select campo
from tabla
where to_char(fecha,'dd/mm/yyyy' = to_char(sysdate,'dd/mm/yyyy')
Si la cambio por la siguiente (abajo), si usa el indice pero entrega un error "not a valid month":
Si lo cambio por este otro, no encuentra registros por el formato del campo fecha (tiene horas)
Select campo
from tabla
where fecha = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy')
Me puedes explicar que me falta o como funciona por favor.
Gracias
Atentamente.
Osvaldo Valenzuela.
El problema es que el campo "fecha" tiene horas
Para optimizar la siguiente consulta yo cree un indice con función de la siguiente manera :
Create index idx_tabla001 on tabla to_char(fecha,'dd/mm/yyyy');
Pero al hacer la consulta de esta forma (abajo) y usar la función no utiliza el indice (según el Explain plan).
Select campo
from tabla
where to_char(fecha,'dd/mm/yyyy' = to_char(sysdate,'dd/mm/yyyy')
Si la cambio por la siguiente (abajo), si usa el indice pero entrega un error "not a valid month":
Si lo cambio por este otro, no encuentra registros por el formato del campo fecha (tiene horas)
Select campo
from tabla
where fecha = to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy')
Me puedes explicar que me falta o como funciona por favor.
Gracias
Atentamente.
Osvaldo Valenzuela.
El problema es que el campo "fecha" tiene horas
1 Respuesta
Respuesta de pedrito12
1
Pues verás, Oracle tiene la costumbre, de no entrar por índice si se le pone un "apellido" a la hora de consultar un campo, sobretodo tratándose de un campo fecha.
El optimizador basado en costes es capaz de perder el norte, si le ponemos apellidos a los campos de esa manera, con lo que decide en casi, casi todas las ocasiones hacer un full scan de la tabla.
Solución:
Create un índice normal, como si fuese para un campo normal, y analízalo.
A partir de ahora puedes intentar varias cosas:
Implementar un HINT para que coja el índice por narices, o bien usar la función TRUNC.
El HINT lo puedes realizar de la siguiente manera:
Select /*+ INDEX tabla.nombre_indice */
campo from tabla
where to_char(fecha,'dd/mm/yyyy hh24:mi:ss' = to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') ;
De este modo tienes muchas posibilidades de coja el índice, pero si lo metes en una subconsulta, no funcionará ni con el HINT.
De todos modos, tienes que pensar que trabajar en tablas grandes, con precisiones de horas, minutos... es un poco peligroso, por que tendrá que barrerse la tabla entera con casi toda seguridad.
Puedes usar la función TRUNC.
Select TRUNC(to_date(sysdate, `DD-MM-YYYY HH24:MI:SS')) from dual;
... pero creo que lo que quieres es justo lo contrario... pues creo que en esta ocasión no hay más remedio que pasar del índice.
En ocasiones resulta que la tabla a tratar es supergrande, no se, 25, o 30 millones de registros... pues en ese caso se usan los llamados cartuchos de oracle, para realizar búsquedas y que no resulten tan pesadas... pero creo que no será necesario aprender a usar esto...
En fin, la solución pasa por si tu sentencia se usa muchas veces, y sobre todo si tienes muchos registros. De ser así, la mejor opción es intentar pasar de las horas, y truncar la fecha para que te coja el índice... si no pues creo que no habrá más remedio que hacer un full scan controlado.
El optimizador basado en costes es capaz de perder el norte, si le ponemos apellidos a los campos de esa manera, con lo que decide en casi, casi todas las ocasiones hacer un full scan de la tabla.
Solución:
Create un índice normal, como si fuese para un campo normal, y analízalo.
A partir de ahora puedes intentar varias cosas:
Implementar un HINT para que coja el índice por narices, o bien usar la función TRUNC.
El HINT lo puedes realizar de la siguiente manera:
Select /*+ INDEX tabla.nombre_indice */
campo from tabla
where to_char(fecha,'dd/mm/yyyy hh24:mi:ss' = to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') ;
De este modo tienes muchas posibilidades de coja el índice, pero si lo metes en una subconsulta, no funcionará ni con el HINT.
De todos modos, tienes que pensar que trabajar en tablas grandes, con precisiones de horas, minutos... es un poco peligroso, por que tendrá que barrerse la tabla entera con casi toda seguridad.
Puedes usar la función TRUNC.
Select TRUNC(to_date(sysdate, `DD-MM-YYYY HH24:MI:SS')) from dual;
... pero creo que lo que quieres es justo lo contrario... pues creo que en esta ocasión no hay más remedio que pasar del índice.
En ocasiones resulta que la tabla a tratar es supergrande, no se, 25, o 30 millones de registros... pues en ese caso se usan los llamados cartuchos de oracle, para realizar búsquedas y que no resulten tan pesadas... pero creo que no será necesario aprender a usar esto...
En fin, la solución pasa por si tu sentencia se usa muchas veces, y sobre todo si tienes muchos registros. De ser así, la mejor opción es intentar pasar de las horas, y truncar la fecha para que te coja el índice... si no pues creo que no habrá más remedio que hacer un full scan controlado.
