SQL Optimization (Oracle)-First part knowledge preparation

First part knowledge preparation
1. SQL Execution Process
2. SQL sharing
3. Binding variables
4. Data table access method
5. SQL Execution Order
6. Index usage
　　　　　　　　　　　　　　　　　　　　　　

1. SQL Execution Process
1) Execution Process
When an Oracle instance receives a SQL, the following procedure is performed:

1) Create a cursor
2) Parse the statement parsing statement
3) Describe results of a query describes the result set
4) Define output of a query that defines the data for the query
5) Bind any variables bound variable
6) Parallelize The statement parallel execution statement
7) Run the statement statement
8) Fetch rows of a query fetch results
9) Close the cursor off cursors

2.SQL sharing
1. To not parse the same SQL statements repeatedly, Oracle will execute SQL statements in the shared buffer pool of memory, which can be shared by all database users
2. When executing an SQL statement, if it is identical to the one previously executed (note that synonyms and tables are different objects), Oracle can obtain statements that have already been parsed;

3.bind Variables Binding variable
1) Resolve the recompilation problem
EG1:
Insert into TAB1 (col1,col2) values (VAL1,VAL2); --Ordinary way
Insert into TAB1 (col1,col2) VALUES (: V1,:V2); --Bind variables, just compile once

EG2: Using PreparedStatement
PreparedStatement PS = con.preparestatement ("INSERT into TAB1 (col1, col2) VALUES (?,?)");

2) Shared Cursors
Benefits: Reduced parsing, increased memory utilization, dynamic memory tuning
If you enter the following two sql:
SELECT * from tab1 where id =: c;
SELECT * from tab1 where id =:d;
These two lines of SQL are converted to:
SELECT * from tab1 where id =: b;

4. Accessing the data table method
1) Full table Scan--sequential access to each record in the table
Oracle optimizes full-table scanning with one read-in multiple data blocks
2) The ROWID Access table--rowid contains the physical location information of the records in the table, which can improve the efficiency of accessing the table based on ROWID access mode.
Oracle implements the link between data and the location of the ROWID data by index, and often the index provides a quick way to access the ROWID

5. Select SQL Execution Order
1) SELECT clause

(8) SELECT (9) DISTINCT (one) <top num> <select list>
(1) From <left_table>
(3) <join_type> join <right_table>
(2) On <join_condition>
(4) WHERE <where_condition>
(5) GROUP by <group_by_list>
(6) With <cube | Rollup>
(7) Having (10) ORDER by <order_by_list>

2) Execution Sequence description
1) from [the table before left Table]--from Descartes set--Virtual table VT1
2) on <join condition>--filter--VT2
3) [Join type] join [Right table]--connection--vt3
4) Where--where Screening--VT4
5) Group by--groups VT4 rows by the columns in the GROUP BY clause--VT5
6) cube| The rollup--group, Eg:rollup (A, b), begins with group by for (A, B), and then group by for (a), and finally to the full table group by
CUBE (A, B), first to group by, then (a), (b) Group BY, and the last full table group by;
--vt6

7) having--having Screening--vt7
8) Select--vt8
9) distinct--to remove duplicate rows--vt9
Order by--sorts the list in VT9 by the column in the ORDER BY clause, generating a cursor--VC10
One) top--Select a certain number or proportion of rows--vt11 from the beginning of the VC10 to return the results

3) Precautions
1. Only the alias of a column in the select list can be used in the ORDER BY clause
If you want to use it elsewhere, use the following method:
SELECT * FROM (select NAME, SALARY as S from EMP) VT WHERE vt.s<5000;
2. Queries that use the ORDER BY clause cannot be used as table expressions (views, inline table-valued functions, subqueries, derived tables, and common expressions), and the following statements produce errors
CREATE TABLE TAB1 as SELECT * FROM student order by score;
SELECT * FROM (SELECT * from student order by score);

SQL Optimization (Oracle)-First part knowledge preparation