Oracle PL/SQL Operations (4) indexes and constraints

1.Database index

You can apply the index concept to a database table. When a table contains a large number of records, it takes a long time for Oracle to look up the close-up records in the table-just as it takes a long time to look up the book to find a topic. Oracle has an easy-to-use feature, that isYou can create a hidden table that contains one or more important columns in the primary table and pointers to corresponding rows in the primary table.. Here, the pointer in the hidden secondary table (index table) is the row number corresponding to the page number of the book. By accessing the index, Oracle can accurately know the row of the specific data to be searched. Because the index is much smaller than the table that references it, It is faster to use the index to search for the data in the table than to use the index. It takes 18.9 seconds to select a record that matches a value in a table with millions of records. After an index is created for the table, the same query takes only 0.6 seconds. Adding an index to a large table can make the query speed dozens of times faster.

Once a table index is created, Oracle automatically synchronizes the table with the table.The index is automatically modified for any insert, update, or delete operations on the table.And if the index contains the columns required by the SELECT statement, any Select Operation on the table is automatically indexed. Adding or deleting indexes does not affect table operations-any previously used tableProgramIt can still run, but the operation speed may be slow. If you delete a table, the related indexes are also automatically deleted, because the indexes of the table are meaningless.

The syntax for deleting an index is as follows:

Drop IndexIndex_name

2. When to use Indexes

For commands that must read Table content, indexes improve their response time. This means that the select, update, and delete commands can run more quickly if the table has an index corresponding to the columns of these commands. HoweverIncreasing the index of a table does not speed up the input data of the INSERT command. On the contrary, it also reduces the running speed.. Because the index itself is actually a table, when adding a record to the table, Oracle must perform two inserts. In this way, adding an index to a group table will lead to more than twice the insert operation time (twice the time for two inserts, and a little time for synchronization between two tables ). Adding two indexes will take three times of the insert operation, and the three indexes will take four times of the insert operation. Therefore, the use of indexes must weigh the advantages and disadvantages. They make data input take a longer time, but increase the speed of reading data. So,It is recommended that you do not add table indexes for applications that require quick data input.. For example, the sales system in supermarkets should make the receiving machine work around the sales affairs as quickly as possible (that is, directly inserted into the database ). In this case, adding an index to the table that stores the transaction will be incorrect because it will slow down the insertion. On the other hand, at the same time, there may be business managers who want to perform queries to analyze transactions.

Remember:The larger the table, the more benefits it will obtain from the table index.. Table 2 is the time it takes to perform various DML operations when using and without indexes on a table with millions of records:

3. How to create an index

The syntax for creating an index is as follows:

 
Create index index_name on table_name (column_name );
 

If you want to index more than one table, the syntax is as follows:

Create index index_name on table_name (first_column_name, second_column_name );
 

Use the above index creation command to create an index:

 
Create index code_index on person (person_code); Create index person_name_index on person (last_name, first_name );
 

Perform an index test on other created tables.

Note: StandardsOracleThe maximum number of columns that an index can contain is32.

4.Index type

1,B *-Tree Indexes

OracleThe default index type of organizational records isB *-Tree. Figure3OutputB *-How is a tree index?Organization record.

When a B *-tree index is created, Oracle analyzes the value of the indexed column, determines how to divide the table into leaf blocks with the same number of records, and then establishes a block layer, so that the records at the leaf block layer are determined in as few steps as possible.

In this example, the branches are evenly separated in alphabetical order. In practice, the branch points are determined by the record value. For example, if a table contains more records starting with "A" than other letters, a complete block may be ", the next block starts with "B.

B *-the advantage of tree indexes is that it enables Oracle to quickly identify records that do not need to be read. By minimizing the amount of data that must be read, Oracle returns results faster.

BecauseB *-Tree indexes work by dividing data into collections and subsets Based on the content.If the indexed column contains a large number of different values, the bitmap index is more suitable.

2Bitmap Index

B *-the tree index structure is most suitable for retrieving columns with a large number of different values. Obviously, another index structure is more effective for columns with only a few different values. For example, gender columns will only contain three possible values: "M" (male), "F" (female), or "u" (unknown ). It is meaningless to put a few different values into the B *-tree index structure, because the B *-tree's "gradually dividing into sub-groups" method is almost useless when there are only a few different values. In this case, it is more meaningful to use Bitmap indexes. Figure 4 provides a simple description of how to design a bitmap index.

When the WHERE clause is a SELECT query of A Low-base series, the bitmap index of this column can be created in advance to greatly reduce the retrieval time. The results of this increase are as follows: 1) The bitmap index is quite small; 2) the "1" or "0" value stored in the bitmap index can be quickly calculated by the computer.

The syntax for creating a bitmap index is as follows:

 
Create bitmap index index_name on table_name (column_name );
 

5.Maintain data integrity: Constraints

(1) What are constraints?

A constraint is a method that defines one or more conditions. user input must meet these conditions before being received by Oracle.

(2) How to establish constraints

We will learn how to establish three different constraints. When used together, these constraints will be helpful in ensuring that the data in the table is not "clean.

1) not null

Non-null. If this constraint is used, the column to be bound cannot be null.

When creating a table, use the "non-null" constraint. For example:

 
Create Table test_constraint (product_name varchar2 (20) not null, product_price number (4, 2) not null, purchase_date date );
 

If the table has been created, you can change the existing table so that when you insert or updateThe column no longer receives null values. Change existing columnsNot nullStatusSyntax:

Alter table table_name modify (column_name not null); for example: alter table person modify (last_name not null );
 

InputCodeYou can test the constraints.

2)Unique

Unique value constraints.

The syntax structure for adding a unique value constraint is as follows:

 
Alter table table_name add constraint constraint_name unique (column_name); for example: alter table person add constraint unique_test unique (first_name, last_name );
 

You can use the insert record statement to test the row.

3)Check

Check (Check) Constraints can be defined as input dataOracleQuantityConditions that must be met for receiving data from the database. You can defineCheck constraints.

For example, the data in the price column must be positive, and the value of the date column must be. Checking constraints is the most effective way to ensure that the database is clean data.Tool.

The syntax for creating a column in an existing table to check constraints is as follows:

 
Alter table table_name add constraint [constraint_name] Check (column_name condition_to_satisfy );
 

 
Create Table Test (age Number (2); alter table test add (constraint test_check check (age is not null and age> 10); insert into test values (12 ); insert into test values (10); insert into test values (9); Commit;
 

 
Alter table purchase add (constraint check_purchase_date check (purchase_date is not null and to_char (purchase_date, 'yyyymmdd')> '2017010'); insert into purchase valuse ('small widget ', 10, '28-May-02 ', 'ga'); insert into purchase valuse ('small components', 9, '28-June-02 ', 'ga '); insert into purchase valuse ('small components', 10, '28-August 22-02 ', 'gal ');
 

 

6.Enable and disable existing constraints

Constraints can be disabled or reloaded without permanently deleting them. Syntax:

 
Alter table table_name disable constraint constraint_name;
 

The syntax for re-enabling constraints is:

 
Alter table table_name enable constraint constraint_name; for example: alter table test disable constraint test_check; insert into test values (7); insert into test values (9); alter table test enable constraint test_check; (Why? How to insert the following data ?) Insert into test values (12); insert into test values (13 );
 

 

7.Modify and delete existing constraints

Life is unpredictable, demand is constantly changing, and sometimes needs to be modified or deletedIn addition to the existing constraints of a table.

The syntax for modifying table constraints is as follows:

 
Alter table table_name modify (column_name null); for example: alter table test modify (test_check null); alter table test modify (test_check not null );
 

To delete the entire constraint, use the following syntax:

Alter table table_name drop constraint constraint_name;
 

Note: deleting constraints is a permanent operation. If you think you will need this appointment in the futureYou can disable the condition to delete it.

8.Use constraints to enhance the relationship between tables

To link two tables, make sure that the two items are correct:

1) The parent table must have one or more columns)Unique IdentifierIt contains every note. (Called primary code or primary key)

2) The sub-table must have oneThe same column (or a group of columns)Contains a unique identifier parentValue. (You are an external code or a foreign key)

 
(1) Use the following statement to create a primary key if the table already exists:
 

 
Alter table table_name add primary key (column_name 1, column_name 2 ,......) ; Example: Create Table ST (st_no number (4), name char (10); alter table st add primary key (st_no );
 

 
When a primary key is created, Oracle automatically uses this primary key to create an index on this column (or several columns.
 

You can also create a primary key directly when creating a table, as shown in figure

Create Table xyqkb (XH number (4) primary key, XM char (10), DH char (15 ));
 

(2) Create a foreign key constraint

A primary key and a foreign key are physical components that establish the association between tables. However, they themselves cannot guarantee the integrity of the connection-that is, even if the primary key and the foreign key columns have identical names and data types, Oracle does not consider them to be related, unless you confirm it. That is, you must define a constraint in the child table so that it checks the primary key of the parent table before receiving data into its foreign key. If such constraints are not met, the user may input a value that does not actually exist in the parent table to the foreign key of the child table.

The syntax for creating a foreign key constraint is as follows:

 
Alter table child_table_name add constraint constraint_name foreign key (column_name (s) _ in_child_table) References parent_table_name;
 

Follow these steps to create a student renewal table(Xycjb):

 
Create Table xycjb (XH number (4), kcbh number (4), CJ number (3); alter table xycjb add constraint xh_fk froeign key (XH) References xyqkb;
 

 

9.Join Operator

1)Union

Returns the results of the first and second queries. If there are duplicates, only one of them is returned.One. The join operator is usually in two tables with similar or consistent structures but within the tableIt can be used in different situations.

The usage is as follows:

 
Select a, B from table_a Union select a, B from table_ B; [order by a]
 

2)Union all

BasicallyUnionSame, but does not remove duplicates and rows;

 
Select name from tst1 Union all select name from tst2;
 

3)Intersect

Returns the same part of the first and second queries:

The syntax structure is as follows:

 
Select a, B from table_a intersect select a, B from table_ B;
 

4) Minus

Return the part of the previous query minus the query, as shown in the following figure:

 
Select name from tst1 minus select name from tst2;
 

 

10. subquery

 
Select name from tst5 where age> (select AVG (AGE) from tst5 );