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

Transaction is the core of data processing and a logical unit of the business, which can ensure that all operations on the data are either successful or fail. DBMS coordinates the concurrent behavior of users through transaction management and reduces conflicts when users access resources.

.

1) Show commit: when a transaction encounters a COMMIT instruction, it ends the transaction and permanently saves the data for all changes.

2) Show rollback: when a transaction encounters a ROLLBACK instruction, it will also end the execution of the transaction, but at this time it rolls back all changed data to the original value at the beginning of the transaction, that is, cancels the change, and the data remains unchanged.

3) DDL statement: once the user executes the DDL (data definition language, such as create,drop, etc.) statement, all previous DML (data manipulation language) operations are committed as a transaction, which is called implicit commit.

4) normal termination program: if the oracle database application ends normally, if you use the sqlplus tool to change the data and exit the program (exit) normally, oracle automatically commits the transaction.

5) abnormal termination of the program: when the program crashes or terminates unexpectedly, all data changes are rolled back, which becomes an implicit rollback.

.

Characteristics of the transaction

Transactions have four features, abbreviated as ACID features.

1) principle: take the transfer operation as an example, the decrease of the balance of the transfer account and the increase of the transfer balance are two DML statements, but they must be treated as an indivisible and complete operation. Either succeed at the same time or fail at the same time, it is obviously unacceptable to transfer out without turning in.

2) consistency: whether before, during or after a transaction, the database is always in a consistent state. For example: before the transfer is 2000 and 1000 respectively, the total amount is 3000, after 300 is 1700 and 1300 respectively, the total amount is still 3000. It's called consistency. Inconsistency is that the total amount queried at some point is not 3000.

3) isolation: in a certain period of time, there must be a lot of people transferring money, and everyone's transfer is in their own transaction, so there will be a lot of things in a database at the same time. Although there are many things at the same time, things do not influence each other.

4) persistence: if the transaction is successfully submitted, the data modification will take effect forever. If it is a rollback, the data will not be modified at all, which means that no such thing has happened.

.

Control of things

1) use COMMIT and ROLLBACK to control things

COMMIT: submit things and permanently save changes to the database in things.

ROLLBACK: rolls back things to cancel any changes made to the database.

.

Example 1: using COMMIT and ROLLBACK to achieve transaction control

Insert data is performed first.

.

Execute the COMMIT submission, and the data will be saved permanently.

.

Insert the data again and perform a rollback rollback.

The query did not find a row of data of 70

.

2) using AUTOCOMMIT to realize automatic submission of things

Oracle provides a way to automatically submit DML operations, so that once the user performs a DML operation, such as UPDATE,DELETE, the data is automatically submitted.

Example 2: use autocommit to automatically submit things, and set autocommit to ON

.

As long as the set autocommit on command is executed in advance, the data will be submitted automatically, and the rollback data will still be saved in time.

.

3) verify isolation

1) create the table yuangong and insert the data.

At this point, the transaction recorded by insert is not committed, and if the transaction is not committed, it is not really completed, and there is still an opportunity for rollback.

.

2) make an inquiry

.

3) then open a new sqlplus session, and when you look at the table, you will find that there are no newly inserted records, which is the isolation of things.

.

4) submit things in the first sqlplus session

.

5) the fourth record inserted in the second session can be seen only after the submission.

.

4) verify persistence

Once you use the commit command to end a transaction, you must ensure that the database does not lose the transaction. During the course of a transaction, the principle of isolation requires that no user other than the user involved in the specified session can view the current changes. However, once the transaction is completed, all users must be able to see the changes immediately, and the database must ensure that the changes are never lost. Oracle meets this requirement by using log files. Log files come in two forms: online redo log files and archived redo log files.

It is impossible for a properly configured oracle database to lose data. Of course, user errors (including inappropriate DML or deletion of objects) can also result in data loss. DDL statements have auto-commit functions (create, drop, truncate, alter)

.

1) delete the table yuangong, and query again to indicate that the table does not exist.

.

2) create a new table students and insert a piece of data

.

3) rollback the transaction

.

4) write data again

.

5) exit sqlplus (exit)

6) if you look at the records in the aa table in another sqlplus, you will find the records of the newly inserted lisi. If you use the sqlplus tool to change the data, oracle will automatically submit things when you normally exit sqlplus.

.

A summary of things:

It's important to note that Commit: just to confirm that this data has been formally modified, it doesn't have to be written to the hard drive, and DBWn doesn't do anything. The LGWR process writes the contents of the log buffer to disk during all physical operations that occur during the commit command. The DBWN process does nothing at all. The DBWN process has nothing to do with commit transaction processing, but eventually the DBWN process writes the changed blocks to disk. Commit and rollback statements should only be in DML statements, and we cannot roll back DDL statements. The DDL statement has a persistent state as soon as it is executed.

3. Auto-commit and implicit commit: oracle can auto-commit in some cases: executing a DDL statement is a case, and exiting a user process is also an auto-commit.

.

Indexes

The meaning of index

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

Index is an object of oracle and an optional structure associated with tables, which provides a fast way to access data and improves the performance of database retrieval. The index enables the database program to find the data it needs without scanning the entire table. Just like the catalogue of a book, you can quickly find the information you need through the catalog without having to read the whole book.

.

Characteristics of the index

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. Table spaces can be specified and maintained automatically by oracle.

The index is transparent to users, and it is up to oracle itself to decide whether to use the index when retrieving.

The Oracle database management system uses the following three ways to access data:

Full table scan

Through ROWID (row address, quick access to a row of the table)

Use index

Use full table scan when there is no index or when you do not choose to use an index

.

Classification of indexes

1) B-tree index structure

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, and the lowest level block is called the leaf node, which contains index items that point to the table data row. The leaf node is a two-way join, which helps to scan the index in ascending and descending order by keyword.

For example: query id data from 2 to 31 rows

.

The traversal process using the index in the above figure is as follows:

Find id CREATE INDEX index_name ON tablename (columnname) first

TABLESPACE index_tbs

2) rebuild the index

SQL > ALTER INDEX index_name REBUILD

Merge index fragments

SQL > ALTER INDEX index_name COALESCE

.

3) Delete index

SQL > DROP INDEX index_name

.

Example 1:

1) in the emp table, create a b-tree index for the employee name (ename) column.

.

Create a unique cable

? Make sure there are no duplicate values in the column that defines the index

Oracle automatically creates a unique index on the primary key column of the table

? Create a unique index using the CREATE UNIQUE INDEX statement

The syntax is as follows:

SQL > CREATE UNIQUE INDEX index_name

ON tablename (columnname)

Example: in the salary scale (salgrade) table, create a unique index for the level numbered grade column.

.

6. Reverse key index

In contrast to a regular B-tree index, the reverse key index reverses the bytes of the index column while maintaining the column order. By reversing the data value of the index key, the reverse key index makes the modification of the index evenly distributed over the whole index tree. It is mainly used in scenarios where multiple instances access a database at the same time. As shown below:

.

If, in the case of a regular B-tree index, two employee number empno indexes are in the same index block because they are close to each other in the index tree species, conflicts will occur when multiple instances are updated at the same time, resulting in a bottleneck in iCompo access. So you can use reverse key indexing at this time. Reverse key indexes are usually based on continuously growing columns, such as numbering.

Example: in the employee emp table, create a reverse key index for the employee number empno column. CREATE INDEX emp_empno_reverse_idx ON emp (empno) REVERSE

.

To remind you that this column has been indexed, execute the following command to query what the indexes are.

.

Let's query the column for which the PK_EMP index is created.

.

As you can see from the figure above, the PK_EMP index is created for the empno column, so the reverse key index created above cannot be created, and multiple indexes cannot be created for the same column.

So either delete the PK_EMP index or keep it instead of creating a reverse key index

So let's delete the original PK_EMP now.

.

The prompt cannot be deleted because the table EMP of this index has a primary key. If you want to delete the index, you must remove the primary key. The command is as follows:

Alter table table name drop constraint primary key name

.

Then create a reverse key index

.

Query whether the establishment is successful.

Add: modify the primary key:

Alter table table name add constraint primary key name primary key (column1,column2,....,column)

Note: the primary key name here is a string defined by yourself, but it may not be the field name in the table (it is customary to write it in the format of PK_ table name, and the primary key name automatically established in oracle is the format of PK_ table name), but keep in mind that this name is also used when deleting! The fields in parentheses are the fields that exist in the table.

ALTER TABLE ZFMI.TB_RI_SHARE_BILL ADD?

CONSTRAINT PK_TB_RI_SHARE_BILL

PRIMARY KEY (C_RI_COM_CDE, C_PROD_NO, C_FEE_TYPE, C_SHARE_YM)

ENABLE

VALIDATE

.

7. Bitmap index

Bitmap indexes are suitable for columns that are lower than the cardinality, that is, the column has a limited number of values. For example, the type of work (job) column in the employee table is limited, even if there are millions of employee records. The Job column can be used as a bitmap index, similar to the book category column in the book table.

.

The bitmap index does not store ROWID directly, but stores the mapping of byte bits to ROWID, which reduces response time and saves space. Bitmap indexes should not be used on tables where insert, update and delete operations occur frequently, because a single bitmap index points to many data rows of the table, and all the data rows it points to need to be locked when the index item is modified, which will seriously reduce the concurrent processing ability of the database. Bitmap index is suitable for data warehouse and decision support system.

Example: in the employee emp table, create a bitmap index for the job column.

Basic syntax:

CREATE BITMAP INDEX emp_job_bit_idx ON emp (job)

Check to see if the establishment is successful.

.

.

Combinatorial index

A composite index similar to sqlserver that creates an index on multiple columns within a table. The columns in the index do not have to be in the same order as the columns in the table, nor do they have to be adjacent to each other.

Example: indexes on departments and job columns in the employee table. The columns of a combined index contain up to 32 columns.

.

Function-based index

The index you need to create requires a function or expression with one or more columns in the table, or you can create a function-based index as a B-tree index or a bitmap index.

Basic syntax:

SQL > CREATE INDEX emp_ename_upper_idx

ON tablename (UPPER (columnname))

Example: in the emp table, create a lowercase function index for the employee name (ename) column.

.

Index:

.

Principles for creating an index

Frequently searched columns can be used as index columns

Sort frequently, 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 table space, not in a table space with fallback segments, temporary periods, and tables

For large indexes, consider using the NOLOGIN clause to create large indexes.

Periodically regenerate or reorganize the index and defragment according to the frequency of business data.

.

Example: put the index in a separate table space

1) create a tablespace using sys login

Create tablespace net_tbs

Datafile'/ opt/oracle/oradata/orcl/worktbs01.dbf'

Size 10m autoextend on

.

2) modify the index to the table space.

Alter index ZUHE rebuild tablespace NEW_TBS

.

3) check whether to modify it.

Select index_name,index_type,table_name,tablespace_name

From USER_INDEXES

.

You can see from the figure above that the tablespace has been changed to new_tbs.

.

Example 2: using the nologging clause

Create index ZUHE2 on emp (JOB,SAL) nologging

Select index_name,index_type,table_name,tablespace_name

From USER_INDEXES

.

View information about index columns: index name, table name, index column.

Select index_name,table_name,column_name

From user_ind_columns

Where index_name like 'EMP%'

.

Maintain index

1) rebuild the index

The index needs to be maintained. If there are a large number of delete and insert operations in the indexed table, the index will be very large, because after the delete operation, the index space of the deleted value can not be automatically reused. For large tables and tables with frequent DML operations, index maintenance is very important. Oracle provides rebuild instructions to rebuild the index. So that the index space can reuse the space occupied by deleted values, making the index more compact.

Alter index emp_job_bit_idx rebuild tablespace net_tbs

.

Select index_name,index_type,table_name,tablespace_name

From USER_INDEXES

2) merge index fragments

Merging index fragmentation can release part of the disk space, which is not only an important way of index maintenance, but also a way to maintain disk space, similar to disk defragmentation, releasing unused space for reuse.

Alter index emp_job_bit_idx COALESCE

.

3) Delete index

Drop index emp_job_bit_idx

.

.

View

Overview

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

.

Advantages of views:

1) provides another level of table security

2) complexity of hidden data: a view may be defined by multi-table joins, but users do not need to know the statements of multi-table joins to query data.

3) simplified user's SQL command: when querying the view, you don't need to write complex query statements, you only need to query the view name.

4) isolate the changes in the structure of the base table: after the view is created, if you change the structure of the table, it will not affect the view.

5) provide data from another point of view by renaming the column: for example, in the sales system, the data of the day should be summarized before the end of the day. In the eyes of the salesperson, the summary table becomes the daily sales statistics table, and in the eyes of the financial staff, the sales table becomes the daily sales report.

.

The syntax for creating a view:

1) CREATE [OR REPLACE] [FORCE] VIEW

View_name [(alias [, alias]...)]

AS select_statement

[WITH CHECK OPTION]

[WITH READ ONLY]

Explanation:

OR REPLACE: if the view already exists, this option recreates the view.

FORCE: if you use this keyword, a view is created regardless of whether the base table exists or not.

NOFORCE: this is the default value, and if you use this keyword, the view is created only if the base table exists.

VIEW_NAME: the name of the view to be created

Alias: specifies the alias of the expression or column selected by the view's query. The number of aliases must match the number of expressions selected by the view.

Select_statement:select statement

WITH CHECK OPTION: this option specifies that only rows that are accessible to the view can be inserted or updated. The term constraint refers to the name specified for the CHECK OPTION constraint.

WITH READ ONLY: this option guarantees that no modification can be performed on this view.

.

2) ORDER BY clause in the view

You can use the ORDER BY clause in the SELECT statement when you create the view to sort it in a specific order, so that the result sets are arranged in the specified order even if you do not use the ORDER BY clause when querying the view.

.

3) create a view with errors

If you use the FORCE option in a create view statement, oracle creates a view even if there are a series of cases.

? The query defined by the view references a table that does not exist.

? The query defined by the view references invalid columns in the 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 is stored in the data dictionary. However, this view cannot be used. This view is considered to be "created with errors". You can use the SHOW ERRORS VIEW view name to view errors.

.

4. Experimental case: operation on single table view

1) Connect to oacle and log in using scott user

.

2) create table order_master

.

3) insert data

SQL > insert into order_master values ('2010-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01)

SQL > insert into order_master values ('2011-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01)

.

4) create a view with an order status of "p", indicating that you do not have permission to create a view.

.

5) Grant permission to create a view (login using sys user)

.

6) create the view again

.

7) query View

As you can see from the above figure, the query view can also find the data in the table.

.

8) modify the data through the view and change the order with status p to d, but querying the view again after the modification is complete will not find any data, because the column that was the condition when the view was created was modified.

.

9) to avoid being unable to query after modifying the view, use the with_check_option statement to create a check constraint to prevent this from happening, and you can use constraint to specify the constraint name.

.

10) write the data again and update it again (violation prompt)

.

5. Experimental case: create a read-only view

1) create a read-only view and query the view using read only

.

3) update the view again, indicating that you cannot dml the read-only view.

.

6. Experimental case: create a view with errors

1) use force to create a view with errors, where the venmast table does not exist, but can be created successfully.

.

2) create tables

.

3) recompile the existing view to make it effective.

.

4) Test query view

.

7. Create a view of the order by clause (it will be sorted automatically after querying the view)

.

Then create a descending one and query the view again

.

DML statements and complex views

DML statements are insert, delete, and update statements used to modify data. Because the view is a virtual table, these statements can also be used with the view. In general, the data is not modified through the view, but the base table is modified directly, because the regulations are clearer. Using a DML statement on a view has the following restrictions (relative to the table).

1) the DML statement can modify only one base table in the view.

2) if the modification of the record violates the constraints of the base table, the view cannot be updated.

3) if you create a view that contains join operators, DISTINCT operators, collection operators, aggregate functions, and groupby clauses, you will not be able to update the view.

4) if you create a view that contains pseudo-column expressions, you cannot update the view.

.

A simple view is based on a single base table, excluding functions and grouping functions, so you can perform insert, update, and delete operations in this view, which actually insert, update, and delete rows in the base table.

Complex views extract data from multiple tables, including function grouping functions. Complex views are not necessarily capable of DML operations.

.

1) query view

You can query the names of views created under the current user through the data dictionary USER_VIEWS.

.

2) Delete the view

To delete a view from the database, use the drop view command.

.

Materialized view

1) the meaning of materialized view

Materialized views are suitable for ordinary views. When oracle uses normal views, it repeatedly executes all the sql statements that create views. If such sql statements contain multiple table joins or order by clauses, and the table has a large amount of data, it will be very time-consuming and inefficient. In order to solve this problem, oracle put forward the concept of materialized view.

To put it simply, a materialized view is a special view with physical storage that occupies physical space, just like a table. Materialized views are created based on tables, materialized views, and so on. He needs to synchronize with the source table and constantly refresh the data in the materialized view.

There are two important concepts in materialized views: query rewriting and synchronization of materialized views. \

.

Query rewriting:

Rewrite the sql statement, when the user uses the SQL statement to query the base table, if the materialized view based on these tables has been established, oracle will automatically calculate and use the materialized view to complete the query. In some cases, it can save query time and reduce the system iActiono. Oracle calls this query optimization technique query rewriting. The parameter QUERY_REWRITE_ENABLED, which determines whether to use a rewrite query, is Boolean. You need to use ENABLE_QUERY REWRITE to start the query rewriting function when creating materialized views. You can view the value of this parameter through the SHOW directive.

.

2) synchronization of materialized views:

The materialized view is created based on the table, so when the base table changes, it is necessary to synchronize the data to update the data in the materialized view, so as to maintain the data consistency between the materialized view and the base table. Oracle provides two ways to refresh materialized views and decide when to refresh them, namely, ON COMMIT and ON DEMAND.

ON COMMIT mode: means that the materialized view is refreshed while the DML operation transaction of the base table is committed.

ON DEMAND mode: it means that the materialized view is updated when the user needs it. It can be refreshed manually through DBMS_MVIEW.REFRESH, or regularly through JOB.

After selecting the refresh method, you also need to select a refresh type, which specifies how to synchronize the data between the base table and the materialized view when refreshing. Oracle provides the following four refresh types.

COMPLETE: completely refreshes the entire materialized view.

FAST: incremental refresh, refreshing only the changes made since the last refresh.

When refreshing, FORCE:oracle will determine whether it can be refreshed quickly, and if so, use FAST mode, otherwise use COMPLETE mode.

NEVER: materialized views do not do any refresh.

The default value is the FORCE refresh type.

.

3) create a materialized view

Prerequisites for ① to create materialized views

Permission to create materialized views, permissions for QUERY REWRITE, access to tables involved in creating materialized views and permissions to create tables.

Log in as sys and then grant the scott user permission to create a materialized view.

Grant create materialized view to scott

Grant query rewrite to scott

Grant create any table to scott

Grant select any table to scott

.

② creates materialized view log

The materialized view log is used when the user selects the FAST refresh type to incrementally synchronize changes in the base table.

Create a materialized view for the scott user's table DETP and table emp, so create a materialized view log for these two base tables.

Create materialized view log on dept with rowid

Create materialized view log on emp with rowid

.

③ create materialized view statement

To create a materialized view through create materialized view statement, you need to pay attention to the meaning of each parameter.

Create materialized view mtrlview_test

Build IMMEDIATE

Refresh fast

On commit

Enable query rewrite

As

Select d.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowid d_rowid,e.rowid e_rowid

From dept d,emp e

Where d.DEPTNO=e.DEPTNO

Command interpretation: bulid immediate: this parameter means to create a materialized view immediately, or you can select build deffered, which indicates that it will not be executed immediately after the materialized view is determined, but will be delayed and created when using the view.

Reffesh fast: the type of refresh data selects the FAST type.

ON COMMIT: update the materialized view immediately after an update is submitted to the base table.

ENABLE QUERY REWRITE: starts the query rewriting function. When creating a materialized view, it is clearly stated that query rewriting is enabled.

As: define the query statement that follows.

Query body: the query content of the materialized view, and the query result set of the sql statement is output to the materialized view and saved in a table created automatically by oracle.

.

Query materialized view:

Select * from mtrlview_test

④ deletes materialized views

Similar to deleting a normal view, you need to add a materialized keyword.

Query the view again, indicating that the view does not exist.

.

.

Sequence

Sequences are used to generate unique, consecutive integer database objects. Sequences are typically used to automatically generate values for primary or unique keys. Sequences can be sorted in ascending or descending order, which is the same as the automatic sorting of excel and the identifier of sqlserver. \

1. Create a sequence

1) use sys login to grant scott permission to create a sequence.

Grant create sequence to scott

Syntax:

CREATE SEQUENCE name

[START WITH integer]

[INCREMENT BY integer]

[MAXVALUE integer | NOMAXVALUE]

[MAXVALUE integer | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE integer | NOCACHE]

.

Explanation: START WITH: specifies the first sequence number to be generated. For ascending sequences, the default value is the minimum value of the sequence, and for descending sequences, the default value is the maximum value of the sequence.

INCREMENT BY: used to specify the interval between sequence numbers. The default value is 1. If n is positive, the resulting sequence is sorted in ascending order, and if n is negative, the resulting sequence is sorted in descending order.

MAXVALUE: specifies the maximum value that a sequence can generate

NOMAXVALUE: if NOMAXVALUE,oracle is specified, set the maximum value of the ascending sequence to 1027 and the maximum value of the descending sequence to-1.

MINVALUE: specifies the minimum value for the sequence. MINVALUE must be less than or equal to the value of START WITH and must be less than MAXVALUE.

NOMINVALUE: if NOMINVALUE,oracle is specified, set the minimum value of the ascending sequence to 1, or set the value of the descending sequence to-1026.

CYCLE: specifies that the sequence continues to generate values from scratch after it reaches its maximum or minimum value.

NOCYCLE: specifies that the sequence will not continue to generate values after it reaches the maximum or minimum value.

CHCHE: using the CACHE option, you can pre-assign a set of serial numbers and keep them in memory, so that you can access the serial numbers more quickly, and when you run out of all the serial numbers in the cache, oracle will generate another set of values and keep them in the cache.

NOCACHE: using the NOCACHE option, the sequence number is not pre-assigned to speed up access. If you omit the CACHE and NOCACHE options when creating a sequence, oracle caches 20 sequence numbers by default.

.

Example 1: create the serial number, starting from the serial number 10, increase by 1 at a time, the maximum is 2000, do not loop, and then increase the error.

Create sequence toy_seq

Start with 10

Increment by 1

Maxvalue 2000

Nocycle

Cache 30

.

2. Access sequence

After you create a sequence, you can access the value of the sequence through the NEXTVAL and CURRVAL pseudo columns. You can select a value from a pseudo column. But you can't manipulate their values.

NETXVAL: the first time you use NEXTVAL after creating a sequence, the initial value of the sequence is returned. When NETXVAL is referenced later, the value of the INCREMENT BY clause is used to increment the sequence value and return this new value.

CURRVAL: returns the current value of the sequence, which is the value returned the last time NEXTVAL is referenced.

Example 2: in the toy table, the identity column toyid is required as the identity, which does not need to have any meaning, but can be used as the primary key.

1) create a table

Create table toys (

Toyid number not null

Toyname varchar2 (20)

Toyprice number

)

.

2) insert data

Insert into toys (toyid,toyname,toyprice)

Values (toy_seq.nextval,'car',25)

Insert into toys (toyid,toyname,toyprice)

Values (toy_seq.nextval,'kitty',85)

.

3) query data

Select * from toys

.

4) View the current value of the sequence

Currval returns the current value of the sequence, which is the value returned the last time NEXTVAL is referenced.

.

5) Test currval

It is found that writing data again after restarting the instance starts from 40, because according to the requirements of creating a sequence, 30 sequence numbers will be put into the cache each time. After the instance is restarted, the sequence in the cache will disappear.

Insert into toys (toyid,toyname,toyprice)

Values (toy_seq.nextval,'snowboy',92)

.

3. Change the sequence

The Alter sequence command is used to modify the definition of a sequence. The sequence is modified if you want to do the following.

Set or delete MINVALUE or MAXVALUE

Modify incremental valu

Modify the number of sequence numbers in the cache

Cannot change the START WITH parameter of a sequence

.

Example 3: set up a new MAXVALUE and open CYCLE for the toy_seq sequence.

ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE

.

Example 4: modify the sequence to have no maximum capped value

.

Example 5: set each increment to 10

.

4. View the sequence

You can get the details of the sequence created by the user by querying the data dictionary view named USER_SEQUENCES.

.

5. Delete the sequence

The DROP SEQUENCE command is used to delete sequences.

DROP SEQUENCE toys_seq

.

Synonym

A synonym is an alias of an object that does not take up any actual storage space and only saves its definition description in the data dictionary of oracle. When using a synonym, oracle will translate it into the name of the corresponding object.

1. The use of synonyms

1) simplify the sql statement

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 name and owner of the object can be shielded. If there are no synonyms, when manipulating other users' tables, you must operate in the form of "user name. Table name". After using the synonym oracle, you can hide the user name. For example, if user user1 wants to access the EMP table of a user's SCOTT, it must be referenced by SCOTT.emp. If you create a synonym named emp for SCOTT.emp for the user, user1 can use that synonym to reference SCOTT.emp as if it were your own table.

3) provide location transparency for remote objects in distributed databases

To complete the access to remote objects, you must first understand the concept of database connection. A database link is a named object that indicates the path from one database to another, through which communication between different databases can be realized. The role of synonyms in database links is to provide location transparency.

4) provide public access to database objects

Public synonyms simply define a public alias for database objects, that is, other users can access through this alias, but can be accessed successfully through that alias, depending on 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

Private synonyms can only be accessed within their schema and cannot have the same name as the object of the current schema

Public synonyms can be accessed by all database users

.

2-1: private synonyms

Private synonyms can only be accessed by users of the current schema, and the private synonym name cannot be the same as the object name of the current schema. To create private synonyms in your own schema, the user must have create synonym system permissions. To create private synonyms in other user modes, the user must have create any synonym system permissions.

2) the syntax for creating private synonyms is as follows:

CREATE OR REPLACE SYNONYM [schema.] synonym_name FOR [schema.] object_name

Synonym_name: the name of the synonym to create

Object_name: specifies the name of the object for which you want to create synonyms.

.

Example 1: create a private synonym in SYSTEM mode to access the EMP table in SCOTT mode.

1) Log in to the database as SYSTEM and access the EMP table under SCOTT.

.

SQL > conn system/123456

SQL > select * from scott.emp

As you can see from the figure above, using the schema name does implement the query, but it exposes the schema information of the emp table, and the use of private synonyms can avoid this problem.

.

2) Log in to the database as SYSTEM and create synonyms.

SQL > create synonym SY_EMP for scott.emp

.

3) access the synonym sy_emp, which actually accesses the emp table of SCOTT (hides the real table name and improves security)

Select * from sy_emp

.

Example 2: access to the table scott.emp in a remote database named orclsv

I only have one server here, so I think of myself as a remote server, and the effect is the same.

1) Log in to the database as a SYSTEM user, create a database connection dblink_sw_orcl to connect to the remote database, where the remote database user name is system, the password is pwd123, the local network server is named orcl, and finally query the table emp in the remote database.

SQL > create database link dblink_sw_orcl connect to system identified by pwd123 using 'orcl'

SQL > select * from scott.emp@dblink_sw_orcl

.

2) create a private synonym sy_t as an alias for the remote database table emp

SQL > create synonym sy_t FOR scott.emp@dblink_sw_orcl

.

3) access the synonym sy_t, which corresponds to the table in the remote database.

SQL > select * from sy_t

.

.

.

.

Public synonym

Public synonyms are accessed by all databases. Public synonyms can hide the identity of the base table and reduce the complexity of sql statements. To create a public synonym, the user must have system permissions for create public SYNOYM.

Example 3: create a public synonym public_sy_dept on the department table dept in scott mode so that other users can access public_sy_dept directly.

Note: if you do not create public synonyms, other users must add the scott prefix, that is, SCOTT.xxx, to access synonyms created in scott mode. If a public synonym is created and the synonym has a public attribute, then other users can use it.

1) Log in to the database as system, and you will create public synonym permissions to SCOTT users.

.

SQL > grant create public synoym to scott

.

2) Log in to the database as SCOTT user

.

SQL > conn soctt/123456

.

3) give the permission to query tmp to ydw

SQL > gran select on dept to ydw

.

.

4) create a public synonym PUBLIC_SY_DEPT as an alias for the dept table of SCOTT users

SQL > create public synonym public_sy_dept FOR dept

.

5) Log in to the database as ydw

SQL > conn ydw/123456

SQL > select * from public_sy_dept

.

3. Delete synonyms

To delete synonyms, the user must have the appropriate permissions.

Example: delete the synonym sy_emp and the public synonym public_sy_dept, you can execute the following statement.

SQL > drop synonym sy_emp

SQL > drop public synonym public_sy_dept

.

Partition table

1. The meaning of partition table

Oracle allows you to divide all rows of a table weight into parts and store them in an impassable table space, each part of which becomes a partition, and the partitioned table becomes a partitioned table.

.

Partitioning is useful for tables that contain large amounts of data, with the following advantages:

1) improve the query performance of the table. After partitioning a table, users can access only a specific partition in the table instead of the entire table when performing an sql query.

2) tables are easier to manage. Because the data of a partitioned table is stored in multiple sections, it is easier to load and delete data by partition than to load and delete data in the table.

3) it is convenient for 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 possibility of data corruption for all partitions at the same time.

.

A partition table can be built by combining a conditional table:

1) the amount of data is larger than that of 2GB.

2) there is a clear demarcation between the existing data and the newly added data.

Table partitioning is transparent to users, and applications can operate as regular tables when updating and querying partitioned tables without knowing that tables have been partitioned, but the oracle optimizer knows that tables have been partitioned.

.

Note: the table to be partitioned cannot have columns of LONG and LONG RAW data types.

.

Classification of partition tables

The partition methods provided by Oracle include range partition, list partition, hash partition, compound partition, interval partition, virtual column partition and so on. Interval partition and virtual column partition are new features of oracle11g.

.

Range Partition case:

Is a commonly used method of table partitioning, which is the first partition type introduced by oracle. Range partitions are used for data that can be separated by range according to certain conditions. If the data is evenly distributed in the different ranges established, then using range partitioning will get the best partitioning effect. Ranges can be based on sequential or partial numbers, and range partitioning techniques are usually based on time (such as month or quarter)

1) create a table and partition it to age.

Create table student (

Id number

Name varchar2 (10)

Age number)

Partition by range (age)

(partition P100 values less than (10)

Partition p200 values less than (20),)

.

2) insert data into the table

Insert into student values (1 recording, 1 recording, 8)

Insert into student values (2 and 2)

Insert into student values (3 minutes, 3 minutes, 15)

Insert into student values (4 minutes 4 minutes 18)

.

3) query the data of P100 area

Select from student partition (P100) *

Query the data of p200 area

.

4) if you insert the following record into the table, you will be prompted that the inserted partition keyword is not mapped to any partition

Insert into student values (5 minutes 5 minutes 50)

.

5) Partition by range. If some records cannot predict the range temporarily, you can create a maxvalue partition, and all records that are not within the specified range will be stored in the partition where the maxvalue resides.

Alter table student add partition p300 values less than (maxvalue)

.

6) insert the following data again

Insert into student values (5 minutes 5 minutes 50)

.

7) query

Select * from student partition (p300)

.

8) commands for viewing all partitions

Select partition_name,table_name

From user_tab_partitions

Generally, when creating a range partition, the last partition will be set to maxvalue, and other data will fall into this partition. Once needed, you can use the technology of splitting the partition to separate the needed data from the last partition and form a separate partition. If you do not create the largest partition, the inserted data will check out the range and will report an error. If the inserted data is a value on the partition key, the data falls into the next partition, for example, if the inserted data is 10, it falls into the p200 partition.

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.