Oracle development misunderstanding

Environment:

sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -aLinux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

① Single Column and composite column not in subquery

NULL in not in (...). If null exists, null is returned. Because, in is an or relation, plus not, then any value and null, logic and, the result is empty.

Test:

-- Q1; null Analysis for a single column not in subquery

HR @ orcl> drop table test1;
HR @ orcl> drop table Test2;
HR @ orcl> Create Table test1 (ID number );

Table created.

HR @ orcl> Create Table Test2 (ID number );

Table created.

HR @ orcl> insert into test1 values (1 );

1 row created.

HR @ orcl> insert into test1 values (2 );

1 row created.

HR @ orcl> insert into Test2 values (null );

1 row created.

HR @ orcl> insert into Test2 values (1 );

1 row created.

HR @ orcl> commit;

Commit complete.

-- Requirement: select records in test1 but not in Test2.
-- Single Column, common errors as follows, no results:

HR @ orcl> select ID from test1 where id not in (select ID from Test2 );

No rows selected

-- The correct writing method and common ones are not exists

HR @ orcl> select ID from test1 where not exists (select 1 from Test2 where test1.id = test2.id );

ID
----------
2

-- Q2: the composite column not in subquery has a null analysis.

HR @ orcl> Create Table T1 (a number, B number );

Table created.

HR @ orcl> Create Table T2 (a number, B number );

Table created.

HR @ orcl> insert into T1 values (1, 1 );

1 row created.

HR @ orcl> insert into T1 values (1, 2 );

1 row created.

HR @ orcl> insert into T2 values (1, 1 );

1 row created.

HR @ orcl> insert into T2 values (null, 2 );

1 row created.

HR @ orcl> commit;

Commit complete.

-- Requirement: select records at T1 but not at T2.
-- Common errors, the same as Q1, with no results

HR @ orcl> select * from T1 where (a, B) Not in (select * From T2 );

No rows selected


-- Correct solution, common is not exists
HR @ orcl> select * from T1 where not exists (select 1 from T2 where t1.a = t2.a and t1. B = t2. B );

A B
--------------------
1 2
-- Analysis
Because it is a composite column, it is equivalent to the combination condition of the column is or. According to null comparison and logic calculation rules, if one of the or conditions is true, true is returned. If all values are false, the result is false, others are unknown,
For example, (1, 2) Not in (null, 2) is equivalent to 1 <> null or 2 <> 2. Obviously, the returned result is unknown, so it is impossible to be true without returning the result;
However, (1, 2) Not in (null, 3) is equivalent to 1 <> null or 2 <> 3, because 2 <> 3 is already true, so the condition is true, and the returned result is returned;
Perform another simple test:
HR @ orcl> select * from dual;

D
-
X
HR @ orcl> select * from dual where (1, 1) Not in (null, 2 ));

D
-
X
HR @ orcl> select * from dual where (1, 1) Not in (null, 1 ));

No rows selected

 

 

② Eliminate implicit Conversions

 

All auto trace analyses contain the keyword predicate information. Predicate information has two types of values: Filter and access. Generally, index read and hash join are represented by access.
The most important thing to focus on predicate information is to check whether data type conversion has occurred.
Data type conversion not only produces overhead, but also affects the normal use of indexes.
Therefore, data type conversion should not be seen at any time.
-- Test
HR @ orcl> drop table t;

Table dropped.

HR @ orcl> Create Table T (col1 varchar2 (20), col2 number );

Table created.

HR @ orcl> insert into T select rownum, rownum + 1 from dual connect by level <= 10000;

10000 rows created.

HR @ orcl> commit;

Commit complete.

HR @ orcl> Create index idx_t on T (col1 );

Index created.

HR @ orcl> set autot traceonly
HR @ orcl> select * from t where col1 = 2 and col2 = 3;


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 25 | 6 (0) | 00:00:01 |
| * 1 | table access full | T | 1 | 25 | 6 (0) | 00:00:01 |
--------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-filter ("col2" = 3 and to_number ("col1") = 2)

Note
-----
-Dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive cballs
0 dB block gets
48 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

-- Through statistics, we can see that 48 logical reads are generated when one row is obtained, which does not meet the criteria for obtaining less than 5 logical reads for a single row record.
-- In the predicate information, to_number ("col1") = 2 type conversion exists.
-- Check that the field type of col1 in table t is varchar2.
HR @ orcl> select * from t where col1 = '2' and col2 = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

Bytes -------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes -------------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 25 | 2 (0) | 00:00:01 |
| * 1 | table access by index rowid | T | 1 | 25 | 2 (0) | 00:00:01 |
| * 2 | index range scan | idx_t | 1 | 1 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-filter ("col2" = 3)
2-access ("col1" = '2 ')

Note
-----
-Dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

-- The Execution Plan is changed from full table scan to index read.
-- In predicate information, access ("col1" = '2') indicates the access path using the index method.
-- The ratio of returned rows to logical reads is also <5