Oracle transactions and common data objects

The relevant knowledge points of this content we introduced some of them when we studied sqlserver2008r2, including: Transactions, indexes, views, etc. So today we are learning to implement these important things on Oracle for database optimization.

First, the business

1. Introduction
Transaction is the core of data processing, is a logical unit of business, it can guarantee the operation of all the data, either all succeed, or all fail. The DBMS coordinates the concurrency behavior of the user through the management of the transaction, reducing the user's access to the resource conflicts.

1) Show commit: When a transaction encounters a commit instruction, it ends the transaction and permanently saves all the changed data.
2) Show rollback: When a transaction encounters a rollback instruction, the execution of the transaction is also ended, but at this point it rolls back all changed data to the original value at the beginning of the transaction, that is, the change is canceled and the data is unchanged.
3) DDL statement: Once a user executes a DDL (data definition language, such as CREATE,DROP, etc.) statements, all previous DML (data manipulation language) operations are committed as a transaction, which is called implicit commit.
4) Normal End procedure: Oracle automatically commits transactions if the Oracle database application ends normally, such as when the data is changed with the Sqlplus tool and the program exits normally (exit).
5) abnormally terminates the program: when the program crashes or terminates unexpectedly, all data changes are rolled back, and this rollback becomes an implicit rollback.

2, the characteristics of the business
Transactions have 4 properties, abbreviated as ACID properties.
1) Principle: Take the transfer operation as an example, the transfer out account balance decrease and the transfer balance increase is two DML statements, but must act as an indivisible complete operation. Either success at the same time, or failure at the same time, only turned out and not transferred is obviously unacceptable.
2) Consistency: The database is always in a consistent state, either before the transaction, in the transaction, or after the transaction. For example: Before the transfer is 2000 and 1000, the total amount is 3000, 300 after the transfer is 1700 and 1300, the total amount or 3000. This is called consistency. Inconsistency is that the total amount that is queried at a certain point in time is not 3000.
3) Isolation: In a certain period of time, there must be a lot of people in the transfer, everyone's transfer is in their own business, so in a database, there will be a lot of things at the same time. Although there are many things at the same time, things do not affect each other.
4) Persistence: If a thing commits successfully, the data modification will always take effect, and if it is rolled back, then the data will not be modified at all, it is equivalent to not having this thing happen.

3, learn the control of things
1) Use commit and rollback to achieve control of things
Commit: Commit a thing and permanently save the changes to the database in the object.
ROLLBACK: Rollback the thing and cancel any modifications made to the database.
4) Verify Persistence
Once you have used the commit command to end a transaction, you must ensure that the database does not lose the transaction. During a transaction, the principle of isolation requires that no user other than the user involved in the specified session be able to view the current changes. However, once the transaction is complete, all users must be able to see the changes immediately, and the database must ensure that these changes are never lost. Oracle uses log files to meet this requirement. There are two forms of log files: Online redo log files, archive redo log files.
A properly configured Oracle database is not likely to lose data. Of course, user errors, including inappropriate DML or deleted objects, can also cause data loss. DDL statements have autocommit capabilities (create, DROP, truncate, alter)

A summary of things:

1. Note that Commit: just to confirm that the data has been formally modified, not necessarily to write to the hard disk, dbwn do nothing. The LGWR process writes the contents of the log buffer to disk when all physical operations occur while executing the commit command. The DBWN process is not performing any action at all. The DBWN process has nothing to do with committing things, but eventually the DBWN process writes the changed chunks to disk.
2. The commit and rollback statements should only be in DML statements, and we cannot roll back the DDL statements. Once the DDL statement is executed, it will immediately have a persistent state.
   3. Autocommit and Implicit commit: Oracle can be autocommit in some cases: executing a DDL statement is a case where exiting a user process is also an automatic commit.

Second, the index

1, the meaning of the index
The Oracle database object is also called the schema object, the database object is a collection of logical structures, the most basic database object is a table, and the index is one of them. Other database objects include:

An index is an object of Oracle, an optional structure associated with a table, providing a way to quickly access data and improve database retrieval performance. The index allows the database program to find the data it needs without scanning the entire table. Like a book directory, you can quickly find the information you need through a catalog, without having to read the entire book.

2, the characteristics of the index
Use indexes appropriately to improve query speed
You can index one or more columns of a table
No limit on the number of indexed builds
Indexes require disk storage, you can specify tablespaces and are automatically maintained by Oracle
The index is transparent to the user and whether the index is used for retrieval is determined by Oracle itself
The Oracle database management system uses the following three ways of accessing data:
? full table Scan
? quick access to a row of the table via ROWID (line address)
? Using the Index
Full table scan when no index is used or if index is not selected

3, the classification of the index
1) B-Tree index structure
The top of the index is the root, which contains items that point to the next-level index. The next level is the branch block, which also points to the lower-level block in the index, and the lowest-level block is called the leaf node, which contains the index entry that points to the table data row. Leaf nodes are two-way connections that help you scan indexes by keywords that are worth ascending and descending.
Example: Query IDs from 2 to 31 rows of data

Use the index traversal procedure in the following:
First find the branch of id<=50, then find 30-40 of the branch block, find the id=31 corresponding index entry, and then through the leaf node bidirectional link, parallel to find the index block containing id=2, complete the query of the ID

4. Syntax for creating indexes
Create [unique] index name on table name (column name) [tablespace table space name]

Explain:
[Unique] is used to specify a unique index, which by default is a non-unique index
[Tablespace] Specify tablespace for index

1) Create a standard index
Sql> CREATE INDEX index_name on tablename (columnname)
Tablespace Index_tbs;
2) Rebuilding the index
sql> ALTER INDEX index_name REBUILD;

Merge Index Fragmentation
Sql>alter INDEX index_name Coalesce;

3) Delete Index
sql> DROP INDEX index_name;

Third, view

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

2, the advantages of the view:
1) provides another level of table security
2) The complexity of hidden data: A view may be defined with a multi-table connection, but the user does not need to know that a multi-table connection statement can also query the data.
3) simplified user's SQL command: Query the view without having to write complex query statements, only need to query the view name.
4) Isolate the structure of the base table: After the view has been created, if you modify the structure of the table, it does not affect the view.
5) Provide the data from another angle by renaming the column: for example, in the sales system, the day before work to summarize the day data, in the eyes of the salesperson, the summary table becomes the daily sales statistics, in the eyes of the financial person, the sales table becomes the Sales day report.

3. Create the syntax for the view:
1) CREATE [OR REPLACE] [force] VIEW
view_name [(alias[, alias] ...)]
As Select_statement
[With CHECK OPTION]
[with READ only];
Explain:
OR REPLACE: If the view already exists, this option will recreate the view.
Force: If you use this keyword, the view is created regardless of whether the base table exists.
Noforce: This is the default value, and if you use this keyword, the view is created only if the base table exists.
View_name: To create the name of the view
Alias: Specifies the alias of the expression or column selected by the query for the view. The number of aliases must match the number of expressions selected by the view.
Select_statement:select statements
With CHECK option: This option specifies that only rows that are accessible by the view can be inserted or updated. The term constraint represents the name specified for the CHECK option constraint.
With READ only: This option guarantees that no modifications can be performed on this view.

2) The ORDER BY clause in the view
You can use the ORDER BY clause in a SELECT statement when you create the view to sort in a specific order, so that the result set is arranged in the order specified, even if the ORDER BY clause is not used when querying the view.

3) Create a view with errors
If the force option is used in the CREATE VIEW statement, Oracle creates the view even if there is a series of cases.
The query for a view definition references a table that does not exist.
The query for the view definition references columns that are not valid in an existing table.
The owner of the view does not have the required permissions.
In these cases, Oracle only checks for syntax errors in the CREATE VIEW statement. If the syntax is correct, the view is created and the definition of the view exists in the data dictionary. However, the view is not available. This view is considered to be "created with errors." You can use the show ERRORS view name to view errors.

Iv. sequence

The sequence is used to generate a unique, contiguous integer database object. A sequence is typically used to automatically generate a value for a primary key or unique key. The sequences can be sorted in ascending order, or in descending sequence, as with Excel's automatic sorting, and SQL Server identifiers.

V. Synonyms

A synonym is an alias for an object that does not occupy any real storage space, and only holds its definition description in Oracle's data dictionary, which Oracle translates as the name of the corresponding object when using synonyms.
1. Use of synonyms
1) Simplifying SQL statements
If the user creates a table with a long name, you can create an Oracle synonym for the table to simplify the statement.
2) Hide the name and owner of the object
In multi-user collaborative development, the names and holders of objects can be masked. If you do not have synonyms, when you manipulate other users ' tables, you must use the form "User name. Table name" To hide the user name after using the Oracle synonym. For example: User user1 to access the user's Scott's EMP table, you must use SCOTT.EMP to refer to it. If a synonym named Emp is created for the user to represent Scott.emp, then User1 can use the synonym to refer to scott.emp as if it were accessing its own table.
3) provides location transparency for remote objects in a distributed database
To complete the remote object access, first understand the concept of database connectivity. A database link is a named object that describes the path of a database to another database through which you can implement communication between different databases. The role of synonyms in database linking is to provide location transparency.
4) Provide public access to database objects
A public synonym simply defines a common alias for the database object that other users can access through the alias, but is able to access it successfully through that alias, as well as whether they already have access to the database object.

2. Classification of synonyms
? Synonyms are divided into the following two categories: private synonyms and public synonyms
A private synonym can only be accessed within its schema and cannot have the same name as an object in the current schema
The public synonym can be accessed by all database users

2-1: Private synonym
A private synonym can only be accessed by the user of the current schema, and the private synonym name cannot be the same as the object name of the current schema. To create a private synonym in its own mode, the user must have the Create synonym system permission. To create a private synonym in another user mode, the user must have the Create any synonym system permission.
2) The syntax for creating a private synonym is as follows:
CREATE OR REPLACE synonym [schema.] Synonym_name for [schema.] object_name;

Synonym_name: Name of the synonym to create
object_name: Specifies the name of the object for which you want to create a synonym.

Six, the partition table

1, the meaning of the partition table
Oracle allows all rows of a table weight to be divided into sections and stored in an out-of-space tablespace, partitioned into partitions, and partitioned tables into partitioned tables.

Partitioning is useful for tables that contain large amounts of data, with the following advantages:
1) Improve the query performance of the table. When a table is partitioned, users can only access specific partitions in the table rather than the entire table when they execute the SQL query.
2) The table is easier to manage. Because partitioned table data is stored in multiple parts, it is easier to load and delete data by partition than to load and delete it in a table.
3) Easy backup and recovery. Each partition can be backed up and restored independently.
4) Improve data security. Distributing different partitions on different disks can reduce the likelihood of data corruption at the same time for all partitions.

A table with a combination of conditions can be built into a partitioned table:
1) The amount of data is greater than 2GB.
2) There is a clear demarcation between the existing data and the newly added data.
The table partition is transparent to the user, and the application may not know that the table has been partitioned and operates as a normal table when updating and querying the partitioned table, but the Oracle optimizer knows that the table is partitioned.

Note: The table to be partitioned cannot have a long and long raw data type column.

2. Classification of partition Table
The partitioning methods provided by Oracle are range partitions, list partitions, hash partitions, composite partitions, interval partitions, and virtual column partitions. Where interval partitions and virtual column partitions are new features of oracle11g

Oracle transactions and common data objects