Oracle SQL Commands Encyclopedia _oracle

Delete a large table when the space is not released, very slow because of a large amount of system resources, support fallback operation, space is also occupied by this table.
TRUNCATE TABLE name (frees table space when records are deleted from a table)
DML Statement:
Table-Level shared locks: does not affect the operation of different records in a single table
Row-Level exclusive lock: For a row of records, Oracle will allow only one user to modify it at the same time
Wait () for data operations until row-level locks are freed
When a table is dropped, the table is also locked and the DDL is locked, so the table cannot be deleted if there are currently user action tables when you delete a table
The ALTER TABLE command is used to modify the structure of the table (these commands are not used frequently):
To increase the constraint:
ALTER TABLE name ADD CONSTRAINT constraint name primary key (field);
Unbind: (delete constraint)
ALTER TABLE name drop PRIMARY key (this method can be used directly for primary KEY constraints because there is only one PRIMARY KEY constraint name in a table, note that errors occur if the primary key is deleted when there are other table references at this time)
Alter Tbale father drop PRIMARY key cascade; (If you have a child table that references a primary key, you use this syntax to delete the primary key, and the child table exists only the FOREIGN key constraint in the child table is deleted)
ALTER TABLE name DROP CONSTRAINT constraint name;
(How to take a constraint name: 1, artificial violation of the rules according to error information to obtain!)
2, query map to get the constraint name!)
ALTER TABLE name disable from primary key; (equivalent to disabling the primary key of a table)
ALTER TABLE name enable primary key; (Enable will automatically check the records of the table is not meet the requirements, if there is dirty data must first delete dirty data to enable)

*******************************************************************
Add fields:
ALTER TABLE name Add (field Word field type)
To delete a field:
ALTER TABLE name drop (field)
Alter Tbale table name drop column field; (8i only after support)
Renaming a column: 920 only to support
ALTER TABLE name rename column Old field name to new field name;
modifying fields
(at this point should pay attention to the problem, change to see the specific value of the case between the transfer, to the character type, you must be empty)
ALTER TABLE name modify (field, type)
To change a field in a table:
Update table name Set field = value Where condition
Change table name
Rename old table name to new table name;
To delete a table:
Trucate Table name: (table structure is still in, the data are all deleted, release the space occupied by the table, do not support fallback, commonly deleted large table)

About generating sequences in oralce (sequence):
Create sequence sequence name alter system flush Shared_pool;
(Default to start at 1 with no parameters) in order to increase the efficiency of the producing sequence, 20 sequences are usually generated at one time in the sequence pool of the current session to accelerate efficiency, and the sequence will have discontinuous action rollback does not affect the sequence value.
Parameters for sequence:
Increment by n starting value, start with n increment, MaxValue n maximum, minvalue n Minimum, cycle | No cycle reincarnation, Cache N Shou (how many IDs will be saved at a time for the first fetch)
View Sequence diagram:
Desc user_sequences;
Select Sequence_name, Cache_size, last_number from user_sequences where sequence_name like ' s_ ';
Select sequence name. Currval from dual View the current number of sequences
The select Sequence name. Nextval from dual View the next sequence number, which automatically adds 1 to the current sequence
As columns: Nextval currval
(When the current value is not successful when you open another session, you should first take the next value and then the current value)
Empty the current session memory:
alter system flush Shared_pool; (You must have DBA authority to execute this command, and a normal user will perform an error)
Modify sequence: (This command is not commonly used, just understand on the line do not need to delve)
Alter SEQUENCE sequence name modification item;
Delete sequence sequence
Drop sequence sequence name;
Create a diagram: Creating Views (belonging to understanding knowledge)
Desc user_views;
Select text from user_views where view_name= ' test1_v1 ';
The diagram is equivalent to a SELECT statement that defines an illustration that defines an SQL statement that does not occupy space, and that using view does not improve performance, but simplifies the SQL statement
(Extended Knowledge: Oracle 8i new figure) MV materialized view (storage space, the select results exist in a space, will improve query view, enhance real-time, but there is a refresh problem, mainly used in the data warehouse to use for aggregation table)
Benefits of using diagrams: Controlling data access permissions.
How to create an illustration:
Create or Replace views Test_vi as SELECT * from Test1 where c1=1;
When inserting data into table test1 (base table): The table does not change, and the data in the illustration changes
The table that corresponds when you insert data from the illustration will change:
When you insert data into the diagram, it is inserted directly into the base table, and when you look at the data in the diagram, the equivalent is the SELECT statement when you execute the creation.
Simple diagram: DML operations are possible.
Complex illustration: From more than one table, you cannot perform DML operations.
About RowNum:
RowNum has a feature that is either equal to 1 or less than a value, cannot be directly equal to a value, cannot be greater than a value. RowNum is often used for paging display.
Exercise: Query between the 5th and 10th data:
Select First_Name, Rnum from (select RowNum rnum, first_name from s_emp where RowNum <=10) where rnum between 5 and 10;
Faceted display:
SELECT * FROM (select A.*, RowNum r from S_emp a WHERE R between 5 and 10);
Practice: Which employees pay more than the average wage in the department?
Select First_Name, salary, avgsal from S_emp E, (select dept_id, avg (Salary) avgsal to s_emp Group by dept_id) a where e.dept_id =a.dept_id and e.salary > a.avgsal;
Adding a with CHECK option on the illustration is equivalent to adding a constraint to the diagram
CREATE VIEW Test_v as SELECT * from Test where C =1 with CHECK option;
Synonyms: Equivalent to the role of alias (* * * only need to understand * * *) the system built synonyms: user_tables
Create synonym asd_s_emp for asd_0607.s_emp;
The purpose is to give the Asd_0607_s_emp table another substitute for the name asd.s_emp; Note that this synonym can only be used by itself;
Create public synonym p_s_emp fro asd_0607.s_emp; Create a common synonym, but you want permissions.
To delete a synonym:
drop synonym synonym name
CREATE INDEX: Creating indexes (concept is important for system performance impact very much)
The purpose of indexing is to speed up the query.
The index is relative to the directory of a book. Index point system Space, which belongs to the appendage of the table. When you delete a table, the corresponding index is also deleted. The index structure is in the TRUNCATE table, but the data does not exist.
Full table Scan Scan
Indexing is a quick way to locate data: (for example, in the dictionary directory for understanding)
To view the rowid of a table:
Select rowID, first_name from S_emp;
The information defined by ROWID is: Object Block table
Every record has its own rowid.
Who created the index: User, indexing will make DML operations slow, but it will be more efficient for user queries, which is the ultimate goal of indexing,
To create an index:
Create index index name on table name (field name);
Create Insex Testindex on test (c1, C2);
Which fields should be indexed:
Always use the WHERE clause, so use the index. Using no index, the key depends on the percentage of the queried data and all the data, the larger the table, the less the query records, and the higher the index efficiency.
Substitution variables: Use & symbols to define substitution variables support interactive hints, for character numbers, be sure to write between single quotes
Set Verify on
Set verify off;
Equivalent to a switch variable that controls whether the old and new SQL statements are displayed
Select ID, last_name, salary from s_emp where title= ' &job_title ';
To change the message of the interaction:
Accept P_dname prompt ' hint information ';
To define a variable:
Define p_dname= ' abc ';
Pagination Implementation statement: (can run correctly)
SELECT * FROM (select RowNum rnum, a.* from (SELECT * from S_emp) a) where rnum between 5 and 10;


This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/andymu077/archive/2009/06/24/4293261.aspx