4

I just listened a dba say that if you name all your functions, procedures etc in uppercase, the db will perform better when the workload in the database is high. Is this any true at all? I find that nearly impossible to believe. Is there any benchmark or something that prove him right or wrong?

I only think that this naming things have value when reading code, specially for a team. It is more likely that you can successfully modify code that is written following good writing conventions than just random stuff. But this has nothing to do with the computer performance, it should execute just the same.

Also, if it was like this, I should not be able to invoque a function named FUNC like func or Func or funC.

I think that statement is crazy, I'm I wrong? I mean, I'm not a database expert at all and he is a dba.

Roger
  • 2,912
  • 2
  • 31
  • 39
  • 6
    sounds like your dba has a sense of humor. – tbone Jun 25 '13 at 19:16
  • @tbone It's not my dba but he is serious! he made people modify their code to match this! – Roger Jun 25 '13 at 19:19
  • 3
    I think he/she just wants people to NOT use double quotes around names (really for naming sanity, not for performance). So this is fine: create function my_func ... but this is NOT: create function "my_func" ... If you look in dba_objects, the first one (without quotes) will be stored as MY_FUNC whereas the second will be in lowercase my_func – tbone Jun 25 '13 at 19:27
  • Rule #1. The dba is never wrong. Rule #2. If the dba is wrong, see Rule #1. :) – Jeffrey Kemp Jun 28 '13 at 06:54
  • @JeffreyKemp Rule #2 is never reachable? ;] – Roger Jun 28 '13 at 16:19

2 Answers2

5

By default, Oracle identifiers are case insensitive. Unless you explicitly create a case-sensitive function name (by enclosing the name in double quotes), which creates a host of issues none of which are related to performance, Oracle is going to store the function name in the data dictionary in upper case.

CREATE FUNCTION myFunction ...

CREATE FUNCTION MyFuNcTiOn ...

CREATE FUNCTION MYFUNCTION ..

CREATE FUNCTION myfunction ...

will all create a function that, in the data dictionary, is named MYFUNCTION in all upper case. You are free to call the function using whatever casing makes sense to you since it is a case insensitive identifier. The database has no idea what casing you used to create the function so it cannot possibly affect performance.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Any links containing these issues? I'm interested to know what can go wrong by doing that. – Roger Jun 25 '13 at 19:43
  • @Roger - Added a link to another SO thread that discusses case sensitive vs case insensitive identifiers in Oracle. – Justin Cave Jun 25 '13 at 19:46
  • @Roger: this is documented in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27561 See item #9 in the list of rules for identifiers. –  Jun 25 '13 at 19:51
  • I would add that the case of the function/procedure name has absolutely no effect on performance. – GriffeyDog Jun 25 '13 at 20:13
2

The only reason for you to use only uppercase entity names (not longer, then 30 characters, by the way) is to avoid using quotes, when accessing them. I haven't ever heard of any performance impact though.

If you name something FUNC, you will be able to call it fUnC or Func (without quotes!) later.

But if you call it func with quotes, that will be the only name you may use.

David Jashi
  • 4,490
  • 1
  • 21
  • 26