1. Obtain the values of 6th to 10th records in the table.
1.1 Method 1: Use the minus statementAssume that the DDL statement is as follows: Create Table T (ID varchar2 (4) primary key, value INT). The first method is to retrieve the first five and then the first 10, then, the first 10 minus the first 5 are OK using the set operation method. The SQL statement is as follows: Select * from t where rownum <= 10 minusselect * from t where rownum <= 5;
1.2 another method: subqueryThis 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; through the preceding statement, we get 6 to 10th data records.
2. Use external connections to replace the not in statementThe 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 TABLE creation of in or not in is "create table T2 (value int)". Oracle's Chinese-foreign association uses the (+) symbol to indicate external association, that is, it identifies (+) the part of the symbol is NULL when the corresponding value cannot be found. The following IS the SQL statement SELECT T1.ID, T1.VALUE FROM T1, T2 WHERE T1.VALUE = T2.VALUE (+) AND T2.VALUE IS NOT NULL when the in statement IS replaced. 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 it, when there is a large amount of data, using external associations is much more efficient than using in or not in.
