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.