Oracle in and not in―― performance practices

oracle| Performance
Oracle in and not in―― performance practices



In many software systems, the performance of the system is determined by the performance of the database. I've done a lot of tests on performance in the past, especially about Oracle, and I think it should be recorded and shared.

Things happen when our system is ported from SQL Server to Oracle, and the time that users wait on a query is unbearable, and we deal with this query in the same way as we did, is there anything different about SQL Server and Oracle? Let's see what's wrong with Oracle, or what's wrong with us?

A business problem can probably be described as a parent table, a child table, and the result of the query is to find records in the child table that do not use the parent table ID, which is estimated to be involved in many systems. Let's give an example:



Table One: Parent table parents

Number

Field

Type

Description

1.

Id

VARCHAR2 (10)

Primary key

2.

Name

VARCHAR2 (100)

Name



Table II: Child table Childen

Number

Field

Type

Description

1.

Id

VARCHAR2 (10)

Primary key

2.

Pid

VARCHAR2 (10)

Representation of the primary table

3.

Name

VARCHAR2 (100)

Name



The parent table stores the father, the child table stores the child, and then the PID is associated with the parent table, and the result of the query is to find the father who has not yet had the child.



Let's look at the wording of the query:

SELECT * from parent where ID isn't in (select PID from Childen)



This standard notation when the child table has 500,000 records, the query time exceeds 10 seconds, much larger than the original SQL Server server for a second. I figured out a way to solve it:

SELECT * from parent where ID in

(select ID from parent minus select PID from Childen)

Normally understood, this statement should be more time-consuming, but the fact is completely unexpected, this statement is not only in the child table with a large number of records in the case of good speed, in the case of small data in the child table is very good, the basic in 1 seconds to complete.

This result can be a clear proof of Oracle's internal processing of subqueries, using the big difference between in and not, hoping to use the user's attention in this way and expecting someone to explain the problem.





Appendix: Statements to test data



--Create parent table
drop table parent;
CREATE TABLE parent (ID varchar (), name varchar, primary key (ID));


--Create Childen table
drop table childen;
CREATE TABLE Childen (ID varchar, PID varchar (), name varchar, primary key (ID));
--Create/recreate primary, unique and foreign KEY constraints
ALTER TABLE Childen
Add constraint fk_123 foreign key (PID)
References parent (ID);

--Add test date for parent
--Created on 2004-11-29 by Guip
Declare
--Local variables
I integer;
Begin
--Test statements here
I: = 0;
Delete from parent;
Loop
I: = i + 1;
Dbms_output.put_line (i);
INSERT into parent (ID, name) values (i, ' name ' | | i);
if (i mod 100=0) then
Commit
End If;
Exit when i > 1000;
End Loop;
Commit
End

--Add test date for Childen
--Created on 2004-11-29 by Guip
Declare
--Local variables
I integer;
J Integer;
Begin
--Test statements here
I: = 0;
Delete from Childen;
Loop
J: = 0;
Loop
I: = i + 1;
J: = j + 1;
INSERT into Childen (ID, PID, name) values (i, J, ' name ' | | j);
if (i mod 100=0) then
Commit
End If;
Exit when j>= 50;
End Loop;

Exit when I >= 10000 * 50;
End Loop;
Commit
End