Oracle's IN and Not in - Performance Practice
In many software systems, the system's performance is determined by the performance of the database. I have previously have done a variety of tests on performance, especially about Oracle, I think it should also be recorded, and share it for everyone.
Things happen from SQL Server to Oracle, the user can't stand the time in the operation of a query, and our processing of this inquiry is in the original way, what is the SQLServer does not have anywhere? Let's take a look at what is there any problem in Oracle, or what we use?
Business issues can probably describe, a parent table, a sub-table, and query results are the records that do not use the parent table ID in the subthech, which is estimated that many systems are involved. Let us give an example:
Table 1: Parent table Parent
Number field Type Description 1.
ID VARCHAR2 (10) Primary key 2.
Name varchar2 (100) name
Table 2: Table Childen
Number field Type Description 1.
ID VARCHAR2 (10) Primary key 2.
PID varchar2 (10) representative 3 of the primary table.
Name varchar2 (100) name
The parent table stores the father, the sub-table stores the child, and then associates through the PID and the parent table, the result of the query is to find the father who has not yet had children.
Let's take a look at the writing of query statements:
Select * from parent where id not in (SELECT PIM Childen)
This type of standard is a record of 500,000 records in the sub-table, and the query time has exceeded 10 seconds, which is much larger than the original SQL Server server. I thought of a way when I was solved:
SELECT * from Parent Where Id in
(Select ID from parent minus select pid from childen)
Under normal understanding, this statement should be more time, but the fact is completely unexpected, this statement is not only in the case where there is a lot of records, but the speed is very good in the case of small data in the child table, basically Completed within 1 second.
This result can be obvious to demonstrate the huge difference between Oracle to use in and NOT IN, I hope to use the user's attention to the user's attention, and I expect some problems.
Appendix: Test data statement
