shared pool

[shawish_sababa]

Hi all

Someone told me that if I write an sql statment in small letters and soneone else writes it in capital letters, in the shared pool I'll see bouth of them becouse of the diff in the type of the letters (ei, uppercase v. lowercase)

Is this the case?

[pwoneill]

Yep, but I never understood why it didn't just to_upper or to_lower the statements before putting them in the shared pool. Anyone have a good explaination?

[aracnid]

because in SGA , oracle checks the statement not by meaning but by =. So A <> a

[pwoneill]

Right, but if oracle used a simple to_upper or to_lower on the statement before it put it in the shared pool, then they would be equal.

A = to_upper(a)
a = to_lower(A)

etc

[tamilselvan]

Why Oracle did not convert the SQL:
Look into the 2 statements-

SELECT EMPNO, ENAME
FROM EMP WHERE ENAME LIKE '%SCOTT%';

select empno, ename from emp where ename like '%scott%';

If application inserts data "SCOTT" in one row and "scott" in another row, both are different according to Oracle.
That is why it did not do conversion.

[pwoneill]

Yeah I suppose so it's not a simple to_upper or to_lower, but even a small procedure to change the case of everything but literals woulndn't be a) hard to code or b) much more processing required than a traditional to_upper or to_lower.