sql in pl/sql dont use indexes
Hello
I have sql code .
It work about 1 sec and use functional index on identdata7: UPPER(identdata7)
identdata7UPPER(p.identdata7)
SELECT DISTINCT p.idperson idperson FROM person p WHERE 1 = 1 AND (UPPER(p.identdata7) = UPPER('851004451091') OR '851004451091' IS NULL) AND (UPPER(p.data1) = UPPER(NULL) OR NULL IS NULL) AND (UPPER(GetFullName(name1, name2, initials, nickname)) = UPPER(NULL) OR NULL IS NULL);
but when i do it in pl/sql just like this
DECLARE lsTmpIBIN VARCHAR2(250); lsTmpRNN VARCHAR2(250); lsTmpCrBy VARCHAR2(2000); lsTmp VARCHAR2(2000);BEGIN lsTmpIBIN := '851004451091'; lsTmpRNN := NULL; lsTmp := NULL; SELECT ' IN (' || listagg(idperson, ',') WITHIN GROUP(ORDER BY NULL) || ')' INTO lsTmpCrBy FROM (SELECT DISTINCT p.idperson idperson FROM person p WHERE 1 = 1 AND (UPPER(p.identdata7) = UPPER(lsTmpIBIN) OR lsTmpIBIN IS NULL) AND (UPPER(p.data1) = UPPER(lsTmpRNN) OR lsTmpRNN IS NULL) AND (UPPER(GetFullName(name1, name2, initials, nickname)) = UPPER(lsTmp) OR lsTmp IS NULL)); dbms_output.put_line(lsTmpCrBy);END;
0