To GUID or not to GUID, on Oracle …

I keep running into that issue when I design a database, this time around it’s an Oracle schema, thus I need fresh input on GUIDs and Oracle. It’s basically the old story, if distribution and replication is a serious feature, a GUID would be very welcome. We could also create a hybrid monster, take a numeric primary key and supplement a GUID field for distribution purposes, sure it takes up additional space …

Arguments for the use of GUIDs

- Makes distribution and replication a lot easier.
- ID’s can be created in the application, thus no ID would need to fetched from a DB insert.
- Creating ID’s on the “client” side enables working “offline”.
- Enabled (dirty?) cross-table references, e.g. a table which contains website url’s could contain a generic “ID” column which contains id’s from the “books” table or “articles” table. This also applies for class models ofcourse.
- Security (by obscurity) in urls.
- don’t have to create those (annoying) sequences with triggers in Oracle :)

Arguments against the use of GUIDs

- Performance (will depend on which database, e.g. Oracle also has sequential GUIDs and MS-Sql has a native guid type).
- Universally small chance that it’s not unique.
- Looks ugly (heard that a LOT)
- It has a Microsoft feel to it :)

Well, Google also supplies some nice information about the performance, reasons to use (or not) and options:

[1] How should I store a Guid in Oracle
[2] Unique IDs for multi-master replication – Sequence or SYS_GUID?
[3] Watch out for sequential Oracle GUIDs!
[4] UUIDs as primary keys

- Probable performance loss of 40% when using a CHAR / RAW(16) instead of an integer. [1] (Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string, thus CHAR instead of VARCHAR2, plus the length of a GUID is fixed anyway)
Whereas in VARCHAR the system has to first find the end of string and then go for searching.
- SYS_GUID is sequential because random GUIDs play hell with indexes. SQL Server has a sequential GUID generator as well for the same reason.
- On SQL Server, the base datatype of a Guid is actually a binary(16) (equivalent to Raw(16) on Oracle). But when the Guid value is marshalled into the binary, the bytes are re-ordered!
- You can uses sys_guid() as a default column so that it is automatically populated. CREATE TABLE guid_table (pky RAW(16) default sys_guid() PRIMARY KEY, NAME VARCHAR2(100));
- Using UUIDs in URLs is more secure. [4]
- A nice function to format the SYS_GUID() [3] :

CREATE OR REPLACE FUNCTION GET_FORMATTED_GUID RETURN VARCHAR2 IS guid VARCHAR2(38) ;
BEGIN
SELECT SYS_GUID() INTO guid FROM DUAL ;

guid :=
'{' || SUBSTR(guid,  1, 8) ||
'-' || SUBSTR(guid,  9, 4) ||
'-' || SUBSTR(guid, 13, 4) ||
'-' || SUBSTR(guid, 17, 4) ||
'-' || SUBSTR(guid, 21) || '}' ;

RETURN guid ;
END GET_FORMATTED_GUID ;
/

/*
CREATE TABLE "TEST_GUID_FORMATTED"
(  "GUID_FORMATTED" CHAR(36),
"NAME" NVARCHAR2(50),
CONSTRAINT "PK_GUID_FORMATTED" PRIMARY KEY ("GUID_FORMATTED"
)</code>

truncate table test_guid_formatted;
commit;
*/

declare
stopwatch date;
begin
stopwatch := sysdate;
for counter in 1..1000000 loop
insert into test_guid_formatted (guid_formatted, name)
values (get_formatted_guid, 'test: ' || to_char(counter));
end loop;
dbms_output.put_line(to_char(sysdate - stopwatch));
commit;
end;

Create a custom Oracle aggregate function that accepts multiple parameters through parameter encapsulation!

Why would you want a feature like this?

Scenarios:
- You want to group by but treat a specific value to overrule no matter what else is specified it would look like:

select id, strpref(string_prefered_pars_type(status, status, ‘active’)) from tablename group by id;
(prefer the ‘active’ value above all else)

- The value has a dependency on another column which looks like:

select id, strpref(string_prefered_pars_type(return_column, pref_dependency_column, ‘active’)) from tablename group by id;
(prefer the value from a record which has its pref_dependency_column set to ‘active’)

Naturally with some code changes it enables all kind of fun aggregation features!

Everything below the line is Oracle PL/SQL example code which demonstrates how a custom aggregate function can be created which accepts multiple parameters by encapsulating the parameters in a custom type.

/*
quote:
There is no support for writing custom aggregate functions that accept multiple parameters.

source:
Build Custom Aggregate Functions
By Jonathan Gennick
http://www.oracle.com/technology/oramag/oracle/06-jul/o46sql.html

This example uses a custom type to use a custom aggregate function that accepts multiple parameters through parameter encapsulation!

more references:

Oracle® Database Data Cartridge Developer's Guide, 10g Release 2 (10.2)
11 User-Defined Aggregate Functions
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm
*/

create or replace type STRING_PREFERED_PARS_TYPE as object
(
-- Author  : O. Lissenberg
-- Created : 4-11-2009
-- Purpose : Passes parameters to the STRING_PREFERED_TYPE's ODCIAggregateIterate function

-- Attributes
strvalue varchar2(100),
pref varchar2(10),
prefvalue varchar2(10)

-- Member functions and procedures
-- N.a.
)
/
create or replace type string_prefered_type as object
(
retvalue varchar2(4000),

static function ODCIAggregateInitialize(sctx IN OUT string_prefered_type) return number,
member function ODCIAggregateIterate(self IN OUT string_prefered_type, value IN STRING_PREFERED_PARS_TYPE) return number,
member function ODCIAggregateTerminate(self IN string_prefered_type, returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT string_prefered_type, ctx2 IN string_prefered_type) return number
);
/
create or replace type body string_prefered_type is
static function ODCIAggregateInitialize(sctx IN OUT string_prefered_type)
return number is
begin
sctx := string_prefered_type(null);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_prefered_type, value IN STRING_PREFERED_PARS_TYPE)
return number is
begin

IF self.retvalue is null THEN
self.retvalue := value.strvalue;
ELSIF value.pref is not null AND lower(value.pref) = lower(value.prefvalue) THEN
-- only change the return value if the prefered value is found
-- hence otherwise the behavior is "the first non null value is accepted and does not change", which
-- might be nice to change into a max() or min() feature.
self.retvalue := value.strvalue;
END IF;

return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_prefered_type, returnValue OUT varchar2, flags IN number)
return number is
begin
-- just return the set value.
returnValue := self.retvalue;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_prefered_type, ctx2 IN string_prefered_type)
return number is
begin
-- this might prove to be difficult, it's easier to disable PARALLEL execution
-- to enable though, a state needs to be added to enable the merge, the state
-- would flag if the current or other context value was set with a "prefered" value.

return ODCIConst.Success;
end;
end;
/
CREATE or replace
FUNCTION strpref(input STRING_PREFERED_PARS_TYPE)
RETURN varchar2
AGGREGATE USING string_prefered_type;
-- PARALLEL_ENABLE (to enable: implement the merge function)
/
select t.id ,strpref(STRING_PREFERED_PARS_TYPE(t.name, t.pref, 'pickme'))
from testgroupby t group by t.id;

/*
CREATE TABLE "TESTGROUPBY"
(  "ID" NUMBER,
"NAME" NVARCHAR2(10),
"PREF" NVARCHAR2(10) );

insert into TESTGROUPBY (id,name,pref) values (1,'test1','ignoreme');
insert into TESTGROUPBY (id,name,pref) values (1,'test2','pickme');
insert into TESTGROUPBY (id,name,pref) values (1,'test3','ignoreme');
insert into TESTGROUPBY (id,name,pref) values (2,'test4','pickme');
insert into TESTGROUPBY (id,name,pref) values (2,'test5','pickme');
insert into TESTGROUPBY (id,name,pref) values (2,'test6','ignoreme');

select t.id ,strpref(STRING_PREFERED_PARS_TYPE(t.name, t.pref, 'pickme'))
from testgroupby t group by t.id;

drop table TESTGROUPBY;
*/

Fetch Oracle NLS Settings, Install Oracle Full Client in Zend Server.

Note that these results were not the ones I’d hoped for, the result was the installation of the full Oracle Instant client in PHP instead of the light version.

SELECT * FROM NLS_DATABASE_PARAMETERS;

To replace the light with the full client in Zend Server, put the “oraociei11.dll” (full) into the __ZendServerInstallPath__\ZendServer\bin directory and remove or rename the “oraociicus11.dll” (light). These libraries can be found in oracle client installation.

For a Linux environment it might be required to set the following environment variables:

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export DNLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export LC_ALL=AMERICAN_AMERICA.WE8ISO8859P1

In Windows it might be necessary to set the registry entry:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\NLS_LANG = DUTCH_THE NETHERLANDS.WE8MSWIN1252
(or HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient10g_home\NLS_LANG)

to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1