Oracle char number varchar2 Efficiency Test

The field number is used when you create a table,

The main problem is to use java to connect to the database, so there must be a pojo class,

Like the number field, int Is Not Enough, long is difficult to convert, and it is prone to errors.

Suddenly I want to see whether char can be used, so I am confused to see how efficient these data types are to be searched.

-- First, create a table (if there are duplicates, delete it first ...)
Drop table yard_test;
Create table yard_test (
Yrd_id char (32 ),
Yrd_num number,
Yrd_var varchar2 (32 ),
Performance_time date,
Yrd_txt varchar2 (4000)
);

-- The required data type must be char number varchar2

-- You can insert data below. Create a plsql statement block and execute it.
Declare
V_temp yard_test % rowtype;
V_begin_time timestamp: = systimestamp;
V_end_time timestamp;
V_all_time timestamp;
Begin
For I in 1 .. 100 loop
-- Each 10 thousand data records are submitted once, and a total of 1 million data records are submitted. In fact, there are a lot of requests. I don't have the patience to wait until he inserts the data ......
For j in 1 .. 10000 loop
V_temp.yrd_id: = sys_guid ();
V_temp.yrd_var: = sys_guid ();
Select nvl (max (yrd_num), 0) + 1 into v_temp.yrd_num from yard_test;
-- Sequence should have been used. It is too troublesome. We can use the select statement to obtain the max value + 1 and get the sequence number...
V_temp.yrd_txt: = substr (sys_guid (), 1, 1 );
Insert into yard_test values (
V_temp.yrd_id,
V_temp.yrd_num,
V_temp.yrd_var,
Systimestamp,
V_temp.yrd_txt );
V_end_time: = policimestamp;
V_all_time: = v_all_time + (v_end_time-v_begin_time );
-- Insert data
End loop;
Commit;
Dbms_output.put_line (v_all_time );
End loop;
Dbms_output.put_line (v_all_time );
End;

-- Okay. Let's see what data has been inserted,
Select * from yard_test;

-- Start the test below
Declare
V_beg timestamp; -- remember the start time
V_end timestamp; -- and End Time
Vanderbilt times number: = 100000;
Begin
-- Test the speed of searching 50,000th data records
V_beg: = policimestamp;
For I in 1 .. v_times loop
Execute immediate 'select * from yard_test where yrd_num = 100 ';
-- Run the SQL statement directly with execute. Here we use number to find data.
End loop;
V_end: = policimestamp;
Dbms_output.put_line ('Number query time' | (v_end-v_beg ));

V_beg: = policimestamp;
For I in 1 .. v_times loop
Execute immediate 'select * from yard_test where yrd_id = ''4a6585c231b64bd1b5a981edd8079990 ''';
End loop;
V_end: = policimestamp;
Dbms_output.put_line ('Char query time' | (v_end-v_beg ));

V_beg: = policimestamp;
For I in 1 .. v_times loop
Execute immediate 'select * from yard_test where yrd_var = ''18C00CA4677E4BF388BD9F3891BBACA0 ''';
End loop;
V_end: = policimestamp;
Dbms_output.put_line ('carchar2 query time' | (v_end-v_beg ));
End;

In fact, I also found other tests on the Internet, and the conclusions are the same...

Char is the most efficient, number is the second, and the slowest is varchar2, which is not big and acceptable.