First_rows optimization Pattern Language sort fuzzy matching problem

The title is longer, but only in this way can the problem be described clearly.

The problem is described in detail as, in First_rows optimization mode, the session sort and comparison method is set to semantic mode, that is, ignoring the case mode, and making a like fuzzy query on the field, which may result in incorrect results.

For a detailed description of the case-insensitive query, refer to: http://yangtingkun.itpub.net/post/468/460324

The following directly look at the phenomenon of the problem:

Sql> CREATE TABLE T1 (ID number PRIMARY KEY, NAME VARCHAR2 (30));

Table has been created.

Sql> CREATE INDEX ind_t1_name on T1 (NAME);

The index has been created.

Sql> INSERT into T1 SELECT rownum, CHR (+ + rownum)
2 from All_objects WHERE rownum <= 26;

26 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> ALTER session SET Nls_comp = linguistic;

The session has changed.

Sql> ALTER session SET nls_sort = Binary_ci;

The session has changed.

Sql> SELECT * from T1 WHERE NAME like ' a% ';

ID NAME
---------- ------------------------------
1 A

sql> SELECT/*+ first_rows * * from T1 WHERE NAME like ' a% ';

No rows selected

As long as you modify any of the key points mentioned above, this error does not occur:

sql> SELECT/*+ all_rows * * from T1 WHERE NAME like ' a% ';

ID NAME
---------- ------------------------------
1 A

sql> SELECT/*+ first_rows * * from T1 WHERE NAME = ' a ';

ID NAME
---------- ------------------------------
1 A

Sql> ALTER session SET nls_sort = BINARY;

The session has changed.

Sql> ALTER session SET Nls_comp = BINARY;

The session has changed.

sql> SELECT/*+ first_rows * * from T1 WHERE NAME like ' a% ';

ID NAME
---------- ------------------------------
1 A

Sql> ALTER session SET Nls_comp = linguistic;

The session has changed.

Sql> ALTER session SET nls_sort = Binary_ci;

The session has changed.

sql> SELECT/*+ first_rows * * from T1 WHERE NAME like ' a% ';

ID NAME
---------- ------------------------------
1 A

sql> SELECT/*+ first_rows * * from T1 WHERE NAME like ' a ';

No rows selected

As you can see from several of the above queries, problems and first_rows,like operations and semantically based ordering are directly related, and here's a look at Oracle's implementation plan in exceptional circumstances:

Sql> SET Autot on EXP
sql> SELECT/*+ first_rows * * from T1 WHERE NAME like ' a ';

No rows selected

Execution plan
----------------------------------------------------------
Plan Hash value:3350237141

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0) | 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0) | 00:00:01 |
|* 2 | HASH JOIN | | |
|* 3 | INDEX RANGE SCAN | Ind_t1_name | 1 | 30 | 3 (34) | 00:00:01 |
| 4 | INDEX FAST Full scan| sys_c006622 | 1 | 30 | 33 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------

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

1-filter ("NAME" like ' a ')
2-access (Rowid=rowid)
3-access ("NAME" like ' a ')

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

Since the index does not contain the result of a semantic query, Oracle must access the table to get the final result, so this execution plan is wrong:

Sql> SELECT * from T1 WHERE NAME = ' a ';

ID NAME
---------- ------------------------------
1 A

Execution plan
----------------------------------------------------------
Plan Hash value:3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0) | 00:00:01 |
|* 1 | TABLE ACCESS full| T1 | 1 | 30 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

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

1-filter (Nlssort ("NAME", ' nls_sort= ' binary_ci ') =hextoraw (' 6100 ')
)

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

Sql> SELECT/*+ INDEX (T1) * * from T1 WHERE NAME = ' a ';

ID NAME
---------- ------------------------------
1 A

Execution plan
----------------------------------------------------------
Plan Hash value:159298173

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 827 (1) | 00:00:10 |
|* 1 | TABLE ACCESS by INDEX rowid| T1 | 1 | 30 | 827 (1) | 00:00:10 |
| 2 | INDEX Full SCAN | sys_c006622 | 26 | | 26 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------

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

1-filter (Nlssort ("NAME", ' nls_sort= ' binary_ci ') =hextoraw (' 6100 '))

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

The above two execution plans have explained the crux of the problem, Oracle's ordering of semantics is not indexed, you must access the table or the corresponding function index, and you can refer to the links given at the beginning of the article.

With the First_rows optimization mode, when the operation is like, the Oracle optimizer chooses the wrong execution plan for optimization, and uses an indexed range scan instead of a table, which causes an error:

Sql> SELECT/*+ index_join (T1 ind_t1_name sys_c006622) *
2 from T1
3 WHERE NAME like ' a ';

No rows selected

Execution plan
----------------------------------------------------------
Plan Hash value:3350237141

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0) | 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0) | 00:00:01 |
|* 2 | HASH JOIN | | |
|* 3 | INDEX RANGE SCAN | Ind_t1_name | 1 | 30 | 3 (34) | 00:00:01 |
| 4 | INDEX FAST Full scan| sys_c006622 | 1 | 30 | 33 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------

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

1-filter ("NAME" like ' a ')
2-access (Rowid=rowid)
3-access ("NAME" like ' a ')

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

There is no use of first_rows now, and the use of hint has achieved the same effect. The solution to this problem is through hint to avoid the occurrence of index range scans.

Sql> ALTER session SET Optimizer_mode = first_rows;

The session has changed.

Sql> SELECT * from T1 WHERE NAME like ' a ';

No rows selected

Execution plan
----------------------------------------------------------
Plan Hash value:3350237141

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0) | 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0) | 00:00:01 |
|* 2 | HASH JOIN | | |
|* 3 | INDEX RANGE SCAN | Ind_t1_name | 1 | 30 | 3 (34) | 00:00:01 |
| 4 | INDEX FAST Full scan| sys_c006622 | 1 | 30 | 33 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------

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

1-filter ("NAME" like ' a ')
2-access (Rowid=rowid)
3-access ("NAME" like ' a ')

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

Sql> SELECT/*+ Full (T1)/* from T1 WHERE NAME like ' a ';

ID NAME
---------- ------------------------------
1 A

Execution plan
----------------------------------------------------------
Plan Hash value:3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0) | 00:00:01 |
|* 1 | TABLE ACCESS full| T1 | 1 | 30 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

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

1-filter ("NAME" like ' a ')

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

Sql> SELECT/*+ no_index (T1) * * from T1 WHERE NAME like ' a ';

ID NAME
---------- ------------------------------
1 A

Execution plan
----------------------------------------------------------
Plan Hash value:3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0) | 00:00:01 |
|* 1 | TABLE ACCESS full| T1 | 1 | 30 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

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

1-filter ("NAME" like ' a ')

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

The query metalink,oracle the bug explicitly in Doc id:note:5252496.8, which is fixed in Oracle10.2.0.4 and 11.1.0.6.