"Knowledge" is very important for Oracle SQL adjustment-to find duplicate rows

After completing the training, Master Zhou immediately ran to another brother organization to solve the problem. I really admire Master Zhou.

Brother organizations have encountered two difficult problems. A primary key repetition error is reported during data imp. This problem is very strange. I still don't know how to solve it today.

Another problem is the preceding SQL statement:

 
Select * From view_a awhere. field_1,. field_2,. field_3,. field_4in (select field_1, field_2, field_3, field_4 from view_a group by field_1, field_2, field_3, field_4 having count (*)> 1)
 

The original intention of this statement is to find the repeated rows according to field_1, field_2, field_3, and field_4. view_a has 20354 rows of data, and the row width is not large, and the current data is not repeated rows, that is to say, the subquery in the in Clause returns an empty set.

Let's talk about the problem in the background.

This statement runs very fast on the old database (Oracle 9i, AIX), but in the new database (Oracle 10g, as 5.2, data is from exp/IMP) after running for a long time, all the results are returned.

The following two responses can be made to this problem:

First, the cost of this statement is very high. It is very inefficient to view the execution plan with several steps;

Second, the response is that the execution plan of the statement is different in 10 GB and 9i;

In terms of the first response, the next step should be the SQL optimization stage. However, there is a doubt that it will be recognized that there will be performance problems:

Select field_1, field_2, field_3, field_4 from view_a group by field_1, field_2, field_3, field_4 having count (*)> 1
 

An empty set is returned very quickly. Why is the in operator A problem? Using in is very concise, clear, and difficult to find a more concise operator.

Next, let's go to the second idea. It is difficult to say that the execution plan of 10 Gb is different from that of 9i. This is possible. However, due to the site relationship, you cannot view the execution plan on 9i's old database at half past one.

If I follow this idea, Will another result be returned if I change the execution plan? (Faster and slower)

How to change the execution plan? solution 1: Oracle has a "prompt" function, which can forcibly specify some execution plans. This function is also available in SQL Server. solution 2: write SQL statements in another way.

 

A long time ago, I saw a post in a forum asking why MySQL does not support subqueries (not in MySQL 4 ), the reply probably means that MySQL is still very young and will be better in the future. The reply from a cool user is: "Any subquery can be replaced by join ". According to the thinking of this cool man, I changed the statement to this:

 
Select. * From view_a ainner join (select field_1, field_2, field_3, field_4 from table_a group by field_1, field_2, field_3, field_4 having count (*)> 1) Bon. field_1 = B. field_1and. field_2 = B. field_2and. field_3 = B. field_3and. field_4 = B. field_4
 

The execution plan has indeed changed. Run it and the results will soon come out.

 

Almost anyone who has ever written SQL can understand join and in, but there are also a few "insights" from the above-mentioned cool-man.

[To] http://www.cnblogs.com/killkill/archive/2009/04/13/1434825.html