[DB] [oarcle] generates the guid type-sys_guid () function in Oracle

Generate the guid type in Oracle -- sys_guid () function

 

Oracle8i introduces sys_guid, which has many advantages over the traditional sequence used by Oracle administrators. A sequence generator simply creates a series of integers starting from a given starting point, and is used to automatically increment the series when selecting the declarative.

 

The number generated by the sequence generator can only be unique in a single instance, which is not suitable for using it as the primary keyword in the parallel or remote environment, the sequence in the environment may generate the same number, resulting in a conflict. Sys_guid makes sure that the identifier it creates is unique in each database.

 

In addition, the sequence must be part of the DML statement, so it requires a round-trip process to the database (otherwise it cannot guarantee that its value is unique ). Sys_guid is derived from the timestamp and machine identifier that do not need to access the database, which saves the query consumption.

 

Create Table use_seq_table (ID integer ); Create sequence use_seq_sequence; Insert into use_seq_table values (use_seq_sequence_value.nextval ); REM-for some reason, the documentation uses raw (32) Create Table use_guid_table (ID raw (16 )); Insert into use_guid_table (sys_guid ());

 

Many applications rely on the sequence generator to create the primary Keywords of data rows. These data rows do not have an obvious primary value. That is to say, the creation of a record in such a dataset changes the data column. Therefore, the Administrator may be interested in using sys_guid as the primary keyword in the table without using sequence numbers. This is useful when objects are generated in different databases of different machines and need to be merged later.

 

However, the value generated by sys_guid is a 16-bit original value. The integer generated by the sequence does not use 16 bits (value) unless it reaches the 10th power of 30 (each byte has two digits), and the number is quite unique:

 

SQL & gt; select dump (123456789012345678901234567890) from dual; Dump (123456789012345678901234567890) -------------------------------------------------------------- Typ = 2 Len = 16: 207,13, 35,57, 79,91, 13,35, 57,79, 91,13, 35,57, 79,91

 

Shorter values mean less storage space for tables and indexes, and shorter query access time.

 

Using sys_guid or sequence may cause performance consumption in some places in the database usage cycle; the problem is where. For sys_guid, the performance impact is on the query time and creation time (more blocks and indexes should be created in the table to accommodate data ). For the sequence, the performance impact during the query, at this time, the SGA sequence buffer is used up. By default, a sequence will buffer 20 values at a time. If the database is disabled without these values, they will be lost.

 

Another notable disadvantage of sys_guid generated values is that managing these values is much more difficult. You must (manually) enter them, fill them with scripts, or pass them as Web parameters.

 

For these reasons, it is not a good idea to use sys_guid as a primary keyword unless it is in a parallel environment or if you want to avoid using a management sequence generator.

 

Here, we do not discuss the advantages of guid and sequencer, and each has requirements in a specific environment. In Oracle9i and Oracle 10g, sys_guid generates 32-bit data, for example, 234e45f0077881aae0430aa301_1aa.

 

The function I want to do here is to split the guid into the objectid format in Windows: {8-4-4-4-12}. The following two solutions are provided:

 

Method 1: Use substr for segmentation. The Code is as follows:

 

/** * Create a system object ID string and return the following result: {234e45f0-077a-81aa-e043-0aa301_1aa} */ Function createguid return varchar2 Is Guid varchar (64 ); Begin Guid: = sys_guid (); Return '{' | Substr (guid, 1, 8) | '-' | substr (guid, 9, 4) | '-' | Substr (guid, 13, 4) | '-' | substr (guid, 17, 4) | '-' | Substr (guid,) | '}'; End createguid;

Method 2: Connect using the Concat function. The Code is as follows:

 

Create or replace function get_guid Return char Is V_guid char (36 ); V_guid_part_one char (8 ); V_guid_part_two char (4 ); V_guid_part_three char (4 ); V_guid_part_four char (4 ); V_guid_part_five char (12 ); Begin Select sys_guid () Into v_guid From dual; V_guid_part_one: = substr (v_guid, 0, 8 ); V_guid_part_two: = substr (v_guid, 8, 4 ); V_guid_part_three: = substr (v_guid, 12, 4 ); V_guid_part_four: = substr (v_guid, 16, 4 ); V_guid_part_five: = substr (v_guid, 20, 12 ); V_guid: = Concat (Concat (Concat (Concat (v_guid_part_one, '-'), V_guid_part_two ), '-' ), V_guid_part_three ), '-' ), V_guid_part_four ), '-' ), V_guid_part_five ); Return (v_guid ); End get_guid;

 

Generate guid type in Oracle

 

Source: http://www.cnblogs.com/dongqi/archive/2008/10/13/1310185.html