Oracle SQL commands

Delete deletes a large table. It is not released between time and space. It is very slow because it occupies a large amount of system resources and supports rollback. The space is also occupied by this table.
Truncate table Name (tablespace is released when table records are deleted)
DML statement:
Table-level shared locks: they do not affect each other when operating different records in a table.
Row-level exclusive lock: oracle only allows one user to modify a row of records at the same time.
Wait () performs data operations only when the row-Level Lock is released
Drop a table also locks the table, DDL exclusive lock, so when you delete a table, if there are still users operating the table, the table cannot be deleted.
The alter table command is used to modify the table structure (these commands are not often used ):
Add constraints:
Alter table name add constraint name primary key (field );
Lift constraints: (delete constraints)
Alter table Name drop primary key (this method can be used directly for primary key constraints, because a table has only one primary key constraint name, note: If the primary key is referenced by another table, an error occurs when the primary key is deleted)
Alter tbale father drop primary key cascade; (if a subtable references a primary key, use this syntax to delete the primary key, at this time, the child table still exists, but the foreign key constraint in the child table is deleted and associated)
Alter table Name drop constraint name;
(How to Get a constraint name: 1. Manually obtain the violation according to the error message!
2. query the graph to obtain 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 whether the records meet the requirements. If there is dirty data, you must first Delete the dirty data before enable)

**************************************** ***************************
Add field:
Alter table name add (field type)
Delete field:
Alter table Name drop (field)
Alter tbale Table Name drop column field; (8i later)
Rename a column: 920 is supported
Alter table name rename column old field name to new field name;
Modify Field
(In this case, we should pay attention to the problem. The change should be based on the transfer between values. When changing to the character type, it must be blank)
Alter table name modify (field, type)
Modify fields in the table:
Update table name set field = value where Condition
Change table name
Rename old table name to new table name;
Delete table:
Trucate table Name: (The table structure is still in progress, all data is deleted, the space occupied by the table is released, rollback is not supported, and large tables are often deleted)

About the sequence generated in oralce ):
Create sequence name alter system flush shared_pool;
(When parameters are not included, the default value is 1 increments from 1. To improve the generation sequence efficiency, oracle generally generates 20 sequences at a time and puts them in the sequence pool of the current session for backup to accelerate efficiency, the sequential rollback operation will not affect the sequence value)
Sequence parameters:
Increment by n start value, start with n increment, maxvalue n maximum, minvalue n minimum, cycle | no cycle, cache n records (the number of IDs that will be retrieved at one time during the first fetch)
View the 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 to view the current number of Sequences
Select sequence name. nextval from dual to view the number of the next sequence. It will automatically add 1 to the current sequence
Column: nextval currval
(If the current value is unsuccessful when another session is opened, the next value should be taken first, and then the current value should be taken)
Clear the memory of the current session:
Alter system flush shared_pool; (the DBA permission is required to execute this command. Generally, an error occurs)
Sequence Modification: (this command is not commonly used. You only need to know it)
Alter sequence name modification item;
Delete sequence
Drop sequence name;
Create a graph: creating views (knowledge)
Desc user_views;
Select text from user_views where view_name = 'test1 _ V1 ';
A graph is equivalent to a select statement. A graph is a SQL statement that does not occupy space. Using view does not improve performance, but simplifies SQL statements.
(Extended knowledge: new charts after oracle 8i) MV materialized views (occupying storage space, there is a space for the select results, which will improve the query view and enhance real-time performance, but there is a refresh problem, the main application is used in the data warehouse to aggregate tables)
Benefits of using the graph: control data access permissions.
How to Create a graph:
Create or replace views test_vi as select * from test1 where c1 = 1;
When data is inserted into table test1 (base table), the table does not change, indicating that the data in the figure has changed.
When inserting data from the graph, the corresponding table changes:
When inserting data into the graph, it is directly inserted into the base table. when viewing the data in the graph, it is equivalent to executing the select statement at the time of creation.
Simple graph: DML operations can be performed.
Complex Graph Display: It is from multiple tables and cannot perform DML operations.
About rownum:
Rownum has a feature that is either equal to 1 or smaller than a value. It cannot be directly equal to or greater than a value. Rownum is usually used for pagination.
Exercise: Query between 5th data records and 10th data records:
Select first_name, rnum from (select rownum rnum, first_name from s_emp where rownum <= 10) where rnum between 5 and 10;
Split display:
SELECT * FROM (SELECT a. *, rownum r FROM S_EMP a WHERE r between 5 AND 10 );
Exercise: which employees have higher salaries than the average salaries of their respective departments?
Select first_name, salary, avgsal from s_emp e, (select dept_id, avg (salary) avgsal from s_emp group by dept_id) a where e. dept_id =. dept_id and e. salary>. avgsal;
Adding a with check option on the graph is equivalent to adding constraints to the graph.
Create view test_v as select * from test where c = 1 with check option;
Synonym: it is equivalent to the alias function (*** only needs to know ***). The synonym used by the system is user_tables.
Create synonym asd_s_emp for asd_0607.s_emp;
The goal is to give the asd_0607_s_emp table another replacement name asd. s_emp. Note that this synonym can only be used by yourself;
Create public synonym p_s_emp fro asd_0607.s_emp; create a public synonym, but you must have the permission.
Delete synonym:
Drop synonym name
Create an index: Creating indexes (the concept is very important and has a great impact on the system performance)
The purpose of index creation is to speed up the query.
The index is like the directory of a book. Index point system space, which belongs to the table's attachments. 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
Index is used to quickly locate data: (take the dictionary directory as an example)
View the rowid of a table:
Select rowid, first_name from s_emp;
Rowid defines the following information: object block table
Each record has its own rowid.
Who created the index: After the index is created, the efficiency of DML operations will be slow, but the efficiency of user queries will be improved. This is the final purpose of index building,
Create an index:
Create index name on table name (field name );
Create insex testindex on test (c1, c2 );
Which fields should be indexed:
The where clause is often used, so the index is used. if no index is used, the key is to view the data queried and the percentage of all data. The larger the table, the fewer query records and the highest index efficiency.
Replace variable: Use the & symbol to define the replace variable. Interactive prompts are supported. Character numbers must be written between single quotes.
Set verify on
Set verify off;
Equivalent to a switch variable, used to control whether to display new and old SQL statements
Select id, last_name, salary from s_emp where title = '& job_title ';
Change interaction prompt information:
Accept p_dname prompt 'prompt information ';
Define variables:
Define p_dname = 'abc ';
Paging implementation statement: (can run normally)
Select * from (select rownum rnum, a. * from (select * from s_emp) a) where rnum between 5 and 10;


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