在oracle10g中可使得排序不區分大小寫

jss001發表於2009-02-17

SQL> select * from test order by id;

ID MC
-------------------- ------------------------------------------------------------
A AAAAA
B BBBBB
a aaaaa
b bbbbb

執行計劃
----------------------------------------------------------
Plan hash value: 2007178810

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 176 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST | 4 | 176 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

統計資訊
----------------------------------------------------------
48 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> ALTER SESSION SET NLS_SORT=generic_m_ci;

會話已更改。

SQL> select * from test order by id;

ID MC
-------------------- ------------------------------------------------------------
A AAAAA
a aaaaa
B BBBBB
b bbbbb

執行計劃
----------------------------------------------------------
Plan hash value: 2007178810

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 176 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST | 4 | 176 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

統計資訊
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed

如果設定session的環境不方便,以可以設定語句級的排序

程式碼:
SQL> select * from test order by id;

ID MC
-------------------- ------------------------------------------------------------
A AAAAA
B BBBBB
a aaaaa
b bbbbb
SQL> select * from test order by nlssort(id, 'nls_sort=generic_m_ci');
ID                   MC
-------------------- ------------------------------------------------------------
A AAAAA
a aaaaa
B BBBBB
b bbbbb
[@more@]

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18921899/viewspace-1017479/,如需轉載,請註明出處,否則將追究法律責任。

相關文章