Indice con función en el where.
Mi problema es que una consulta no usa el indice (aun intentando forzarlo) al incluir en el where una función (LAST_DW_EXTRACT_DATE, que devuelve la fecha de la ultima extracción):
select /* + INDEX (CCH.I2_CCH) */ CCH.*
from CCH where LAST_UPDT_DATE_TIME_STAMP > LAST_DW_EXTRACT_DATE('CCH');
En cambio, si quito la función, si que entra según el plan:
select /* + INDEX (CCH.I2_CCH) */ CCH.*
from CCH where LAST_UPDT_DATE_TIME_STAMP > SYSDATE;
He intentado usar variables, pero me obliga a usar SELECT INTO. Hay alguna otra manera?
1 Respuesta
Cuando forzás el indice CCH.I2_CCH, ¿Asumo qué el indice es sobre la función que utilizas y no solo del campo?
Sino crea un indice basado en la función, es decir la condición que utilizas, es por eso que creo no esta utilizando ningún indice y te hace un full scan; en tu caso el indice debería ser
Create index YYYYYY on CCH
(LAST_DW_EXTRACT_DATE(LAST_UPDT_DATE_TIME_STAMP ))
Creo que debería funcionar.
El indice I2.CCH está sobre el campo LAST_UPDT_DATE_TIME_STAMP de CCH. Si intento crear indice sobre la función indica:
CREATE INDEX indexPrueba ON CCH (LAST_DW_EXTRACT_DATE(LAST_UPDT_DATE_TIME_STAMP ))
Informe de error -
Error SQL: ORA-30553: La función no es determinista
30553. 00000 - "The function is not deterministic"
*Cause: The function on which the index is defined is not deterministic
*Action: If the function is deterministic, mark it DETERMINISTIC. If it
is not deterministic (it depends on package state, database state,
current time, or anything other than the function inputs) then
do not create the index. The values returned by a deterministic
function should not change even when the function is rewritten or
recompiled.
La función es la siguiente:
create or replace function last_dw_extract_date (tname in varchar)
return timestamp
is
tstamp timestamp;
cursor cur is
select min(last_extracted)
from dw_extract_date
where table_name = tname;
begin
open cur;
fetch cur into tstamp;
if tstamp is null then
tstamp := to_date('19000101', 'yyyymmdd');
end if;
close cur;
return tstamp;
exception
when others then
raise_application_error(-20001, 'error fetching extract date - ' ||
sqlcode || ' - ' || sqlerrm);
end;
Gracias por la ayuda.
Muchas veces se da este error, justamente al trabajar funciones con fechas, o al crear indices basado en funciones, si deseas seguir probando tendrías que modificar tu función "last_dw_extract_date" agregando en su definición la palabra deterministic, de esta manera:
create or replace function last_dw_extract_date (tname in varchar)
return timestamp deterministic
Is
...
end;
Ese cambio no te afectara en nada la operación del mismo, únicamente define que la función retornara el mismo valor cuando se le envíe un determinado parámetro.
Luego prueba crear el indice nuevamente.
