Problems and experiences encountered in the past week (oracle)

Source: Internet
Author: User
Tags what sql

A long time ago, when I was still at X Tang Telecom, I was asked by the leaders to optimize an SQL statement. At that time, I did not know what SQL optimization was at all. However, after Baidu and google found that all the materials had such a article: Write the conditional clause with a large selectivity at the end. This is because oracle is executed from the bottom to the top during execution. I was confident in this sentence. However, after I found more information and gained a deeper understanding of SQL optimization, I found that the sentence was RBO, And now oracle adopts CBO. Is the SQL statement executed from the bottom up? This question was subsequently resolved in an error.
There is such a table named test, which has two fields ser_id, area_id. But I can't remember it. I wrote the following SQL:

Select * from test
Where area_id = 290
And name = 'lil'
And class_id = '20140901 ';

Execute one:

The system reports an error, but the system does not say "name" is invalid, but it detects that the class_id is invalid. This means that oracle does execute statements from the bottom up during statement parsing. Is it the fastest way to write the conditions with high selectivity to the bottom? I think it depends on the execution plan.
In this test table, there are now 12582912 data records, of which 290 are data records with area_id = 4291456, and 100001 are data records with ser_id = 2097152. Obviously, area_id = 290 is more selective. Well, now SQL statement 1 is as follows:

Select * from test
Where ser_id = 100001
And area_id = 290;

Its execution plan is as follows:

Reverse the condition to view the execution plan:

Surprised to find that the two execution plans are the same. But it is a little different, that is, physical reading. We can see that 27 physical reads were performed for the first SQL statement, but no physical reads were performed for the second SQL statement. I think everyone knows the cause of physical reading. Here, physical reading is missing because the required data is the same, so the second time I read the required data directly from the cache. However, no matter what the physical reads are, the two execution plans are the same, which proves that oracle, at least 11 GB of local installation, the execution plan of a condition clause is not changed because of its selectivity.

The following is the second part. This week I learned a basic SQL statement: having.
This statement is very easy to use. It is generally used to count duplicate data. For example, I want to know how many times the data record ser_id = 100001 has been repeated in the test table above. I only need to write the SQL statement like this:
Select ser_id, count (*)
WHERE ser_id = 100001
Group by ser_id
Having count (*)> 1;

The result is displayed.

By wingsless

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.