Hard Parse on Non-Identical Statements?

Question

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.

Answer 1

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.