Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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