Oracle uses specified records and uses external join queries

How to obtain the values of 6th to 10th records in a table.

The first method is to use the minus statement:

Assume that the DDL statement is as follows:

Create Table T (ID varchar2 (4) primary key, value INT)

The first method is to extract the first five, then the first 10, and then the first 10 minus the first five by using the set operation method. The SQL statement is as follows:

Select * from t where rownum <= 10
Minus
Select * from t where rownum <= 5;
Another method is to use subqueries:

This method of subquery is relatively complicated, but the performance is better than the set subtraction. This method first obtains the first 10 data records in the subquery, then obtains the rownum of the first 10 data records, and then obtains the data with the rownum greater than 5 in the query. The SQL statement is as follows:

Select ID, value from
(Select ID, value, rownum R from t where r <= 10)
Where

R> 5;

The preceding statement generates 6 to 10th data records.

Replace the not in statement with external connections

The efficiency of the in statement and the not in statement is very poor, because the database must compare the data one by one when encountering these two statements, if there are tens of thousands of data records on both sides of the in or not in, the number of comparisons is hundreds of millions. It is very likely that a simple SQL statement will be executed for more than half an hour. This efficiency is certainly unacceptable to customers. We can consider two alternative methods. The first one is to use the exist statement and not exist statement, which should be familiar to everyone. The other method is to use external join statements. This method may not be very familiar to everyone. Let me talk about it a bit. Assume that the DDL statement for table creation is

Create Table T1 (ID varchar2 (4) primary key, value INT)
The DDL statement for creating an in or not in table is:
Create Table T2 (value INT)

Oracle foreign association uses the (+) symbol to indicate external association, that is, the part that identifies the (+) symbol is null when the corresponding value cannot be found. The following is the SQL statement used to replace the in statement:

Select t1.id, t1.value
From T1, T2
Where t1.value = t2.value (+)
And t2.value is not null;
And similar. When the not in statement is replaced, the SQL statement is:

Select t1.id, t1.value
From T1, T2
Where t1.value = t2.value (+)
And t2.value is null;

You can test that when there is a large amount of data, using external associations is much more efficient than using in or not in.