How to write good SQL statements and how to write efficient SQL statements

Optimize SQL queries:How to write high-performance SQL statements.

1. First of all, we must understand what is the implementation plan?

If an SQL statement is used to query 1 record from a table with 100,000 records, the query optimizer will choose the "index lookup" mode, if the table is archived, there are only 5000 records left, then the query optimizer will change the scheme and adopt the "full table scan" method.

It can be seen that the execution plan is not fixed, it is "personalized". There are two important things that are important to produce a correct "execution plan": optimizing SQL queries: how to write high-performance SQL statements.

1. First of all, we must understand what is the implementation plan?

If an SQL statement is used to query 1 record from a table with 100,000 records, the query optimizer will choose the "index lookup" mode, if the table is archived, there are only 5000 records left, then the query optimizer will change the scheme and adopt the "full table scan" method.

It can be seen that the execution plan is not fixed, it is "personalized". There are two important points to produce a correct "execution plan":

Optimize SQL queries:How to write high-performance SQL statements.

1. First of all, we must understand what is the implementation plan?

If an SQL statement is used to query 1 record from a table with 100,000 records, the query optimizer will choose the "index lookup" mode, if the table is archived, there are only 5000 records left, then the query optimizer will change the scheme and adopt the "full table scan" method.

It can be seen that the execution plan is not fixed, it is "personalized". There are two important points to produce a correct "execution plan":

1) Does the SQL statement clearly tell the query optimizer what it wants to do?

2) Are the database statistics from the query optimizer up-to-date and correct?

2. Unify the writing of SQL statements.

For the following two SQL statements, the programmer thinks they are the same, and the database query optimizer thinks they are different.

select * from dual

select * from dual

In fact, the case is different, and the query analyzer thinks that it is two different SQL statements, which must be parsed twice. Generate 2 execution plans.

So as a programmer, you should make sure that the same query statement is consistent everywhere, not one more space!

3. Don't write SQL statements too complicated.

I often see that a single SQL statement captured from the database prints out as long as 2 sheets of A4 paper. In general, such complex statements are usually problematic. I took the two-page SQL statement to the original author, but he said that it was too long and he couldn't understand it for a while.

As you can imagine, even the original author may read the SQL statements confused, and the database will also be confused.

Generally, it is relatively common to take the result of a select statement as a subset and then query from that subset, but according to experience, beyond three layers of nesting, the query optimizer is easy to give an incorrect execution plan. Because it was dizzy. Something like this kind of artificial intelligence is worse than human resolution after all, and if people are dizzy, I can guarantee that the database will also be dizzy.

In addition, execution plans can be reused, and the simpler the SQL statement, the more likely it is to be reused. Complex SQL statements must be parsed as soon as one character changes, and then this large pile of garbage is stuffed into memory. One can imagine how inefficient the database can be.

4. Use the "Temporary Table" to temporarily store intermediate results.

An important way to simplify SQL statements is to use temporary tables to temporarily store intermediate results, however, the benefits of temporary tables are far more than these, the temporary results are temporarily stored in the temporary table, and the subsequent queries are in tempdb, which can avoid multiple scans of the main table in the program, and also greatly reduces the "shared lock" blocking "update lock" in the program execution, reduces the blocking, and improves the concurrency performance.

Concise and efficient SQL statements are very useful for a large ** or large database, and the redundancy of SQL causes the occupation of systems and resources and the increase of time.

1.What kind of SQL is considered efficient SQL? Why not go indexing?

How to make SQL go indexing, that is, change the execution plan of SQL 3What are the different types of indexes? 4. When to use indexes, when to scan the table statistics of the Oracle optimizer with the whole table, and evaluate the best connection order of the table, the joining method of the table, and the execution path; Finally, an execution plan is generated, and Oracle executes SQL 1 according to this plan

What kind of SQL is an efficient SQL? Answer: The most essential answer is the shortest implementation time, how to make the shortest, is to use the least resources to do things, not to do useless work; Even if SQL has the least io, how can it be the least?

That is, try to use indexes as much as possible, and do not scan the whole table; When linking multiple tables, why don't you follow index aType does not match b. The conditional column contains a function but does not create a corresponding function index c.

In the mode where the leading column in the composite index is not used as the query condition, the number of rows selected is large, and the optimizer selects the mode of full table scanning, and the table has not been analyzed for a long time, and the optimizer selects full table scan3Index type and creation methodCreate index indexName on tablename(columnname); b.create index indexname on tablename(columnname) reverse; c.

create index indexname on tablename(columnname desc); d.create bitmap index indexname on tablename(columnname); e。create index indexname on tablename(functionname(columnname)); 4. When to use indexes and when to use full table scanning?

A: When you want to use indexes, you first need to figure out how many rows there are in some basic information tables. How many rows does the query return?

What columns of the table have indexes? What kind of indexes are they? What kind of index should I choose when I have multiple conditional columns?

a.When the number of queried records is less than 40% in an ordered table, it is best to use indexes. Otherwise, scan b. with the full tableWhen the number of queried records is less than 7% of the unordered tables, it is best to use indexes. Otherwise, scan C. with the full table

When there are a large number of key-locks in the table (the DML operations on this table are frequent).

This is not a simple SQL but a trigger (inside the trigger is a PLSQL block, with judgment).

The trigger condition is after insert b, that is, after the b table is entered.

For convenience, it is recommended to add a total cost field to the newly created table to make it easier to calculate.

1) You need to query whether table A has been entered, (I don't know the order of your input, if you can make sure that A is earlier than B, then you don't need to query whether table A has been entered.) This is relatively simple, as long as the tracking number exists, and if it exists, then continue to execute.

2) Find the corresponding student number in this table, if not, then insert.

3) Judge the amount, which is what you said about 1000, 2000, 10000, if yes, then update. The way of judging is also simple, if the original student number does not exist, then judge directly, and if it exists, then use the original to add this time. The judging conditions are as follows:

The original cost < 1000 and the original cost + this cost 1000, and the other two judgments are similar to this. If the judgment is successful, then update, otherwise do not change.

Personally, I can think of this method, but the specific needs some experiments, I haven't been able to write it directly, and it must be modified, there is no environment here, you can only change it yourself.