Difference Between SELECT and select in oracle sql

Question

The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. Means The SELECT instead of select statement will be parsed again in library cache.

But Some where i read that the oracle treat's lowercase select as upper SELECT automaticaly in a statement

---

Which on is true above those two...

Answer 1

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt.

The issue being described is that if you issue these three semantically identical statements:

 SELECT thing FROM mytable;

 select thing from mytable;

 Select Thing From MyTable;

the database treats them as three entirely different entities, that will each occupy some space in the library cache. They will each have been parsed individually, costing CPU each time, even if the resulting execution plan are are identical.

Same thing if you have two identical queries but one with a comment and another without - they will be treated as different statements entirely by the engine, and each consume resources. (I believe even spacing matters.)

(This is actually helpful for debugging. If you're trying different settings to optimize a query without changing the query text, add a comment describing the current settings each time you run it. You're guaranteed a re-parse, and can find the individual runs in [g]v$sql easily with those comments. This wouldn't work if the engine "normalized" the query text before parsing.)

What this means for you is that you should be consistent with your query syntax. Choose a "coding" style and stick with it to avoid littering the various caches with semantically identical queries.

Example: notice the three different SQL IDs.

select a from foo;
Select a From foo;
SELECT a FROM foo;

select * from v$sql where sql_text like '%foo%';

SQL_ID        SQL_TEXT
------------- -------------------
dxdhrwqjpn6b7 Select a From foo  
0dqb3y3bask98 select a from foo  
1y13x9j2rwzuz SELECT a FROM foo  
...

Answer 2

Check the Oracle documentation here. There is a hash associated with queries in the shared pool (what Oracle calls the query cache) - so you could issue two - one with caps, one with lower case and check the hash and see if they're the same. [EDIT - removed rash speculation]