Shulou(Shulou.com)06/01 Report--

Table integrity and constraints

Entity integrity: primary key constraint

Domain integrity

Referential integrity: foreign key constraint

SELECT constraint_name, constraint_type FROM user_constriants WHERE table_name = 'EMP'

II. Manage the index

Index is a kind of storage structure based on table field, and the query speed of table can be accelerated by using index. When the index key column is used in the WHERE clause of the SQL statement, the index will point directly to containing these worthwhile locations. Rational use of the index is the main way to reduce disk I-O, which only affects the speed of execution.

When you create an index, Oracle gets the columns you want to create the index and sorts them. The ROWID is then stored along with the index value of each row. When in use, Oracle first performs a quick search with sorted index values, and then uses the associated ROWID value to locate the record row you are looking for.

Once indexes are created, Oracle automatically maintains and uses them. When you update a row record, the index is updated automatically, so creating too many indexes for the table can degrade the performance of the table.

Get the index information user_indexes view.

Rebuilding an existing index performs better than deleting a rebuilt index. ALTER INDEX item_index REBUILD

2.0, traditional B-tree index:

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. Composite index: an index created on multiple columns in a table. If the WHERE clause in the SELECT statement references all or most of the columns in the combined index, the combined index can improve the speed of data retrieval. In general, the most frequently accessed column when creating an index should be placed at the front of the list.

CREATE INDEX comp_index ON itemfile (p_category, itemrate)

2.3, reverse key index: a special index, which is very useful when the index contains ordinal columns. The reverse key index solves the problem by simply reverse the data in the index.

CREATE INDEX rev_index ON itemfile (itemcode) REVERSE

Ps. You can use NOREVERSE to rebuild a reverse key index to a standard index, but not a standard index to a reverse key index.

ALTER INDEX rev_index REBUILD NOREVERSE

2.4. Bitmap index: the amount of data in the table is large, but the unique value of the table is small. The bitmap index uses a bitmap of each key value instead of a ROWID list.

CREATE BITMAP INDEX bit_emp ON emp (sex)

2.5. Index organization table

2.6. Function-based indexing

2.7. Partitions in the index

Third, lock

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

3.1, row-level lock

Row-level locks lock only the rows that the user is accessing. When using INSERT, UPDATE, DELETE and SELECT. When you use statements such as FOR UPDATE, Oracle automatically applies row locking.

SELECT * FROM order_master WHERE vencode = 'V002'

FOR UPDATE WAIT 5

UPDATE.

COMMIT

3.2, table-level lock

The table level will protect the data, and during the transaction, the table level lock will restrict the operation of adding, deleting, and changing the entire table.

LOCK TABLE IN MODE [NOWAIT]

● line sharing (ROW SHARE, RS)

● row exclusion (ROW EXCLUSIVE, RX)

● sharing (SHARE, S)

● shared row exclusive (SHARE ROW EXCLUSIVE, SRX)

● exclusion (EXCLUSIVE, X)

3.3. Deadlock

A deadlock occurs when two transactions wait for each other to complete the task. Oracle automatically detects deadlocks and resolves the problem by terminating one of the two transactions.

Table Partition

Oracle provides table partitioning technology to improve the performance of large-scale applications. Table partitioning allows users to divide all rows in a table into sections and store them in different locations. The use of table partitioning in large tables can improve table query performance, facilitate table management, backup / restore, and improve data security.

Table partitions are made transparent to users, who use partitioned tables as regular tables when updating and querying. Add a PARTTION clause to the CREATE TABLE statement to create a table partition.

Ps. The table to be partitioned cannot have columns of LONG and LONG RAW data types.

4.1, range Partition

Determine the partition on which the data is stored based on the range of values of a column or group of columns in the table.

For example: partition the sales_cost column values of the sales table

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 partition

The hash partition determines the physical location of the data by executing a hash function on the partition key. The hash partition distributes the records evenly among different partitions, reducing the possibility of disk Imax O contention.

For example: create an employer table and partition it into 4 hash partitions according to the department column

CREATE TABLE employer

(

...

)

PAERTTION BY HASH (department) PARTTIONS 4

4.3, compound partition

Composite partitions are a combination of range partitions and hash partitions. When creating a composite partition, the data is partitioned according to the range, and then the hash subpartition is created within the hash partition.

For example: create a sales3 table, and the partition is a compound partition, first create a range partition based on the sales_date column, and then create a sub-partition according to the product_id within the partition.

CREATE TABLE sales

(

...

)

PARTTION BY RANGE (sales_date)

SUBPARTTION BY HASH (product_id) SUBPARTTION 5

(

PARTTION P1 VALUES LESS THEN (DATE '2001-04-01')

PARTTION P2 VALUES LESS THEN (DATE '2001-07-01')

PARTTION P3 VALUES LESS THEN (DATE '2001-09-01')

PARTTION P4 VALUES LESS THEN (MAXVALUE)

);

4.4. List Partition

List partitions allow users to explicitly control row-to-partition mapping. List partitioning allows unordered and unrelated datasets to be grouped and organized in a natural way.

For example, the employee table is partitioned according to the employee's address emp_address column and stored differently for different regions.

CREATE TABLE employee

(

...

)

PARTTION BY LIST (emp_address)

(

PARTTION north VALUES ('Chicago')

PARTTION west VALUES ('San Francisco', 'Los Angeles')

PARTTION south VALUES (Atlanta, Dallas, Houston)

PARTTION east VALUES ('New York', 'Boston')

PARTTION aa VALUES (DEFAULT) / * other addresses are stored in aa partitions. If not specified, no other address input is accepted * /

);

When you insert data, Oracle stores the record to the corresponding partition based on the value of the partition column.

Query partition data: SELECT * FROM sales3 PARTTION (P3)

Maintenance operations for partitions:

Modify the partition of the partition table during the partition maintenance operation to achieve a better Icano load balance.

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 you delete a partition, the data in the partition is also deleted.

ALTER TABLE sales DROP PARTTION P4

3. Truncate the partition: all records of the table partition will be deleted.

ALTER TABLE sales TRUNCATE PARTTION P3

4. Merge partitions: you can join two adjacent partitions of a range partition or a composite partition table. The resulting partition inherits the upper bounds of the two partitions.

ALTER TABLE sales MERGE PARTTIONS P1, P2 INTO PARTTION P2

5. Split partition: allows the user to split a partition into two partitions.

/ * split a partition data with '2005-01-01' as the dividing line * /

ALTER TABLE sales SPLIT PARTTION P3 AT (DATE '2005-01-01')

INTO (PARTTION P31, PARTTION P32)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.