Use Oracle 10g, Number, Char, and Varchar2 as primary keys to analyze query efficiency.

Background in actual projects, the primary key generation mechanism for database tables is designed with multiple options: Sequence, product auto-growth, table auto-growth, UUID, and composite primary key. Considering the simplicity of primary keys and query, we do not recommend that you use a composite primary key. UUID is preferred in terms of data table reconstruction and data migration convenience. However, if UUID is used, character type fields must be used. There are concerns that the query efficiency of character-type primary keys is far less than that of numeric primary keys. In addition, it is pointed out that this index is not used when you create an index for the Varchar2 field. After searching related articles on the Internet, there is no credible evidence. Therefore, we are self-reliant and looking for verification solutions. The www.2cto.com scheme is designed to create three tables with the same structure, respectively using the Number, Char, and Varchar2 types as the primary key (the Number type primary key value is generated in Sequence mode, char and Varchar2 primary key values are generated using the GUID class. This test only tests the database performance. Therefore, all operations are performed using SQL and PLSQL. Perform the following operations and record the execution time: 1. Each table has 1 million records. 2. sort by primary key, extract the primary keys of 50th million records, and perform primary key query. Create a table at www.2cto.com -- Number Primary Key
Create table B2C _pk_number
(
Id number (15 ),
Name varchar2 (32)
) Tablespace ecsdata2;
Alter table B2C _pk_number
Add constraint pk_pk_number primary key (id)
Using index tablespace ecsindex2;
-- Char primary key
Create table B2C _pk_char
(
Id char (32 ),
Name varchar2 (32) tablespace ecsdata2;
Alter table B2C _pk_char
Add constraint pk_pk_char primary key (id)
Using index tablespace ecsindex2;
-- Varchar2 primary key
Create table B2C _pk_varchar2
(
Id varchar2 (32 ),
Name varchar2 (32) tablespace ecsdata2;
Alter table B2C _pk_varchar2
Add constraint pk_pk_varchar2 primary key (id)
Using index tablespace ecsindex2; insert 1 million records -- Number type primary key declare
Vanderbilt Total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Insert into B2C _pk_number (id, name) values (idx, sys_guid ());
 
-- Submit every 10 thousand
If (mod (idx, 10000) = 0) then
-- Dbms_output.put_line (idx );
Commit;
End if;
End loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; -- Char primary key declare v_total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Insert into B2C _pk_char (id, name) values (sys_guid (), sys_guid ());
 
-- Submit every 10 thousand
If (mod (idx, 10000) = 0) then
-- Dbms_output.put_line (idx );
Commit;
End if;
End loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; -- Varchar2 type primary key declare
Vanderbilt Total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Insert into B2C _pk_varchar2 (id, name) values (sys_guid (), sys_guid ());
 
-- Submit every 10 thousand
If (mod (idx, 10000) = 0) then
-- Dbms_output.put_line (idx );
Commit;
End if;
End loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; Result

Number type primary key Char type primary key Varchar2 type primary key execution time (unit: seconds) 71.516642000 93.747191000 97.184995000 correction considering that newly added records of Number type primary keys call less than one sys_guid (), and, sequence is used in the actual system. Therefore, make the following adjustments: 1. Create Sequencecreate sequence seq_pk_number; 2. Clear the B2C _pk_number table truncate table B2C _pk_number; 3. Modify the plsql statement of the new record and execute it again. Declare
Vanderbilt Total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Insert into B2C _pk_number (id, name) values (seq_pk_number.nextval, sys_guid ());
-- Submit every 10 thousand
If (mod (idx, 10000) = 0) then
-- Dbms_output.put_line (idx );
Commit;
End if;
End loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; Corrected Results
Number type primary key Char type primary key Varchar2 type primary key execution time (unit: seconds) 78.962285000 93.747191000 97.184995000 total time for querying million records-Number type primary key declare
Vanderbilt Total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Execute immediate 'select * from B2C _pk_number where id = 100 ';
End loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; -- select * from (select t. *, row_number () over (order by id) rn from B2C _pk_char t) where rn = 500000; declare
Vanderbilt Total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Execute immediate 'select * from B2C _pk_char where id = ''d4f1204d823f706be0440018fe2db7d7d7'''; end loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; -- select * from (select t. *, row_number () over (order by id) rn from B2C _pk_varchar2 t) where rn = 500000; declare
Vanderbilt Total number: = 1000000;
V_starttime timestamp;
V_endtime timestamp;
Begin
V_starttime: = policimestamp;
For idx in 1 .. v_total
Loop
Execute immediate 'select * from B2C _pk_varchar2 where id = ''d4f1a8cc22be55ade0440018fe2db7d7d7'''; end loop;
 
V_endtime: = policimestamp;
 
Dbms_output.put_line (v_endtime-v_starttime );
End; Result Number type primary key Char type primary key Varchar2 type primary key execution time (unit: seconds) 31.926289000 24.500471000 24.247383000 execution plan -- insert into B2C _pk_number (id, name) values (: 1, sys_guid (); insert statement, GOAL = ALL_ROWS 1 100 0 1 ALL_ROWS 1 -- insert into B2C _pk_char (id, name) values (sys_guid (), sys_guid ()); insert statement, GOAL = ALL_ROWS 1 100 0 1 ALL_ROWS 1 -- insert into B2C _pk_varchar2 (id, name) values (sys_guid (), sys_guid (); INSERT STATEMENT, GOAL = ALL_ROWS 1 1 100 0 1 ALL_ROWS 1 -- select * from B2C _pk_number where id = 500000; SELECT STATEMENT, GOAL = ALL_ROWS 1 1 31 15463 1 ALL_ROWS 1
Table access by index rowid ccbpd B2C _PK_NUMBER 1 1 1 31 15463 1 1
Index unique scan ccbpd PK_PK_NUMBER 1 1 8171 "ID" = 500000 1 1 -- select * from B2C _pk_char where id = 'clerk'; SELECT STATEMENT, GOAL = ALL_ROWS 1 1 1 52 15463 1 ALL_ROWS 1
Table access by index rowid ccbpd B2C _PK_CHAR 1 1 1 52 15463 1 1
Index unique scan ccbpd PK_PK_CHAR 1 1 8171 "ID" = 'd4f1204d823f706be0440018fe2db7d7 '1 1 -- select * from B2C _pk_varchar2 where id = 'hangzhou'; SELECT STATEMENT, GOAL = ALL_ROWS 1 1 36 15463 1 ALL_ROWS 1
Table access by index rowid ccbpd B2C _PK_VARCHAR2 1 1 36 15463 1 1
Index unique scan ccbpd PK_PK_VARCHAR2 1 1 8171 "ID" = 'd4f1a8cc22be55ade0440018fe2db7d7 '1 www.2cto.com conclusion from the above results, the efficiency of adding Record Number Primary keys is higher than that of Char and Varchar2 primary key types, while query is the opposite. Of course, the above analysis only involves simple addition and query analysis, and also requires complex tests (complex queries and real application environments ).