PL/SQL (MOSC)

MOSC Banner

sql in pl/sql dont use indexes

edited Apr 26, 2020 11:13PM in PL/SQL (MOSC) 2 commentsAnswered ✓

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center