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

Blogger QQ:819594300

Blog address: http://zpf666.blog.51cto.com/

Friends who have any questions can contact the blogger, the blogger will help you answer, thank you for your support!

We introduced some related knowledge points of this content when learning sqlserver2008R2, including: transaction, index, view and so on. So today we learn to implement these important things on oracle in order to optimize the database.

I. Affairs

1. Introduction

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.

2. 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.

3. Learn to control 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.

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

Note: as long as the set autocommit on command is executed in advance, the data will be submitted automatically, and the data will still be saved after the rollback is executed 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) inquire

(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 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)

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

(3) Roll back the transaction

(4) write data again

(5) exit sqlplus

(6) if you look at the records in the students 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:

1. 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.

2. Commit and rollback statements should only be applied to 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.

II. Index

1. 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.

2. the 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

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

3. Classification of index

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_namerebuld

3) merge index fragments

SQL > alter index index_name coalesce

4) Delete index

SQL > drop indexindex_name

Example 1:

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

5. Create a unique index

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

ONtablename (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 indicate 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 cannot be created above, indicating that 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:

Then create the reverse key index:

Whether the query was established successfully:

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 graph 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 weitu ON emp (job)

Check whether the establishment is successful:

8. Composite 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.

9. 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.

Now create a function-based index:

Check whether the establishment is successful:

10. Principles for creating indexes

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

2) modify the index to the table space.

3) check whether to modify it.

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

Example 2: using the nologging clause

11. View the information about the index column: index name, table name, index column.

12. Maintain the 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.

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.

3) Delete index

III. View

1. 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.

2. the advantages of the view:

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.

3. The syntax for creating a view:

1) CREATE [OR REPLACE] [FORCE] VIEW

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

ASselect_statement

[WITH CHECKOPTION]

[WITH READONLY]

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 ORDERBY 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 ORDERBY 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 createview 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 SHOWERRORS VIEW view name to view errors.

4. Experimental case: operation on single table view

1) Connect to oracle and log in using scott user

2) create table order_master

3) insert data

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

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)

11) look at the final result

5. Experimental case: create a read-only view

1) use read only to create a read-only view

2) query view

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

8. 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.

9. Materialized view

1) the meaning of materialized view

The materialized view corresponds to the normal view. 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.

② 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 statement

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

Query materialized view:

④ 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.

IV. 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.

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

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

2) insert data

3) query data

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

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 xulie sequence.

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.

5. Synonyms

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 createany synonym system permissions.

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

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

[OR REPLACE]: replace a synonym if it exists

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.

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

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

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 123456, the local network server is named orcl, and finally query the table emp in the remote database.

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

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

2-2: public synonyms

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.

2) Log in as sys user and create a new user zpf

3) Log in to the database as scott user and give zpf the right to query tmp

4) create a public synonym public_dept as an alias for the dept table of scott users

5) Log in to the database as sys user and give zpf the right to log in to the database

6) Log in to the database as zpf

3. Delete synonyms

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

Example: delete the synonym tyc and the public synonym public_dept, you can execute the following statement.

1) Log in as sys and give zpf permission to delete private and public synonyms

2) Log in as zpf to delete the private synonym tyc and public synonym public_dept created by system

VI. Partition table

1. The meaning of partition table

Oracle allows you to divide all rows in a table into parts and store them in different table spaces, 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.

Compose a table of what conditions can be built into a partition 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.

2. 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

2) insert data into the table

3) query the data of P1 area

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

5) when partitioning 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.

6) insert the following data again

7) query

8) commands for viewing all 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 20, it falls into the p2 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.