Oracle Certification skillfully taking outside designated records associated with the alternative in / not in query

Posted on 2009-06-30 filed under: Oracle (RSS) 20 views

Oracle Certification skillfully taking outside designated records associated with the alternative in / not in query

Table 1 to obtain the first 6 to Article 10 of the value of records

1.1 The first method, the use of minus statement

Ddl statement assumptions are as follows:

CREATE TABLE T (ID VARCHAR2 (4) PRIMARY KEY, VALUE INT)

The first method is then removed before the 5, and then removed before 10, and then using the method of computing a collection of the former 10 minus 5 on the OK before the, SQL statement is as follows

The following is a quoted fragment:
SELECT * FROM T WHERE ROWNUM <= 10
MINUS
SELECT * FROM T WHERE ROWNUM <= 5;

1.2 In addition a method of using subquery

This kind of subquery approach is relatively complex, but a collection of performance than just subtract better. This method first subquery to be 10 before the data has also been made way before the 10 data rownum, and then once again just made inquiries when rownum query the data of more than 5. SQL statement is as follows

The following is a quoted fragment:
SELECT ID, VALUE FROM
(SELECT ID, VALUE, ROWNUM R FROM T WHERE R <= 10)
WHERE
R> 5;

Through the above statement, it has been 6 to Article 10 of the data.

2 the use of an alternative not in outer join statement

in statements are not in the statement of the efficiency is very poor, because the database in the face of these two statements, when data is to a more than a right, if in or not in the amount of data on both sides in the last million of the time, to compare the number of the million is very likely a simple sql statement to be performed for more than half an hour. This efficiency can not be sure that customers are acceptable. Then we can consider two alternative methods, the first statement is based on the exist and not exist statement, which we should be more familiar with. Another is outside the associated statement Using this method may not be familiar with everyone, I came to talk about a little. Hypothetical data table DDL statements to build tables for the

CREATE TABLE T1 (ID VARCHAR2 (4) PRIMARY KEY, VALUE INT)

And in the table or not in the building table for DDL statements

CREATE TABLE T2 (VALUE INT)

Oracle is used by Chinese and foreign association (+) symbol outside the association, that is to say a logo (+) symbols of value can not find the time corresponding to NULL. The following is a statement of when to replace in the SQL statement

The following is a quoted fragment:
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE (+)
AND T2.VALUE IS NOT NULL;

And similar. Alternative not in the SQL statement of the statement when compared with

The following is a quoted fragment:
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE (+)
AND T2.VALUE IS NULL;

We can test, in the amount of data and more time than the use of external linkages in or not in the implementation of the efficiency of many, many higher.