вторник, 25 января 2011 г.

Dev: nls_sort: possible values and why index not used for order by

select * from v$nls_valid_values where parameter='SORT';
--
-- Territory
--
select to_char(sysdate, 'd') from dual;

T
-
3

alter session set nls_territory=RUSSIA;
Session altered.
select to_char(sysdate, 'd') from dual;

T
-
2
--
-- Be careful, for varchar columns optimizer will not use index for sorting
--
13:45:19 SQL> select value from nls_session_parameters where parameter = 'NLS_SORT';

VALUE
----------------------------------------
RUSSIAN
13:46:54 SQL> select * from id order by id;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1459189690

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10145 | 14M| | 3196 (1)| 00:00:39 |
| 1 | SORT ORDER BY | | 10145 | 14M| 31M| 3196 (1)| 00:00:39 |
| 2 | TABLE ACCESS FULL| ID | 10145 | 14M| | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
13:47:24 SQL> alter session set nls_sort=binary;

Session altered.

Elapsed: 00:00:00.01
13:47:31 SQL> select * from id order by id;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4052251165

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10145 | 14M| 19 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | SYS_C006460 | 10145 | 14M| 19 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--
-- You can create linguistic index but in this case table will be accessed too
--
14:49:47 SQL> alter session set nls_sort=russian;

Session altered.

Elapsed: 00:00:00.01
14:50:49 SQL> select value from nls_session_parameters where parameter = 'NLS_SORT';

VALUE
----------------------------------------
RUSSIAN

1 row selected.

Elapsed: 00:00:00.01
14:51:16 SQL> set autotrace traceonly exp
14:51:31 SQL> select * from id order by id;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3088927961

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10145 | 14M| 954 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| ID | 10145 | 14M| 954 (0)| 00:00:12 |
| 2 | INDEX FULL SCAN | ID_IDX | 10145 | | 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Комментариев нет:

Отправить комментарий

Update BLOB

set define off DECLARE    vb1 CLOB := 'long text';    vb2 CLOB :=                 'long text';    vb3 CLOB :=              ...