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

Blog structure

Use of transactions

Use of index

The role of views

The use of sequences and synonyms

The concept of partition table

one。 Business

Oracle ensures the consistency of data in the database through transactions.

The meaning of the transaction

A transaction is a logical unit of a business, which ensures that all operations on the data either succeed or fail.

The transaction begins with an executable SQL statement, continues to execute the transaction body, and ends in any of the following situations.

Explicit commit (commit): when a transaction encounters a cormmit instruction, the transaction ends and all changed data is permanently saved.

Explicit ollback; when a transaction encounters a rollback instruction, it also ends the execution of the transaction, but it rolls back at this point

All changed data reaches the original value at the beginning of the transaction, that is, canceling the change.

DDL statement: once the user executes DDL (Data DefinitionLanguage, data definition language, such as CREATE. DROP

Statement, then all previous DML (Data Manipulation Language, data manipulation language) operations will be used as a

A transaction is committed. This kind of submission is called implicit submission.

Normal Terminator: if the Oracle database application ends normally, such as changing the data using the SQL Plus tool

If you exit the program normally (enter "exit"), Oracle automatically commits the transaction.

End the program abnormally: when the program crashes or aborts unexpectedly, all data changes are rolled back, which is called hidden

Type rollback. .

Characteristics of the transaction

There are four characteristics of a transaction, namely Atomicity. -Consistency, isolation. (Isolation) and persistence (Durability). Abbreviated as ACID feature.

(1) atomicity: either succeed or fail at the same time

(2) consistency: whether before, during, or after a transaction, the database is always in a consistent state.

Transaction control

First create a tablespace SQL > create tablespace sales 2 datafile'/ space/sales.dbf' size 100m; the tablespace has been created. See which tablespaces SQL > select tablespace_name from dba_tablespaces;TABLESPACE_NAME----SYSTEMSYSAUXUNDOTBS1TEMPUSERSSALES have selected 6 rows. Create table sales and place it in sales space SQL > create table sales 2 (name varchar (50), 3 sex varchar (10), 4 address varchar (100), 5 results float (10)) 6 tablespace sales; table has been created. Insert into the table the first record SQL > insert into sales values ('zhangsan','nan','haidian',90) as above; 1 row has been created. Commit transaction (do not commit or do not save) SQL > commit; commit complete. Rollback SQL > rollback; fallback completed. Automatic submission

SQL > set autocommit on; II. The meaning of index

Index is an object of Oracle and an optional structure associated with tables, which provides a fast way to access data and improves the retrieval performance of the database. The index enables the database program to find the data it needs without scanning the entire table.

The characteristics of the index are as follows:

Proper use of indexes can improve query speed.

You can index one or more columns of a table.

There is no limit to the number of indexes.

Indexes need to be stored on disk, and tablespaces can be specified and maintained automatically by Oracle.

The index is transparent to the user, and it is up to Oracle to decide whether to use the index or not.

Oracle's database management system uses the following two access methods when accessing data.

Full table scan.

Use the index.

Classification B-tree index of index

The top of the index is the root, which contains items that point to the next index. The next level is the branch block, which in turn points to the block at the next level in the index. The lowest level of the block is called the leaf node, which contains index entries that point to the rows of table data. Leaf nodes are bi-directional links, which help

Scan the index in ascending and descending order of key values.

As shown in the figure:

Case study:

Create a B-tree index sales_address_indexSQL > create index sales_name_index 2 on sales (address) on the address column of the sales table; the index has been created. Create a unique index sales_name_unique_indexSQL > create unique index sales_name_unique_index on sales (name) on the name column of the sales table; the index has been created. Create reverse index SQL > create index sales_reverse_index on sales (results) reverse; index has been created. Create bitmap index SQL > create bitmap index sales_bit_index on sales (sex); index has been created. The principle of creating an index is that frequently searched columns can be used as indexes. Frequently sorted, grouped columns can be used as indexes. Columns that are often used as joins (primary / foreign keys) can be used as indexes. Put the index in a separate tablespace, not in a tablespace with fallback segments, temporary periods, and tables. For large indexes, consider using the NOLOGGING clause to create them. Periodically rebuild or reorganize the index for defragmentation based on how often the business data occurs. Periodically rebuild or reorganize the index for defragmentation based on how often the business data occurs. Do not create an index where there are only a few columns with different values. The table contains only a few rows. three。 View

The View is a virtual table that does not take up physical space because the definition statements for the view itself are stored in the data dictionary. The data in the view is obtained from one or more actual tables. The tables used to generate views are called the base tables of views. One view can also be generated in another view.

Materialized views (Materialized View), also known as materialized views and snapshots (the previous term of Oracle 8i), contain data and take up storage space, which is of great practical value in data warehouses.

The function of the view hides the complexity of the data, the view simplifies the user's command, the view isolates the application from the changes defined in the base table, and the view renames the column. four。 Sequence

Sequences are database objects that are used to generate unique, consecutive integer types. Sequences are typically used to automatically generate values for primary or unique keys. Sequences can be arranged in ascending or descending order. For example, a serial number in a sales table can be used to automatically

Generate.

The creation sequence SQL > create sequence sale_seq 2 start with 1 3 increment by 1 4 nomaxvalue 5 nocycle; sequence has been created. Create table projectSQL > create table project (id int,name varchar2 (10)); table has been created. Add the record SQL > create table project (id int,name varchar2 (10)) to the project table; the table has been created. SQL > insert into project values (sale_seq.nextval,'zhangsan'); 1 line has been created. The submission is complete. SQL > insert into project values (sale_seq.nextval,'lisi'); 1 line has been created. The submission is complete. SQL > select * from project; ID NAME- 1 zhangsan 2 lisi View sequence SQL > select sale_seq.currval from dual; CURRVAL- 2 maximum value of modified sequence to 5000SQL > alter sequence sale_seq 2 maxvalue 5000 3 cycle; sequence has been changed. View the sequence SQL > select sequence_name,increment_by,cache_size from user_sequences where 2 sequence_name='SALE_SEQ' SEQUENCE_NAME----INCREMENT_BY CACHE_SIZE--SALE_SEQ 1 20 5. Synonym

A synonym is an alias for an object that does not take up any actual storage space and only saves its definition description in the Oracle data dictionary. When a synonym is used, Oracle translates it to the name of the corresponding object.

The use of synonyms

1) simplify the SQL statement

2) hide the name and owner of the object

3) provide location transparency for remote objects in distributed databases

4) provide public access to database objects

Classification of synonyms

Synonyms can be divided into two categories

1) Private synonyms

2) Public synonyms

six。 Partition table

The meaning of partition table

Oracle allows users to divide all rows in a table into sections and store them in different table spaces. Each partition is called a partition and the partitioned table is called the partition table (Partition).

For tables that contain large amounts of data, partitioning is necessary. The advantages of partitioned tables are as follows:

Improve the query performance of tables. After partitioning a table, users can access only specific partitions in the table when executing a SaL query

Not the whole table. Watches are easier to manage. Because the data of the partitioned table is stored in multiple sections, loading and deleting data by partition is better than adding data to the table.

It is easier to load and delete.

Easy to backup and restore. Each partition can be backed up and restored independently. Improve data security. Distributing different partitions on different disks can reduce the possibility of data corruption for all partitions at the same time.

Case study:

Create the table and partition it to age. Create table student (id number, name varchar2 (10), age number) partition by range (age) (partition p1 values less than (10), partition p2 values less than (20), partition p3 values less than (maxvalue)); 2. Insert data insert into student values into the table (1); insert into student values (2); insert into student values (3); insert into student values (4) 3, query the data select * from student partition (p1); select * from student partition (p2); select * from student partition (p3); 4. Delete the data under 10 years old. Delete from student partition (p1)

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.