Differences between Oracle in and not in actual application

The following article mainly describes the differences between Oracle in and not in actual application. We all know that in many related software systems, the actual performance of the system is largely determined by the performance of the database. I have also done many tests on performance before.

Especially for Oracle, I think we should record some of them to share them with everyone.

This happened when our system was transplanted from sqlserver to Oracle. The user waited for a query operation and the time was unbearable. Our query processing and the original method were as follows, is there something different between sqlserver and Oracle? Let's take a look at what's wrong with Oracle or what's wrong with Oracle?

The business problem can be roughly described as follows: A parent table and a sub-table, the query result is to find the records in the sub-table that do not use the parent table id. In this case, it is estimated that many systems will be involved. Let's take an example:

Table 1: parent table parent

Table 2: child table childen

The parent table stores the parent table, the child table stores the child table, and then uses the pid to associate with the parent table. The result of the query is to find the father who does not have the child.

Let's take a look at the syntax of the query statement:

 
 

  
  
select * from parent where id not in (select pid　from childen) 
 
 

When the sub-table contains 0.5 million records, the query time exceeds 10 seconds, which is far more than one second on the original SQL server. I came up with a solution:

 
 

  
  
select * from parent where id in  
  
  
( select id from parent minus select pid　from childen )  
 
 

Under normal understanding, this statement should be more time-consuming, but the fact is totally unexpected. This statement is not only fast when the sub-table has a large number of records, in the case of a small amount of data in a sub-table, the speed is also very good, basically completed within 1 second.

This result can clearly prove the huge difference between Oracle in and not in the internal processing of subqueries, I also hope someone will explain the problem.