Code Completion for Mixed Case Identifiers

In general, programming environment obey its design principles, and one of them is whether the language is conceived to be case sensitive or not. This is why mixed case identifiers in oracle are something like bastard children. What is the perspective of SQLDeveloper code completion?

Let’s create a couple of test tables

CREATE TABLE "SCOTT"."MixedCase"
( "MixedId" VARCHAR2(20 BYTE)
);

CREATE TABLE "SCOTT"."Mixed Case"
( "Mixed Id" VARCHAR2(20 BYTE)
);

If you type

select 1 from

and assuming that these two tables are in your schema, then they should appear on the list. Likewise, if you type

select 1 from "Mi

fast enough not to give any chance to code completion popup to surface after the from keyword, then you are still prompted with both table names. Furthermore, you erase the column expression “1″ you are prompted with column name. So it sort of works.

However, as several posters on SQLDeveloper forum indicated, this functionality is naturally expected to be extended so that a user don’t know if the table is in mixed case, and specifically what case the letters should be! Unfortunately, the

select 1 from Mi

won’t prompt you any mixed case identifiers.

The reason is that code completion executes the following dictionary query:

select ... from all_objects where object_name like 'MI%'

which misses the mixed case identifiers, and the query which “obviously” should be executing is

select ... from all_objects where upper(object_name) like 'MI%'


To understand why the second query is a bad idea let see the execution statistics in a moderately sized database (such as oracle Apps). Here is the first case captured by SQLDeveloper autotrace

As you see, the execution is lean 336 buffer gets and the crux is index skip scan on I_OBJ2 (where the value the first column OBJ$.OWNER# is not known). Compare this to the second case

where full OBJ$ table scan results in 5K buffer gets, which translates into the execution time of about 250 ms. This number is somewhat on the boundary of acceptable performance for code completion, which is designed to be fast, otherwise it directly affects the product end user experience. I decided that compromising performance for 99.9% of those users who don’t use pitiful mixed case identifiers is not worth it.

There is a straightforward fix on server side: the function based index with the first column upper(object_name), but unfortunately it is not there (even in oracle version 11).

P.S. In another SQLDeveloper forum thread a poster suggested that “obviously” code completion should be designed to cache all the identifiers into memory which would guarantee a “good performance”. This sort of ideas usually come from object propellerheads who are ignorant in database fundamentals and learned nothing from OODBMS failure. It doesn’t warrant a more detailed reply.