Code Completion for Mixed Case Identifiers

March 24, 2008

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

( "MixedId" VARCHAR2(20 BYTE)

( "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

Skip scan select from all_objects

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

Full scan select from all_objects

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.


One Response to “Code Completion for Mixed Case Identifiers”

  1. Marcel Says:

    There is another scenario where mixed case code completion would be extremely helpful: PL/SQL.
    Let’s assume I hava a package “Test” with a procedure “DoSomeReallyHardWork”.
    I would really like code completion to complete a call of this procedure and preserve the case.
    I don’t like “test.dosomereallyhardwork” and I strongly dislike “TEST.DOSOMEREALLYHARDWORK().
    Is there any way to achive this kind case preservation in PL/SQL?
    I know SQL Developer goes to the all_arguments view to extract PL/SQL names, it should parse the original source code instead. So this time it is not a performance problem but simply a missing feature.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: