1

Let's say I have this query :

SELECT X FROM Y

If I run it for the first time then it would do a hard parse.

After that, I run this query :

select x from y

With just a difference in alphabetical upper and lower case, does it mean they have to do the hard parse again?

Thank You.

1 Answer 1

4

Yes, those are two different queries. They'll both be parsed and saved in the SQL area independently.

SQL> create table share_test (foobar number);
Table created.

SQL> select foobar from share_test;
no rows selected

SQL> SELECT foobar FROM SHARE_TEST;
no rows selected

SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%foobar%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
g9cdv3mpuhd8d
select sql_id, sql_text from v$sqlarea where sql_text like '%foobar%'

d2v4u39j41y6k
select foobar from share_test

fdsqytc1y3f4u
SELECT foobar FROM SHARE_TEST

The criteria for SQL sharing are in the Configuring and Using Memory - SQL Sharing Criteria docs. Among others:

The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.