Oracle notes (6), table management (constraints, indexes, locks, and table partitions)

I. Table integrity and constraints

Entity Integrity: primary key constraints
Domain integrity
Integrity of reference: foreign key constraints

Select constraint_name, constraint_type from user_constriants where table_name = 'emp ';

2. Manage Indexes
Indexes are a storage structure built on table fields. Using indexes can speed up table queries. When an index key column is used in the WHERE clause of an SQL statement, the index directly points to the location containing these worthwhile rows. the proper use of the index is the main method to reduce disk I/O, it only affects the execution speed.

When an index is created, Oracle obtains and sorts the columns for which the index is to be created. Store the rowid together with the index value of each row. In use, Oracle performs a quick search using sorted index values, and then uses the associated rowid value to locate the record rows to be searched.

Once an index is created, Oracle automatically maintains and uses it. The index is automatically updated when the row record is updated. Therefore, creating too many indexes for the table reduces the table performance.

Obtain the index user_indexes view.

The performance of rebuilding an existing index is better than that of deleting and recreating an index. Alter index item_index rebuild;


2.0. Traditional B-tree indexes:
Create index ix_mm on mm (M1 );

2.1 unique index:The unique constraint is the unique index, but the unique index is not necessarily the unique constraint.
Create unique index myidx on mm (m2 );

2.2. combined index:The index created on multiple columns in the table. If the WHERE clause in the SELECT statement references all or most columns in the composite index, the composite index can increase the data retrieval speed. Generally, columns that are most frequently accessed during index creation should be placed at the beginning of the list.
Create index comp_index on itemfile (p_category, itemrate );

2.3 reverse key index:Special indexes are very useful when indexed columns contain ordinal numbers. Reverse key indexes solve the problem by simply reverse indexing data.
Create index rev_index on itemfile (itemcode) reverse;

PS. You can use noreverse to re-create the reverse key index as the standard index, but you cannot re-create the Standard Index as the reverse key index.
Alter index rev_index rebuild noreverse;

2.4 bitmap index:The data volume in the table is large, but the unique value of the table is small. Bitmap indexes use bitmaps for each key value instead of the rowid list.
Create bitmap index bit_emp on EMP (sex );

2.5. index the Organizational table

2.6 function-based indexes

2.7. partitions in the Index

Iii. Locking
Locking is a mechanism used by databases to control concurrent access to shared resources. When data is updated, Oracle automatically locks the data. Other users can only view the data until the data is rolled back or committed, and the lock is released. Other users can edit the data.

3.1 row-Level Lock
The row-Level Lock only locks the row that the user is accessing. Oracle automatically applies row locking when statements such as insert, update, delete, select... for update are used.

Select * From order_master where vencode = 'v002'
For update wait 5;
Update .....
Commit;

3.2 Table-Level Lock
Table-level protection protects data. During transaction processing, table-level locks limit the addition, deletion, and modification operations on the entire table.

Lock table <table_name> in <look_mode> mode [Nowait];
● Row share (RS)
● Row exclusive (RX)
● Share (s)
● Share row exclusive, SRX)
● Exclusive (X)

3.3 deadlock
Two transactions wait for each other to complete the task, and a deadlock occurs. Oracle will automatically detect deadlocks and solve the problem by terminating one of the two transactions.

4. Table Partitioning
Oracle provides Table Partitioning technology to improve the performance of large-scale application systems. Table partitions allow users to divide all rows in a table into several parts and store these parts in different locations. Using table partitions in large tables can improve Table query performance, facilitate table management, backup and recovery, and improve data security.

Table partitions are transparent to users. During update and query, users use partitioned tables as normal tables. Add the parttion clause in the create table statement to Create Table partitions.

PS. the table to be partitioned cannot have columns of the long and long raw data types.

4.1. Range partitioning
Determines the partition in which the data is stored based on the value range of a column or a group of columns in the table.
For example, the sales_cost column value of the sales table is partitioned.

Create Table sales
(
...
)
Parttion by range (sales_cost)
(
Parttion P1 values less than (1000 ),
Parttion P2 values less than (2000 ),
Parttion P3 values less than (3000 ),
Parttion P4 values less than (maxvalue)/* Other values greater than 3000 are stored in partition P4 */
);

4.2 hash partitions
Hash partitions determine the physical location of data by executing a hash function on the partition key value. Hash partitions evenly distribute records to different partitions, reducing the possibility of disk I/O contention.
For example, create an employer table to partition by department column into four hash partitions.

Create Table employer
(
...
)
Paerttion by hash (department) parttions 4;

4.3. Composite Partition
A composite partition is a combination of range partitions and hash partitions. When creating a composite partition, partition the data based on the range, and then create a hash partition in the hash partition.
For example, create a sales3 table with a composite partition. Create a range partition based on the sales_date column, and then create a subpartition Based on product_id in the partition.

Create Table sales
(
...
)
Parttion by range (sales_date)
Subparttion by hash (product_id) subparttion 5
(
Parttion P1 values less then (date '2017-04-01 '),
Parttion P2 values less then (date '2017-07-01 '),
Parttion P3 values less then (date '2017-09-01 '),
Parttion P4 values less then (maxvalue)
);

4.4 list partitions
List partitions allow you to explicitly control the ing from rows to partitions. List partitions allow you to group and organize unordered and irrelevant datasets in a natural way.
For example, the employee address is partitioned by the emp_address column and stored in different regions.

Create Table employee
(
...
)
Parttion by list (emp_address)
(
Parttion North values ('Chicago '),
Parttion West values ('san Francisco ', 'Los Angel '),
Parttion South values ('atlanta ', 'daldallas', 'houston '),
Parttion East values ('newyork ', 'boston '),
Parttion AA values (default)/* store the AA partition with other addresses. If this parameter is not specified, other address input is not allowed */
);

When data is inserted, Oracle stores records in the corresponding partition based on the value of the partition column.
Query partition data: Select * From sales3 parttion (P3 );

Partition maintenance:
During partition maintenance, modify the partition of the partition table to achieve better I/O load balancing.

1. Add a partition:Add a new partition after the last partition.
Alter table sales add parttion P4 values less then (4000 );

2. delete a partition:When a partition is deleted, the data in the partition is also deleted.
Alter table sales drop parttion P4;

3. Partition truncation:All records in the table partition will be deleted.
Alter table sales truncate parttion P3;

4. merge partitions:You can connect two adjacent partitions of a partitioned range partition or a composite partitioned table. The result partition inherits the upper bound of the two partitions.
Alter table sales merge parttions p1, p2 into parttion P2;

5. Shard:You can split a partition into two partitions.
/* Split the data in a partition based on '2014-01-01 */
Alter table sales split parttion P3 at (date '2017-01-01 ')
Into (parttion P31, parttion p32 );