Specifying records in Oracle and using external association query skillfully

1 The value of the 6th to 10th record in the table
1.1 The first method, using the minus statement
Suppose the DDL statement is as follows: CREATE TABLE T (ID VARCHAR2 (4) PRIMARY KEY, VALUE INT)
Then the first method is to take out the first 5, and then remove the first 10, and then use the set operation method to the first 10 minus the top 5 on the OK, the SQL statement is as follows
SELECT * from T WHERE rownum <= 10
Minus
SELECT * from T WHERE rownum <= 5;
1.2 Another method, using subqueries
This method of subqueries is relatively complex, but performance is better than subtracting the collection just now. This method first obtains the first 10 data in the subquery, by the way also obtains the first 10 data rownum, then once again inquires the time to obtain the rownum which the query is more than 5 of those data. 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 above statement, you get 6 to 10th data.
2 using an outer join instead of the not in statement
The in statement also has a very poor efficiency of the not in statement, because the database is encountered in these two statements is to make a pair of data, if in or not on both sides of the amount of data in the tens of thousands of times, the number of times to do it is hundreds of millions of times, It is likely that a simple SQL statement will execute for more than half an hour. This efficiency is definitely unacceptable to customers. Then we can consider the substitution of two methods, the first is to adopt exist statements and not exist statements, this should be more familiar. The other is a clever use of external related statements, this method may not be very familiar, I would like to say a little bit. Suppose that the table-built DDL statement for the datasheet is
CREATE TABLE T1 (ID VARCHAR2 (4) PRIMARY KEY, VALUE INT)
The table DDL statement for in or not is
CREATE TABLE T2 (VALUE INT)
The Oracle-Chinese Association uses a (+) symbol to represent an outer association, meaning that the part that identifies the (+) symbol is NULL when the corresponding value is not found. Here is the SQL statement that replaces the in statement
SELECT t1.id, T1. VALUE
From T1, T2
WHERE T1. VALUE = T2. VALUE (+)
and T2. VALUE is not NULL;
and a similar. The SQL statement that replaces the not in statement is
SELECT t1.id, T1. VALUE
From T1, T2
WHERE T1. VALUE = T2. VALUE (+)
and T2. VALUE is NULL;
You can experiment, in the amount of data, the use of external association than in or not in the execution efficiency is much higher.
http://tb.blog.csdn.net/TrackBack.aspx?PostId=1473286