Re: Storing GUIDs in Oracle

On Wed, 23 Jul 2003 16:40:29 -0400, "Jeff Boenig" <jeff.boenig_at_elsitech.com> wrote:

>Thank you very much. You're feedback is very constructive.
>
>So are you're saying that using a RAW(16) column as a primary key would
>adversely affect performance? Would it cause other problems too?
>
>Thanks!
>

As long as you primarily take into account, you will only have native RAWs when using pl/sql and *hence* you the appropiate conversion function (HEXTORAW) when your variable is a varchar2, nothing should go wrong.
(So you are using <primary key column> = HEXTORAW(<your varchar2)

However, when you have end-users or other applications firing ad-hoc queries you may well run into implicit conversion. The implicit conversion always comes down to

RAWTOHEX(<primary key column) = <your varchar2 or literal>

which you will dump you in a full table scan.

One of the consequences also is: when you run tkprof on the app, tkprof will come up with incorrect explain plans,as tkprof treats your literal or your bind variable as a varchar2. tkprof (and other tools) don't know what RAWs are.

One other problem I have in a specific app is : all primary keys are RAWs. The RAWs do not contain any meaningful info. I would like to partition those tables, as they are *huge*, but I can't do anything, because the range of my RAWs.

IMO: this means as far as I am concerned RAW(16) is a big pain in the butt and should be avoided at all costs.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jul 23 2003 - 16:39:29 CDT