well, GUIDS when stored would be stored in RAW fields - they would be 16 bytes, not 32, when stored.

You would use:

where trans_id = hextoraw( :x );

and you would select:

select rawtohex(trans_id) ...


if you want to use "strings" in your application (reasonable to do)

Sequences, if set to a large enough cache size (cache defaults to 20, inadequate for many high volume applications, a setting of a 1,000 or more makes great sense) are used by most all high volume Oracle applications.

One of the downsides will be that they are a monotonically increasing number - hence the (unique) index on them will be very "hot" on the right hand side. This can be offset by

o hash partitioning (so you have 8, 16, 32, 64, ... "hot" right hand sides)
o reverse key indexes (so you spread the data out in the entire index structure)


GUIDS will be somewhat randomly distributed and will not cause a "hot" right hand side index. They will always be 16bytes (we use them in our nested table implementation - the parent table has a 16byte raw column added for each nested table type it includes and the child table has as part of it's structure this same 16byte raw)


I'm not sure which you say "require more latches". Your second to last paragraph was not clear which you thought took more



Table created.

ops$tkyte%ORA10GR2> create table t2 ( x number primary key, data char(80) );

Table created.

ops$tkyte%ORA10GR2> create sequence s cache 1000;

Sequence created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> define n=100000
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> begin
2 for i in 1 .. &n
3 loop
4 insert into t1 (x,data) values (sys_guid(),'x');
5 commit;
6 end loop;
7 end;
8 /
old 2: for i in 1 .. &n
new 2: for i in 1 .. 100000

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> begin
2 for i in 1 .. &n
3 loop
4 insert into t2 (x,data) values (s.nextval,'x');
5 commit;
6 end loop;
7 end;
8 /
old 2: for i in 1 .. &n
new 2: for i in 1 .. 100000

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 2499 hsecs
Run2 ran in 2759 hsecs
run 1 ran in 90.58% of the time

Name Run1 Run2 Diff
STAT...messages sent 30,027 41,046 11,019
LATCH.lgwr LWN SCN 30,058 41,081 11,023
LATCH.Consistent RBA 30,050 41,110 11,060
LATCH.mostly latch-free SCN 30,275 41,411 11,136
LATCH.messages 61,497 84,489 22,992
LATCH.redo allocation 283,917 316,909 32,992
LATCH.redo writing 90,132 123,202 33,070
STAT...calls to get snapshot s 101,365 201,226 99,861
LATCH.library cache pin 58 200,135 200,077
LATCH.sequence cache 0 300,200 300,200
LATCH.library cache 100,213 400,577 300,364
STAT...IMU Redo allocation siz 2,115,812 1,712,344 -403,468
STAT...IMU undo allocation siz 67,228,188 66,388,100 -840,088
STAT...undo change vector size 26,828,460 24,345,060 -2,483,400
STAT...redo size 94,087,692 88,202,304 -5,885,388

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
6,104,556 7,022,494 917,938 86.93%

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_pkg
2 as
3 type rawArray is table of t1.x%type index by binary_integer;
4 type numArray is table of t2.x%type index by binary_integer;
5
6 raw_data rawArray;
7 num_data numArray;
8 end;
9 /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 select x bulk collect into my_pkg.raw_data from t1;
3 select x bulk collect into my_pkg.num_data from t2;
4 end;
5 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
2 l_rec t1%rowtype;
3 begin
4 for i in 1 .. my_pkg.raw_data.count
5 loop
6 select * into l_rec from t1 where x = my_pkg.raw_data(i);
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
2 l_rec t2%rowtype;
3 begin
4 for i in 1 .. my_pkg.num_data.count
5 loop
6 select * into l_rec from t2 where x = my_pkg.num_data(i);
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 420 hsecs
Run2 ran in 394 hsecs
run 1 ran in 106.6% of the time

Name Run1 Run2 Diff
STAT...session pga memory max 131,072 65,536 -65,536
STAT...session uga memory max 123,452 0 -123,452
STAT...session pga memory 65,536 -196,608 -262,144

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
329,048 326,257 -2,791 100.86%

PL/SQL procedure successfully completed.


And in a RAC environment - you could use reverse key indexes or hash partitioning to "smear the data around" (reverse key indexes probably). I wrote about that in "Expert Oracle Database Architecture" in the indexing chapter.