Oracle Two optimization modes
The WHERE clause in 1.CBO CBO mode has no effect on query speed
2.rbo will perform the index first under Rbo.
OLTP online transaction processing refers to systems that frequently change data.
OLAP table Data Analysis Index does not often change the system only take data for analysis use
subquery resolves an issue in which the problem cannot be solved one step at a time considering using subqueries:
Issues that are noted by subqueries:
1. Enclose the subquery in parentheses
2. Use a reasonable line feed and indent
3. You can put subqueries in the where from select of the main query
3.1The subquery after the select must be a single-row subquery
Select Ename, (select Job from emp where empno= ')
from EMP;
3.2 where,having behind the subquery with
= > >= < <= <> must be a single-line subquery
In any and not in can be a single-row subquery or a multiline subquery
3.3 Subquery after from is a virtual table
3.4 Query Department with minimum wage for each department greater than department ID is 50 minimum wage
Select department_id, min (Salary)
From Employees
GROUP BY department_id
Having min (Salary) > (select min (Salary)
From Employees
where department_id =50)
4. The subquery cannot be placed behind the group by of the main query
5. Emphasize the placement of sub-queries from behind
6. The main query and subquery can be not the same table, as long as the subquery returns the result, the main query can use
7. In general: ORDER by is not used in subqueries, but in top-n analysis, order by is required;
8. Single-line subqueries can only use single-line operators, multi-row subqueries can only use multiline operators
9. Null value in subquery
Multi-table queries can solve problems as much as possible with multiple table queries but with the exception of related subqueries:
Subquery (internal query) executes before the main query completes
The result of the subquery is used by the main query (outer query)
Multi-line query character
1.any: Comparison of any of the values of a subquery
--inquire about employee's salary which is higher than any employee in department 10th.
SELECT * FROM emp
where Sal > any (select Sal from emp where deptno = 10);
--in fact greater than is the minimum value of a department salary
-less than is less than one department of the maximum salary
2.all: Compare all values of the collection
SELECT * FROM emp
where Sal > All (select Sal from emp where deptno = 10);
--in fact greater than is greater than the maximum value of a department salary
-less than is less than a department of minimum wage
Null value problem in sub-query
No in (10,20,null) data not found
You cannot have null values by using not in. Because not in is compared with all values
In (10,20,null) can have data
Because in is a comparison with any value
Set operation
and set union/union all
Intersection Intersect
Subtraction Minus
Problems with SET operations
1. Each collection that participates in the operation must have the same number of columns, and the type is consistent
2. Use the header of the first set as the final table header
3.order by must be placed after each collection
GROUP BY rollup (A, B)
=group by A, b;
Uoion
Group by A;
Uoion
GROUP BY NULL;
Time to open SQL execution
Set timing on
Implicitly inserting null values to empty the hidden columns insert INTO (ename,age) VALUES (' HAH ', 18) All other columns are empty by default
Explicitly insert null value INSERT INTO EMP (' xx ', ' xx ', null,.....)
& Address Characters
INSERT into EMP (EMPNO,ENAME,SAL,DEPTNO) VALUES (&empno, ' &ename ', &sal,&deptno);
Select Empno,ename,&sal from EMP;
Address characters can be placed in all (DML) Action statements
Insert a collection into a table
INSERT INTO EMP10 SELECT * from emp where Deptno =10
--Insert to note that the number of columns must be the same type ...
Update tableName Set field =??
where
field =??
Delete TableName
where
field =??
Updates and modifications to consider the integrity of the data must be added WHERE clause in addition to deleting or modifying all
Common database objects
Table A collection of basic data stores, consisting of rows and columns
View Extracts logically related data collections from a table.
Sequence Provide a regular number of values
Index Efficiency in providing queries
Synonyms alias to Object
Naming rules
1. Must start with a letter
2. Must be between 1-30 characters
3. Must contain only A-Z a-Z 0-9 _ $ #
4. You must not duplicate the name of another user-defined object
5. Must not be a reserved word for Oracle
6.oralce default storage is all village capitalization
7. Database name can only be 1-8 bits, datalink (data link) is can be 128 bits, and some other special characters
CREATE table
Must have
CREATE TABLE Permissions
Storage space
CREATE TABLE "schema." Table
(column datatype "default expression")
Querying other users ' tables
Other users ' tables do not belong to this user's space
If you want to query a table under another user, use the user name of another user as the prefix
Data type
VARCHAR2 (size) variable long character data
char (size) fixed-length character data
Number (p,s) variable-length numeric data
Date Date type data
LongVariable long character data up to 2G
Clob Character data, up to 4G
Raw and long raw raw binary data
Blob Binary data up to 4G
bfile Store binary data for external files up to 4G
rowID Line Address
--use subqueries to quickly build tables
CREATE TABLE Empincome
As
Select Empno,ename,sal from EMP;
--Quickly build tables without data
CREATE TABLE Empincome
As
Select Empno,ename,sal from emp where 1<>1;
--create create alter to modify drop Delete (table)
--Append columns, modify columns, delete columns, rename columns (columns)
1. Append columns
Alter TALBE test2 add image blob;--add Increase
2. Modifying columns
ALTER TABLE test2 Modify Tname varchar2 (30);--modify modification
3. Delete Columns
Alter TALBE test2 drop column image;--drop Delete
4. Renaming columns
ALTER TABLE test2 rename column tname to username;--rename renaming
--Delete Table
drop table emp;--deleted in Oracle Recycle Bin
--oralce Recycling Station
1. View the Recycle Bin
Show RecycleBin;
--Delete the table completely
Drop Talbe EMP Purge; --delete completely will not be put into Recycle Bin
--The administrator does not have a recycle Bin or has DBA authority, no Recycle Bin
--constraints
--Constraint type
1. Not nulll
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
GENDER VARCHAR2 (4) CHECK (GENDER in (' Male ', ' female '));
or constraint emp_yueshu_name check (salary>0);
--FOREIGN KEY constraint
1. Delete in two steps
Delete the data before deleting the primary table or setting the child table data to NULL in the main table
2. One-step removal
FOREIGN KEY: In a child table, a table-level constraint is defined
REFERENCES: Specifying columns in tables and parent tables
2.1 On Delete CASCADE: When the parent table is deleted, Cascade deletes the child table records
2.2 on DELETE SET null: Sets the foreign key value of the dependent record for the child table to NULL-recommended use because cascading deletes are too risky
--The
CREATE TABLE MyPerson
(
PersonID varchar2 () constraint MYPERSON_PK primary key,
Name VARCHAR2 (constraint) myperson_name NOT NULL,
Gender VARCHAR2 (4) Constraint Myperson_gender check (gender in (' Male ', ' female '));
Email VARCHAR2 (+) constraint Myperson_email_u unique
Constraint myperson_email_n NOT NULL,
Deptno numberConstraint MYPERSON_FK References Dept (deptno) on DELETE CASCADE-Cascade Delete sub-table information
)
--Adding constraints
--PRIMARY key check unique foreign key
ALTER TABLE TABLENAME ADD constraint constraint name {"PRIMARY key" (Talbecolumnname) | "Check" (Talbecolumnname in (",") |talbecolum Nname >=< 10 ...) | "Unique" (Talbecolumnname) |
"Foreign Key (Talbecolumnname) references TableName (tablecolumnname) on DELETE Cascade"};
--Modify constraints
--not NULL
ALTER TABLE tableName modify COLUMNNAME constraint constraint name not null;
--defalut
ALTER TABLE TableName Modify ColumnName char (2) DEFAULT ' M ';
CREATE VIEW View1
As
SELECT * FROM EMP where deptno=10
with CHECK option; --only data that satisfies the view criteria can be inserted
Insert into View1 (**,10);--ok
Insert into View1 (**,20);--error
or with Read only; --read-only
--Disabling constraints
ALTER TABLE tableName DISABLE constraint name
--Enable constraints
ALTER TABLE TABLENAME enable constraint name
--Delay constraint
ALTER TABLE TableName employee ADD Constraint Fk_emp_infro foreign key (DEPTNO)
References Dept (DPTNO)
Deferrable initially deferred;
--Add comments to the table
Comment on table tableName is ' comment ';
--Add a comment to the column
Comment on column talbename.columnname is ' comment ';
--Sequence: A database object that can be used by multiple users to generate displacement values
1. Automatic supply of unique values
2. Shared objects
3. Mainly used to provide primary key value
4. Loading sequence values into memory can provide access to efficient memory accesses faster than hard drives.
--[] content is omitted [{}] curly braces must be selected
Create sequence Sequencename
[Increment by n]
[Start with n] Start value
[{MaxValue n | nomaxvalue}]Maximum Value
[{MinValue n | nominvalue}]Minimum value
[{cycle | nocycle}]Loop or not loop the default non-circular loop means that when the sequence is the largest one, the loop begins with the start n number of values
[{Cache n | nocache}];Default number of caches is 20
--select Xx_seq. Currval form Dual; Takes the current value of a sequence
Select Xx_seq. Nextval form Dual; Take a sequence next value
--Modify the sequence
Alter sequence Sequencename
Increment by 20
MaxValue 999999
NoCache
Nocycle;
--Delete sequence
DROP sequence sequencename;
--Index
A table-independent schema object that can be stored in a different disk or table space than the table
The index is deleted or corrupted and does not affect the table, it only affects the speed of the query
Once an index is established, the Oracle Management system automatically maintains it, and the Oracle management system determines when the index is used. The user does not have to specify which index to use in the query statement
When you delete a table, all indexes that are based on that table are automatically deleted
Accelerate query speed for Oracle servers with pointers
Reduce disk I/O by quickly locating data
CREATE INDEX IndexName
On EMP (DEPTNO);
1. Creating an index is similar to creating an index table
2. The function of an index is to keep discontinuous data in the index table.
--When to create an index
1. The data values in the column are distributed in a wide range
2. Columns often appear in the WHERE clause or join condition
3. The table is frequently accessed and the data is large, and the data accessed accounts for about 2% to 4% of the total data
--When not to create an index
1. The table is very small
2. Columns are not frequently used as join conditions or appear in the WHERE clause
3. Query for data greater than 2% to 4%
4. Frequently updated tables
Divided into--b tree index
Bitmap index
--Create an index
Automatically created:
The system automatically creates a unique index on the corresponding column after the PRIMARY KEY or UNIQUE constraint is defined
Create manually:
Users can create non-unique indexes on other columns to speed up the query
--Delete Index
Drop INDEX IndexName
--Query Index
You can use the data dictionary view user_indexes and user_ind_columns to view the information for the index
SELECT Ic.index_name, Ic.column_name,
Ic.column_position col_pos,ix.uniqueness
From User_indexes IX, User_ind_columns IC
WHERE Ic.index_name = Ix.index_name
and
Ic.table_name = ' EMPLOYEES ';
--Synonyms are aliases
--For increased security
--Assigning permissions Grant create synonym to user;
--Create synonyms Create synonym hremp for emp.employees;
--Delete synonyms drop synonym Synonymname;
Oracle Foundation two
