Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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