Sys_guid () is a function provided after Oracle 8i. Sys_guid is generated and returns a globally unique identifier (original value) consisting of 16 bytes. On most platforms, the generated identifier consists of the host identifier, the process or thread identifier that executes the function, and a non-repeated value (byte sequence) of the process or thread.
Directory
-
Sys_guid () and traditional sequence (sequence)
-
Performance Comparison
-
Expand
-
Sys_guid () and traditional sequence (sequence)
-
Performance Comparison
-
Expand
Edit this section sys_guid () and the traditional sequence Oracle8i introduce the sys_guid concept, 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. A 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.
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-byte original value. The integer generated by the sequence does not use 16 bytes (value) unless it reaches the 10th power of 30 (each byte has two hexadecimal digits), and the number is quite unique:
SQL> select dump (123456789012345678901234567890) from dual; dump (123456789012345678901234567890) using typ = 2 Len = 16: 207,13, 35,57, 79,91, 13,35, 57,79, 91,13, 35,57, using sys_guid or sequence may cause performance consumption in some places in the database use cycle; the problem is there. 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. Edit this section to create the following objects for performance comparison: Create Table TSG as select rawtohex (sys_guid () SGID,. * From all_objects A; Create sequence seq_tsg; Create Table tsg2 as select seq_tsg.nextval,. * From all_objects A; compare the current two tables: TSG and tsg2 have the same number of rows, but different sizes:
| Table |
Number of rows |
Number extents |
Size in bytes |
Index size |
| TSG (sys_guid primary key) |
50231 |
23 |
8388608 |
3145728 |
| Tsg2 (sequence primary key) |
50231 |
21 |
6291456 |
917504 |
In other words, if sys_guid is used as the primary key under the same conditions, the table consumes 2097152 bytes more space, the index consumes 2228224 bytes more, and the average consumption of 86.1 bytes for each row. taking into account the production environment, 50 thousand records per day, 365*50000 = 18250000 records per year, it is theoretically required to consume about 1.43 GB of storage space. these spaces are negligible for disk consumption and still have a certain impact on the memory, but the impact on the current server capacity is limited. If a table is reasonably partitioned, this impact can be minimized. Comparison of execution plans for unique queries: Comparison of TSG execution: Select ownerfrom tsgwhere SGID = 'f36c09b7a7a84297995352d2409eb40e 'on tsg2: Select ownerfrom tsg2where SGID = 99: according to the above statistics, the execution plan is the same. It is expected that because sys_guid is used as the primary key and compared with a string, the CPU usage is higher. Therefore, logical reads is higher, and physical readers is lower, I don't know the reason (in fact, neither of them generates a large number of physical reads). It is estimated that my test environment DB
The cache is too small. for the response time, this should be the impact of the computer environment. The problem cannot be described. The two statements respond quickly, less than 0.02 seconds. summary in practice, using sys_guid () for primary keys has more advantages than negative effects. Guid has obvious advantages, especially when data is integrated into multiple databases. project A does not use the customer-defined "unique sequence number of the bill of lading" as the primary key. It is also out of the rule of the relational database design: "primary keys do not represent any meaning ".
