Oracle automatically adds a primary key field to generate

Source Description: This post, the original web site http://www.cnblogs.com/leup/archive/2007/11/18/962952.html

Reason for posting: The Oracle Auto-increment field is different from the SQL field, but the call is very interesting. Multiple users call it at the same time and no unique ID is generated.

 

There are automatically numbered Data Types in access, and both MSSQL and MySQL have auto-increasing data types. When you insert a record, you do not need to operate on this field, and the data value is automatically obtained, oracle does not have an auto-increasing data type. We need to create an auto-increasing serial number. When inserting a record, we need to assign the next value of the serial number to this field. It is foreseeable that this function is available, we can migrate data from access, MSSQL, or MySQL to Oracle!
Create sequence type_id increment by 1 start with 1;
In this sentence, type_id is the name of the serial number. Each time it increases to 1, the Starting sequence number is 1.
If you want to delete a sequence, use the drop sequence name !!

A sequence can ensure that multiple users generate a unique integer when performing operations on the same table. A sequence can be used to automatically generate a primary keyword. The sequence only exists in the data dictionary.

Create sequence Sequence
[Increment by N]
[Start with N]
[{Maxvalue n | nomaxvalue}]
[{Minvalue n | nominvalue}]
[{Cycle | nocycle}]
[{Cache n | nocache}];
Increment by -- specifies the step size
Start with -- specify the Initial Value
Maxvalue -- defines the maximum number generated by the sequence. The default value of maxvalue is nomaxvalue. The ascending sequence is 10 ^ 27, and the descending sequence is-1.
Minvalue -- defines the minimum number of a sequence. The default minvalue is nominvalue. For an ascending sequence is 1, the descending sequence is-10 ^ 26.
Cycle -- number of the configuration sequence when the limit value is reached
Nocycle -- do not repeat the number when the threshold value is reached. This is the default value. If you try to generate maxvalue + 1, an exception is returned.
Cache-defines the size of the serial number block retained in the memory. The default value is 20.
Nocache-forces the data dictionary to update each generated sequence number to ensure that there is no vacancy in the generated number, but this will reduce the performance.

Generate a sequence
Create sequence dept_deptid_seq
Increament by 10
Start with 120
Max value 9999
Nocache
Nocycle;
// If it is used to generate the primary key value, do not use the cycle option, and it is best to reflect its potential use in naming sequence for ease of understanding.

Validation Sequence
Select sequence_name, min_value, max_value, increament_by, last_number
From user_sequences;
// If you specify the nocache option, the last_number column displays the next available serial number.

Nextval can be used to access the next serial number in the sequence, but the problem often occurs when the current serial number currval is queried before the initial sequence of the session.
Create sequence emp_seq
Nomaxvalue
Nocycle;
Then Query
Select emp_seq.currval
From dual;
An error will be returned. The problem is that before your view references currval, nextval is not used in your session to initialize the sequence first.
Select emp_seq.nextval
From dual;
In this way, no errors will occur when querying currval.

Use Sequence
Insert into orders ments (department_id, department_name, location_id)
Values (dept_deptid_seq.nextval, 'support', 2500 );

Caching and storing sequences can improve performance, because you do not have to update the data dictionary table for each generated number. You only need to update each group of numbers. in this way, we can directly extract data from the buffer when querying nextval, and the speed will be much faster. However, when the database is rolled back, for example, the system crashes, when you manually roll back the data, the sequence values stored in the buffer will be lost, which is why there is a gap (GAPS). If nocache is specified when the sequence is generated, you can query the next available serial number value in the user_sequences table. This query does not generate an action to increase the sequence value.

Modify Sequence
Alter sequence dept_deptid_seq
Increment by 20
Max value 999999
Nocache
Nocycle;
Rules:
> It must be the sequence owner or have the alter privilege.
> The modification takes effect for subsequent serial numbers.
> The sequence must be deleted and re-generated (invalidates all related objects and loses their associations)
> Some other verification conditions must be met during modification. For example, the new maxvalue cannot be lower than the current serial number.

Delete Sequence
Drop sequence dept_deptid_seq;
> It must be the sequence owner or have the permission to drop any sequence.

Index
An index is a data structure that greatly improves system performance compared to the default full table scan by obtaining specific row information. however, manual creation can also be automatically generated by Oracle. They are independent of the index table, that is, indexes can be created or deleted at any time without any impact on the base table or other indexes (when you delete a table, the related indexes will be deleted ).
There are two types of Indexes
> Unique index-a unique index is automatically generated when you define a column that contains a primary key or a unique key constraint (you can create it manually, but it is recommended that it be automatically created by Oracle)
> Non-unique index-when you manually create a foreign key index for a connection in a query to accelerate the query speed

Create an index
Create Index
On Table (column [, column]...);
E.g.
Create index emp_last_name_idx
On employees (last_name );

When to use Indexes
> When a column contains a large value range
> When a column contains a large number of null values
> One or more columns are often used together in the WHERE clause or join clause.
> The table is very large, but most queries only need to retrieve Row Records less than 2-4 age points.

When should I use indexes?
> Table hours
> Columns are not often used as conditions in queries.
> Most queries retrieve rows with more than 4 percentage points
> Tables are updated frequently.
> The index column is referenced as part of the expression.

Although the index can greatly improve the search performance, the more indexes, the more effort Oracle will spend to update the index after the DML operation. Therefore, you must use the index in a timely manner,

Confirm Index
You can query the index information in the data dictionary views user_indexes and user_ind_columns.
Select IC. index_name, IC. column_name,
IC. column_position col_pos, ix. uniqueness
From user_indexes IX, user_ind_columns IC
Where IC. inde_name = IX. index_name
And IC. table_name = 'ployees ';

Function-based index
Create index upper_dept_name_idx
On attributes (upper (department_name ));
// However, If you want to ensure that Oracle uses an index instead of a full table scan, you must ensure that the value of the letter is not null, that is, you must add a where clause to specify a non-null value, such
Select *
From employees
Where upper (last_name _ is not null
Order by upper (last_name );
If there is no WHERE clause, the full table scan will be performed instead of using the index.

Delete Index
Drop Index;
// When you delete a table, the indexes and constraints are automatically deleted, but the views and sequences are retained.

Synonym
Synonyms are often used to simplify SQL by specifying a common name for a local or remote object. A synonym can point to a table, view, sequence, process, function, or package in a local database, or point to an object in another database through a database connection. public synonyms can be used by all users, while private synonyms can only be used by their owners or authorized account owners.
For example, scott owns the table EMP. All users use their usernames to log on to the database and must reference this table, that is, Scott. EMP. If we generate a synonym for EMP, everyone with related privileges on the table can simply reference it in the form of EMP in their own SQL or PL/SQL statements without pointing out the owner.

Create [public] synonym Synonym
For object;

E.g.
Create synonym d_sum
For dept_sum_vu;

Drop synonym d_sum;

Create public synonym Dept
For Alice. attributes;

Drop public synonym dept;
// Only the database administrator can delete public synonyms.

 

 

The sequence of Oracle uses a sequence as a database object to generate a unique integer. Generally, the sequence is used to automatically generate the primary key value. For our programmers, we have limited energy and time, so we only learn the most useful knowledge. For details, see: 1) Create sequence [user.] sequence_name
[Increment by N]
[Start with N]
[Maxvalue n | nomaxvalue]
[Minvalue n | nominvalue];
[Nocycle] --
Increment by: Specifies the interval between serial numbers. This value can be a positive or negative integer, but cannot be 0. The sequence is ascending. When this clause is ignored, the default value is 1.
Start with: Specifies the first serial number generated. In ascending order, the sequence can start from a value greater than the minimum value. The default value is the minimum value of the sequence. For descending order, the sequence can start from a value smaller than the maximum value. The default value is the maximum value of the sequence.
Maxvalue: specifies the maximum value that can be generated by the sequence.
Nomaxvalue: set the maximum value to 1027 in ascending order and-1 in descending order.
Minvalue: specifies the minimum value of the sequence.
Nominvalue: specify the minimum value as 1 for ascending order. Specify a minimum value of-1026 for descending order.
Nocycle: Always accumulate, no loop 2) change the sequence command altersequence [user.] sequence_name
[Increment by N]
[Maxvalue n | nomaxvalue]
[Minvalue n | nominvalue];
The sequence can be modified:
Modify the increment of future sequence values.
Sets or removes the minimum or maximum value.
Change the number of buffer sequences.
Specify whether the serial number is ordered.

Note:
1. The first nextval returns the initial value.
2. You can alter all sequence parameters except start. To change the start value, you must drop sequence and re-create.

3) Delete the sequence command drop sequence [user.] sequence_name;
Deletes a sequence from a database. (4) try it out in a test. 4.1) create a serial number statement: -- create Sequence
Create sequence ncme_question_seq
Minvalue 1
Max value 999999999999
Start with 1
Increment by 1
Nocache;

//////////////////////////////////////// //////////////////////////////////////// /////////

4.2) how to obtain the serial number in SQL: Select ncme_question_seq.nextval from dual
Select ncme_question_seq.currval from dual Note: When Using Sequences, you sometimes need a user name, like this: insert into system. conservator (conservatorname, conpass, contruename, consex, conid) values ('jg', '000000', '000', 0, system. conid. nextval );