Performance Analysis of Oracle primary key and composite primary key

Performance Analysis of Oracle primary keys and composite primary keys. Primary keys and composite primary keys have the same query performance (the same index height and the appropriate use of indexes ). Primary Key and composite primary key, (update, in

Performance Analysis of Oracle primary keys and composite primary keys. Primary keys and composite primary keys have the same query performance (the same index height and the appropriate use of indexes ). Primary Key and composite primary key, (update, in

Summary:
1. Primary keys and composite primary keys have the same query performance (the same index height and the appropriate use of indexes ).
2. Primary keys and composite primary keys. The (update, insert) performance is different (because composite primary keys use more blocks to create indexes, the update and insert performance is low)

Tutorial ideas:
1. Create an experiment table and a primary key. Combine two primary keys and three primary keys.
2. view the index structure
3. View execution plans with the same conditions (to determine the efficiency of primary keys and composite primary keys)


1. Create an experiment table. test1 is a single primary key with one column, test2 is the Union primary key with two columns, and test3 is the Union primary key with three columns.
SQL> create table test1 (a number, B number, c number, primary key ());

Table created.

SQL> create table test2 (a number, B number, c number, primary key (a, B ));

Table created.

SQL> create table test3 (a number, B number, c number, primary key (a, B, c ));

Table created.

2. view the index structure
1. First, check the index corresponding to the created table.
SQL> select index_name, table_name from user_indexes;

INDEX_NAME TABLE_NAME
------------------------------------------------------------
SYS_C005198 TEST1
SYS_C005199 TEST2
SYS_C005200 TEST3

2. Write a stored procedure to insert data to the experiment table.
Begin
For I in 1 .. 10000 loop
Insert into test1 values (I, I + 1, I + 2 );
Commit;
End loop;
End;


Test1
SQL> analyze index SYS_C005198 validate structure;

Index analyzed.

SQL> select HEIGHT, BLOCKS, BR_BLKS, LF_BLKS, LF_ROWS, DEL_LF_ROWS from index_stats;

Height blocks BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
-------------------------------------------------------------
2 24 1 18 10000 0
Test2
SQL> analyze index SYS_C005199 validate structure;

Index analyzed.

SQL> select HEIGHT, BLOCKS, BR_BLKS, LF_BLKS, LF_ROWS, DEL_LF_ROWS from index_stats;


Height blocks BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
-------------------------------------------------------------
2 32 1 23 10000 0
Test3
SQL> analyze index SYS_C005200 validate structure;

Index analyzed.

SQL> select HEIGHT, BLOCKS, BR_BLKS, LF_BLKS, LF_ROWS, DEL_LF_ROWS from index_stats;

Height blocks BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
-------------------------------------------------------------
2 40 1 28 10000 0

Summary: according to the structure characteristics of B-TREE index. It indicates that the primary key and the federated primary key require more resources to maintain the index. (Because the Union primary key index uses more blocks, the update and insert operations are slower than the primary key index. As for querying the following research)

3. Check the efficiency of the primary key in the same case.

1. All statements let them go through index unique scan to see the efficiency:


Test1
SQL & gt; select a from test1 where a = 5555;

A
----------
5555


Execution Plan
----------------------------------------------------------
Plan hash value: 2716871853

--------------------------------------------------------------------------------
-

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time
|

--------------------------------------------------------------------------------
-

| 0 | select statement || 1 | 13 | 1 (0) | 00:00:01
|

| * 1 | index unique scan | SYS_C005198 | 1 | 13 | 1 (0) | 00:00:01
|

--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id ):
---------------------------------------------------

1-access ("A" = 5555)


Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
2 consistent gets
0 physical reads
0 redo size
405 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Test2
SQL> select a, B from test2 where a = 5555 and B = 5556;

A B
--------------------
5555 5556


Execution Plan
----------------------------------------------------------
Plan hash value: 3210951477

--------------------------------------------------------------------------------
-

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time
|

--------------------------------------------------------------------------------
-

| 0 | select statement || 1 | 26 | 1 (0) | 00:00:01
|

| * 1 | index unique scan | SYS_C005199 | 1 | 26 | 1 (0) | 00:00:01
|

--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id ):
---------------------------------------------------

1-access ("A" = 5555 AND "B" = 5556)


Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
2 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Test3
SQL> select a, B, c from test3 where a = 5555 and B = 5556 and c = 5557;

A B C
------------------------------
5555 5556 5557


Execution Plan
----------------------------------------------------------
Plan hash value: 1852305570

--------------------------------------------------------------------------------
-

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time
|

--------------------------------------------------------------------------------
-

| 0 | select statement || 1 | 39 | 1 (0) | 00:00:01
|

| * 1 | index unique scan | SYS_C005200 | 1 | 39 | 1 (0) | 00:00:01
|

--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id ):
---------------------------------------------------

1-access ("A" = 5555 AND "B" = 5556 AND "C" = 5557)


Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
2 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Analysis: When the SQL statement is executed using the INDEX UNIQUE SCAN INDEX, the results of the analysis are as follows:

(What we pay attention:
1 recursive cballs
0 db block gets
2 consistent gets
0 physical reads
0 redo size
The consumption is the same as that of COST .)

Conclusion: when the primary key and joint primary key are applied with the B-tree index, if the index height is the same and the index is correctly applied. In this case, the query performance is the same.


You are welcome to correct the mistakes and improve them together!