Understanding of the company's organization of Oracle Training

Oracle Execution mechanism

1. The client sends a SQL to the Oracle server, and Oracle will see if the execution plan for this SQL exists is cached if it exists and runs directly if there is no second step.

2. If there is no cache there will be a syntax check (such as our SQL if the error is wrong is the syntax to check this step). Then do the following (presumably if your syntax is fine.) Oracle optimizes your SQL and generates execution plans)

3. The execution plan generated above is saved in the cache (in order to reduce the second execution of the same SQL time to perform hard parsing, time consuming).

4.oracle eventually executes the query through SQL. Will be queried in the Oracle cache if it is not read in the Oracle hard drive (we know that Oracle data is present on the hard disk, but a large number of IO operations per access to the hard disk are definitely undesirable, so it is read in the cache in the query cache and then read from the hard disk in the cache).

The SQL statement casing specifications that need to be noted above are consistent. Otherwise, for example, the first SQL SELECT * from user performed a hard parse and saved to cache the second SQL SELECT * from user will not be found in the cache to re-hard parse

PS: I've seen SQL Server's execution process before, but it's probably the same thing.

Avoid full table scan prompt query efficiency

1. Queries for null values are scanned in full table. So when we design the data, we define a default value for fields that may appear null values, such as "1" or "a" when you want to query all students who have a blank name: SELECT * FROM Table T.student.name = '-1 '

2. Avoid implicit conversions For example, when you design a database, the type of UserId setting is number when you enter a query on the client, select * from user u where u.userid= ' 1 ' When the database and the client pass the type inconsistency causes the index to fail

3. Avoid query conditions use function SELECT * from user u where U.createtime=to_date (2017-01-02, ' yyyy-mm-dd ') This also causes the index to fail the full table scan

4. Replace with exists in because in also causes index invalidation such as querying a class for a first-year student select * from student where S.calssid in (select ClassID from class where CALs Sname= ' first grade ')

Replace with SELECT * from student s where exists (select ClassID from class where classname= ' first grade ' and S.CLASSSID=S.CLASSID)

Of course, the example is a bit extreme

5. Replace or with UNION ALL

For example, you need to check all student information for age 13 or 15 or 17 years old.

SELECT * from student s where S.age in (13,15,17),

Replaced by

SELECT * from student s where s.age=13

UNION ALL

SELECT * from student s where s.age=15

Unoin All

SELECT * from student s where s.age=17

6. Fuzzy query '% condition% ' is all scanned if business support can be changed to ' condition% ' is walk index

7.join table cannot be more than 3 (the associated fields between table and table are indexed) if business support can query the data to the program and then fetch the associated data

List<student> students=dbhelper.query (SELECT * from Student where rownum<=10);

Student. ForEach (function (c) {

C.studentname=dbhelper.query (SELECT * from class where classid= ' "+c.classid+ '). ClassName

})

PS: This may feel like an increase in the load on the database. Because fetching the associated data will make 10 requests to the database (in fact, these 10 requests are very fast first is the database connection pool The second is the above SQL execution mechanism is directly run will not perform hard parsing because these 10 SQL is consistent)

8. Avoid using SELECT * * will cause Oracle to parse which columns in this table are not directly taken. * will result in data transfer the best need what to take what

Summary: The above optimization operation may not feel anything when the amount of data is small, but tens of millions of data often do not circumvent the above SQL will query for a long time and evade the SQL only need fraction seconds

Understanding of Oracle Training in the company organization