Oracle 技術探討2
6、簡易研究ROWNUM的方法
實驗獲取的知識點（同時也是易錯點）
1、永遠找不到rownum>1的資料
原因：ROWNUM是從1開始排序的，第一條始終是1，當查詢到第一條記錄時，該記錄的ROWNUM為1，但條件要求ROWNUM>1，不符合要求繼續查詢下一條，由於前面無符合條件的記錄，所以下一條就變成第一條，而第一條ORACLE始終規定為1，如此下去，自然找不到ROWNUM>1的值了。
2、永遠找不到rownum=2 的資料（不只是2，只要是大於1的，都找不到，如rownum=3也不可能找到）
原因：不用多說了，就是上面的解釋。
3、上訴的兩種錯誤的主觀想法該如何實現呢？只要使用檢視，將rownum實體化，即可成功，具體見實驗。
4、欄位排序再取前n條資料的誤區
oracle會先按物理順序取出滿足rownum條件的記錄，然後再對這些記錄按照order by 的欄位排序，因此如果不注意寫法，往往不是我們想要的結果
drop table ljb_test ;
create table ljb_test as select * from dba_objects;
SQL> drop table ljb_test ;
Table dropped
SQL> create table ljb_test as select * from dba_objects;
Table created
SQL> select count(*) from ljb_test;
COUNT(*)
----------
80010
SQL> select count(*) from ljb_test where rownum>0;
COUNT(*)
----------
80010
SQL> select count(*) from ljb_test where rownum>1;
COUNT(*)
----------
0 ----根本找不到你所想要的記錄！
SQL> select count(*) from ljb_test where rownum=1;
COUNT(*)
----------
1
SQL> select count(*) from ljb_test where rownum=2;
COUNT(*)
----------
0 ----根本找不到你所想要的記錄
如果真要取你想的記錄，該如何做呢？
方法很簡單，用上子查詢，將rownum實體化，即可解決問題！
SQL> select count(*) from (select t.*,rownum rn from ljb_test t) where rn>1;
COUNT(*)
----------
80009
SQL> select count(*) from (select t.*,rownum rn from ljb_test t) where rn=2;
COUNT(*)
----------
1
--以下寫法是從表中按照物理順序取出5條記錄，然後再把這些記錄按照object_name的列進行排序，往往不是我們想要的結果。
SQL> select object_id,object_name from ljb_test where rownum <= 5 order by object_name;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
28 CON$
29 C_COBJ#
20 ICOL$
44 I_USER1
15 UNDO$
---以下寫法是根據object_name先排序，這個正是我們所期望的先排序、再取特定記錄數的演算法。
SQL> select object_id,object_name from (select object_id,object_name from ljb_test order by object_name) where rownum <= 5;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
30796 /1000e8d1_LinkedHashMapValueIt
30797 /1000e8d1_LinkedHashMapValueIt
17108 /1005bd30_LnkdConstant
17109 /1005bd30_LnkdConstant
12512 /10076b23_OraCustomDatumClosur
--以上是rownum的一些基礎知識，實際應用中rownum的應用範圍非常廣泛，體現在下面三點，但是限於簡練的原則，避免文章太過複雜，將在後續優化篇中體現三點思路（這裡就略去不說）
1、在分頁演算法中靈活應用，將起到意想不到的效果。
2、減少函式呼叫（利用實體化檢視）
3、利用rownum優化表連線
7、縮小表空間方法的簡易研究
做了個小實驗，關於表空間的小小研究，具體步驟如下：
獲取知識點
1、檢視錶空間使用的指令碼方法
簡單，無非就是dba_data_Files和dba_free_space兩張資料字典的結合。
2、縮小表空間可以考慮使用resize方法（10g也可以考慮shrink)
3、表空間能縮小多少的估算方法
4、10G中，要把當前表空間的回收站清空一下，否則無法resize表空間，這點我忽略了，多謝小V兄弟提醒！
5、臨時表空間使用的關鍵字tempfile
alter database tempfile '檔案路徑名' resize xxM;
可以通過select max(segblk#) from v$sort_usage來檢視當前能縮小的最大空間，可以和本實驗普通檔案的縮小方法比較。
----首先，用如下方法找檢視當前表空間使用情況
SELECT A.TABLESPACE_NAME "表空間名",
A.TOTAL_SPACE "總空間(M)",
NVL(B.FREE_SPACE, 0) "剩餘空間(M)",
A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空間(M)",
trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) "剩餘百分比%"
FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024 ,2) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024 ),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
表空間名 總空間(M) 剩餘空間(M) 使用空間(M) 剩餘百分比%
TBS_FJZHJS 7167 6648.37 518.63 92.76
SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M;
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M
ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料
奇怪，這個是為什麼呢？
1、計算表空間能縮小多少的方法
SQL> select max(block_id) from dba_extents where file_id=78;
MAX(BLOCK_ID)
-------------
524153
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select 524153*8/1024 from dual;
524153*8/1024
-------------
4094.9453125 ---由此可見，這個表空間的當前的資料檔案，至少需要4G這麼大，前面要縮小為2G，當然失敗了！
--我們還可以看看是哪個物件佔用
SQL> select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=78 and block_id=524153
2 ;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ---------- ----------
ZHJS_CONFIG PK_TPR_TABLE_GROUP INDEX TBS_FJZHJS 0 65536 8
SQL>
----這裡非常明顯的問題是，表空間明明還有90%幾剩餘，居然縮小不下來，顯而易見是HWM的問題，這種情況我們忽然想到用EXP/IMP來解決這個問題
這裡略去EXP/IMP傳輸表空間的步驟，單獨列在下一個實驗步驟第8小節中（為了單獨成文）
做完傳輸表空間的匯出再次匯入，原本以為可以縮小表空間了，實際發現並非如此！
SQL> select file#,name from v$datafile where name like '%FJ_ZHJS%';
FILE# NAME
---------- --------------------------------------------------------------------------------
16 D:\ORACLE\PRODUCT\10.2.0\ORADATA\FJ_ZHJS_03.DBF
17 D:\ORACLE\PRODUCT\10.2.0\ORADATA\FJ_ZHJS_02.DBF
18 D:\ORACLE\PRODUCT\10.2.0\ORADATA\FJ_ZHJS.DBF
SQL> select max(block_id) from dba_extents where file_id=17;
MAX(BLOCK_ID)
-------------
524153
---看到這個524153，大喊糟糕，玩了EXP/IMP表空間，對縮小資料檔案沒幫助！
SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M;
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M
ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料
啥原因呢？？？
換個方法
執行這個
SELECT 'alter table ' || OWNER || '.' || TABLE_NAME ||
' move TABLESPACE USERS;'
FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'TBS_FJZHJS'
然後再MOVE回來
結果還是不成立
SQL> select max(block_id) from dba_extents where file_id=17;
MAX(BLOCK_ID)
-------------
524153
SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M;
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 2048M
ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料
---把索引也移走一下
SELECT 'alter index' || OWNER || '.' || INDEX_NAME ||
' rebuild TABLESPACE USERS;'
FROM DBA_INDEXES
WHERE TABLESPACE_NAME = 'TBS_FJZHJS'
然後再REBUILD一下MOVE回來
SQL> select max(block_id) from dba_extents where file_id=17;
MAX(BLOCK_ID)
-------------
519681
SQL> select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=17 and block_id= 519681;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ---------- ----------
ZHJS_CONFIG IDX_CLASSIFIED_TARIFF_A_6 INDEX TBS_FJZHJS 0 65536 8
SQL>
SQL> select max(block_id) from dba_extents where file_id=17;
MAX(BLOCK_ID)
-------------
519673
---看到這個519673，心裡一寒，沒啥用，還是沒啥縮小！
--執行如下發現一堆很大得到BLOCK_ID的，到某個層面後，忽然變小了。。
select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks,block_Id from dba_extents where file_id=17
ORDER BY Block_id DESC
要不考慮一下直接MOVE走，不移回來了。。。。
然後檢視如下：
SQL> select max(block_id) from dba_extents where file_id=17;
MAX(BLOCK_ID)
-------------
2025
現在肯定可以縮小表空間了，看到這個2025，心花怒放，現在肯定可以縮小了！
SQL> select 2025*8/1024 from dual;
2025*8/1024
-----------
15.8203125
SQL>
可以縮小到15M啊
實際操作 ，可是實際上居然不能縮小？？？？？
SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M;
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M
ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料
實在奇怪，因為這個file_id=17的檔案只生效一個記錄了，其他都被我MOVE了啊？而且BOCK_ID是2025，超級小啊！！！！
select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks,block_Id from dba_extents where file_id=17
ORDER BY Block_id DESC
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS BLOCK_ID
1 VAJS PLAN_TABLE TABLE TBS_FJZHJS 0 65536 8 2025
SQL> drop table VAJS.PLAN_TABLE;
Table dropped
SQL> select max(block_id) from dba_extents where file_id=17;
MAX(BLOCK_ID)
-------------
select owner,segment_name,segment_type,tablespace_name,extent_id,bytes,blocks,block_Id from dba_extents where file_id=17
ORDER BY Block_id DESC
都已經無記錄了
SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M;
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M
ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料
---這裡感謝小V兄弟的提醒
SELECT * FROM dba_recyclebin;
發現剛才DROP 的東東在垃圾箱裡！！！！
SQL> PURGE recyclebin
2 ;
SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\FJ_ZHJS_02.DBF' RESIZE 1024M;
Database altered
8、EXP/IMP傳輸表空間的簡易方法
--玩啥呢，就玩EXP/IMP表空間吧，我用EXP/IMP多次了，從未用過EXP出表空間的方法，雖然官方文件是看過多次了，但是從未實驗過。
知識點：
1、傳輸表空間必須是SYSDBA使用者操作
2、表空間裡的物件集是自包含的(物件及其索引都在此表空間中)
這個要用 exec sys.dbms_tts.transport_set_check來執行校驗，然後在transport_set_violations中檢查
3、要將傳輸的表空間設定為只讀
alter tablespace tbs_fjzhjs read only;
4、完成後要將傳輸匯入的表空間屬性設定為可讀寫。
alter tablespace tbs_fjzhjs read write;
C:\Documents and Settings\fujitsu>exp system/rqrq@rqrq tablespaces='TBS_FJZHJS' transport_tablespace
=y file=c:\tbs_fjzhjs.dmp
Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 20:40:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
EXP-00044: 必須以 'SYSDBA' 身份連線, 才能進行時間點恢復或可傳輸的表空間匯入
EXP-00000: 匯出終止失敗
C:\Documents and Settings\fujitsu>exp 'sys/change_on_install as sysdba ' tablespaces='TBS_FJZHJS' t
ansport_tablespace=y file=c:\tbs_fjzhjs.dmp
Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 20:47:57 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 將不匯出表資料 (行)
即將匯出可傳輸的表空間後設資料...
EXP-00008: 遇到 ORACLE 錯誤 29335
ORA-29335: 表空間 'TBS_FJZHJS' 不為只讀
ORA-06512: 在 "SYS.DBMS_PLUGTS", line 507
ORA-06512: 在 line 1
EXP-00000: 匯出終止失敗
C:\Documents and Settings\fujitsu>
C:\Documents and Settings\fujitsu>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 4 20:48:32 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter tablespace tbs_fjzhjs read only;
C:\Documents and Settings\fujitsu>exp 'sys/change_on_install as sysdba ' tablespaces='TBS_FJZHJS' t
ansport_tablespace=y file=c:\tbs_fjzhjs.dmp
Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 21:20:57 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 將不匯出表資料 (行)
即將匯出可傳輸的表空間後設資料...
前面略去。。。
. . 正在匯出表 PLAN_TABLE
. . 正在匯出表 TEST
. . 正在匯出表 TPVA_MGACCT_MAP_091028
. . 正在匯出表 TPVA_MGACCT_RULE_091028
. . 正在匯出表 TPVA_SETT_DEFINE_091028
. . 正在匯出表 TPVA_SETT_JIETI_091028
. . 正在匯出表 TPVA_SETT_RULE_091028
. . 正在匯出表 TPVA_TANFEN_RULE_091028
. . 正在匯出表 TP_JUDGE_ACC
. 正在匯出引用完整性約束條件
. 正在匯出觸發器
. 結束匯出可傳輸的表空間後設資料
成功終止匯出, 沒有出現警告。
Export: Release 10.2.0.1.0 - Production on 星期三 11月 4 21:29:41 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 將不匯出表資料 (行)
即將匯出可傳輸的表空間後設資料...
EXP-00008: 遇到 ORACLE 錯誤 29341
ORA-29341: 可傳送集不是自包含的
ORA-06512: 在 "SYS.DBMS_PLUGTS", line 1387
ORA-06512: 在 line 1
SQL> exec sys.dbms_tts.transport_set_check('TBS_FJZHJS',true);
PL/SQL 過程已成功完成。
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Index LJB.IDX1_BILLING_NBR in tablespace TBS_FJZHJS points to table LJB.TICKET_1
60 in tablespace USERS
Index LJB.IDX2_BILLING_NBR in tablespace TBS_FJZHJS points to table LJB.TICKET_1
68 in tablespace USERS
SQL> drop index LJB.IDX1_BILLING_NBR ;
索引已刪除。
SQL> drop index LJB.IDX2_BILLING_NBR ;
索引已刪除。
SQL> exec sys.dbms_tts.transport_set_check('TBS_FJZHJS',true);
PL/SQL 過程已成功完成。
SQL>
SQL> select * from transport_set_violations;
未選定行
. . 正在匯出表 PLAN_TABLE
. . 正在匯出表 TEST
. . 正在匯出表 TPVA_MGACCT_MAP_091028
. . 正在匯出表 TPVA_MGACCT_RULE_091028
. . 正在匯出表 TPVA_SETT_DEFINE_091028
. . 正在匯出表 TPVA_SETT_JIETI_091028
. . 正在匯出表 TPVA_SETT_RULE_091028
. . 正在匯出表 TPVA_TANFEN_RULE_091028
. . 正在匯出表 TP_JUDGE_ACC
. 正在匯出引用完整性約束條件
. 正在匯出觸發器
. 結束匯出可傳輸的表空間後設資料
成功終止匯出, 沒有出現警告。
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop tablespace tbs_fjzhjs;
drop tablespace tbs_fjzhjs
*
第 1 行出現錯誤:
ORA-01549: 表空間非空, 請使用 INCLUDING CONTENTS 選項
SQL> drop tablespace tbs_fjzhjs including contents
2 ;
表空間已刪除。
C:\Documents and Settings\fujitsu>imp 'sys/change_on_install as sysdba ' tablespaces='TBS_FJZHJS' tr
ansport_tablespace=y datafiles='D:\oracle\product\10.2.0\oradata\FJ_ZHJS.DBF','D:\oracle\product\10.
2.0\oradata\FJ_ZHJS_02.dbf','D:\oracle\product\10.2.0\oradata\FJ_ZHJS_03.dbf' file='c:\tbs_fjzhjs.
dmp'
. . 正在匯入表 "TPVA_MGACCT_MAP_091028"
. . 正在匯入表 "TPVA_MGACCT_RULE_091028"
. . 正在匯入表 "TPVA_SETT_DEFINE_091028"
. . 正在匯入表 "TPVA_SETT_JIETI_091028"
. . 正在匯入表 "TPVA_SETT_RULE_091028"
. . 正在匯入表 "TPVA_TANFEN_RULE_091028"
. . 正在匯入表 "TP_JUDGE_ACC"
. 正在將 LJB 的物件匯入到 LJB
. 正在將 ZHJS_CONFIG 的物件匯入到 ZHJS_CONFIG
. 正在將 LJB 的物件匯入到 LJB
. 正在將 ZHJS_CONFIG 的物件匯入到 ZHJS_CONFIG
即將啟用約束條件...
. 正在將 SYS 的物件匯入到 SYS
成功終止匯入, 沒有出現警告。
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter tablespace tbs_fjzhjs read write;
表空間已更改。
9、切換臨時表空間和回滾段表空間簡易方法
知識點（某些場合，在臨時表空間和回滾段表空間異常擴充套件無法回收的時候，需要採用這種方法）
1、瞭解當前臨時表空間是哪個
2、如何切換臨時表空間
3、瞭解當前回滾段表空間是哪個
4、如何切換回滾段表空間。
以下是我輕鬆完成切換臨時表空間的實驗過程：
SQL> SELECT username,temporary_tablespace FROM dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LJB TEMP2
ZHJS_APPTEST TEMP2
ZHJS_APP TEMP2
MGMT_VIEW TEMP2
SYS TEMP2
SYSTEM TEMP2
SQL> SELECT NAME FROM v$tempfile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\TEMP02.DBF
SQL> create temporary tablespace temp tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\TEMP03.DBF' size 100M;
Tablespace created
SQL> alter tablespace temp add tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\TEMP04.DBF' size 20M;
Tablespace altered
SQL> alter database default temporary tablespace temp;
Database altered
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
LJB TEMP
WMSYS TEMP
CTXSYS TEMP
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
SQL> drop tablespace temp2;
Tablespace dropped
以下是我輕鬆完成切換回滾段表空間的過程
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
--可看出當前回滾段表空間為undotbs1
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
--檢視回滾表空間的資料檔案路徑及大小
SQL> select file_name,bytes/1024/1024/1024 from dba_data_Files where tablespace_name like 'UNDOTBS1';
FILE_NAME BYTES/1024/1024/1024
-------------------------------------------------------------------------------- --------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\UNDOTBS01.DBF 0.7568359375
---undo segment的擴充套件狀態無異常：
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 0.00035858154296875 0.00035858154296875 0
2 0 0.00109100341796875 0.00597381591796875 1
3 0 0.00109100341796875 0.00499725341796875 1
4 0 0.00109100341796875 0.00402069091796875 1
10 0 0.00109100341796875 0.00389862060546875 3
6 0 0.00109100341796875 0.0108566284179688 3
7 0 0.00109100341796875 0.00499725341796875 1
8 0 0.00109100341796875 0.00695037841796875 1
9 0 0.00109100341796875 0.00499725341796875 2
1 0 0.00109100341796875 0.00499725341796875 1
5 0 0.00109100341796875 0.00499725341796875 2
11 rows selected
---新建一回滾段表空間
SQL> create undo tablespace undotbs2 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\RQRQ\UNDOTBS03.DBF' size 100M;
Tablespace created
---切換
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered
--生效
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
10、SQLPLUS工具研究
知識是由點到線，由線到面的一個過程，我覺得平常經常記錄下工作中的心得體會，並記錄下實驗心得，彙總起來，形成面後，解決問題就有可能做到融會貫通。下面來探討一下最近在工作中用到SQLPLUS的系列小結。後續會多一些類似這樣開發相關的研究，併發出與大家共享。其中10.2是資料庫開發大牛NEWKID兄指點的，感謝大牛NEWKID兄。
SQLPLUS很多人用的並不多，在我觀察周圍來看，很多人都在使用PLSQL DEVELOPER，尤其是開發人員，更是如此，那學習SQLPLUS有啥好處呢？在我看來有如下三點
1、當我們要在UNIX平臺用SHELL訪問資料庫（如：做一些後臺操作，定時任務等等），這個時候SQLPLUS是唯一選擇。
2、SQLPLUS 是ORACLE自帶的工具，只要安裝了資料庫就有了，而PLSQL DEVELOPE等工具還要獨立安裝。並且由於是客戶端工具，在網路故障或者是主機效能障礙情況下，往往根本就無法登陸進該工具。
3、每個工具都各有所長，SQLPLUS少了PLSQL DEVELOPER的視覺化的方便性，自然有其他獨到的優勢，在下文中會簡要說明。。。
10.1 sqlplus工具特有的COPY功能
SQL*Plus Copy 命令的語法：
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query
Append - 向已有的目標表中追加記錄，如果目標表不存在，自動建立，這種情況下和Create等效。
Create - 建立目標表並且向其中追加記錄，如果目標表已經存在，則會返回錯誤。
Insert - 向已有的目標表中插入記錄，與Append不同的是，如果目標表不存在，不自動建立而是返回錯誤。
Replace - 用查詢出來的資料覆蓋已有的目標表中的資料，如果目標表不存在，自動建立。
根據最近使用的經驗來看，我認為好處有如下：
1、直接在不同資料庫中進行拷貝動作，比傳統方法需建DBLINK更方便易用多了。
2、DBLINK一旦建立，如果出於安全考慮，很可能要刪除掉，否則會留下安全隱患(想著想著多想了一個理由）。
3、CREATE TABLE XXX AS SELECT * FROM XXX@dblink或
inser into table select *from xxx@dblink的方法必須要保證目標表沒有該表或者已經有該表。 而COPY APPEND,REPLACE不存在自動建立，有存在則自動插入，靈活的多。（如果要判斷了選擇CRDATE,INSERT,可讓我們有選擇的餘地）
4、在已經有資料的情況下，SQLPLUS的APPEND命令追加，比INSERT INTO SELECT 高效
5、如果不考慮效能，比如只是實時同步兩庫的引數配置小表，那在SHELL中批量寫COPY+REPLACE語句，CRONTAB定時同步，就可以保證兩庫配置表是基本一致的，這個在大表不實用，在小表就一定大有用武之地！（這個是在自己多次反覆同步配置表後，無意想到的）
此外我整了幾個注意點：
1、這些都是直接路徑讀，所以COPY+APPEND，INSERT也是直接提交成功的，所有的動作操作後都無法回滾。
2、雖然主要適用於不同庫之間，但是在同一庫不同使用者也使用，甚至同一庫同一使用者也一樣（同使用者表名要不一樣），就是FROM TO寫好則可。
3、寫法習慣要注意，COPY+CREATE 後面不要增加TABLE的關鍵字，經常收到ORACLE語法誤導，會多寫了這個。
實驗：
本機筆記本上10G環境測試可以連到測試環境9I資料庫
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\fujitsu>tnsping dev_db
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-11月-2009 10:43:26
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的引數檔案:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fjpta)))OK (580 毫秒)
下面實驗直接COPY命令
C:\Documents and Settings\fujitsu>sqlplus ljb/ljb
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db create ljb_test using select * from ljb_test;
陣列提取/繫結大小為 15。(陣列大小為 15)
將在完成時提交。(提交的副本為 0)
最大 long 大小為 80。(long 為 80)
表 LJB_TEST 已建立。
267 行選自 ljb@rqrq。
267 行已插入 LJB_TEST。
267 行已提交至 LJB_TEST (位於 vajs@dev_db)。
--這裡注意，create ljb_test 我老是會根據ORACLE語法習慣寫成create table ljb_test，忘記了SQLPLUS語法中沒有這個TABLE關鍵字，這就是思維習慣了，呵呵。
這個等效的方法在ORACLE中如何實現，大家都知道，應該是 CREATE TABLE LJB_TEST AS SELECT * FROM LJB_TEST@DBLINK WHERE XXX條件,這個時候就一定要建DBLINK，多了一個步驟，而且大家一定有過沒有許可權建DBLINK或者是忘記DBLINK語法的經歷，如此COPY命令就省事多了（此外我還想到另一種情況，就是DBLINK一旦建立了，就有可能被人使用，如果出於安全不想讓人用，建立了還要立即刪除了，如果用COPY命令，這個安全問題就無需考慮了）。
當我們要繼續新插入資料該如何寫呢？
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db append vajs.ljb_test using select * from ljb.ljb_tes
t
陣列提取/繫結大小為 15。(陣列大小為 15)
將在完成時提交。(提交的副本為 0)
最大 long 大小為 80。(long 為 80)
267 行選自 ljb@rqrq。
267 行已插入 VAJS.LJB_TEST。
267 行已提交至 VAJS.LJB_TEST (位於 vajs@dev_db)。
查檢視資料，本地10G環境ljb_test表記錄267條
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> SELECT COUNT(*) FROM ljb_test;
COUNT(*)
----------
267
9I測試環境ljb_test表記錄534條，看來確實是APPEND進去了。
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as vajs
SQL> SELECT COUNT(*) FROM ljb_test;
COUNT(*)
----------
534
再看看LJB_TEST存在情況下，COPY +INSERT如何？
發現插入OK，記錄增加到801，為了篇幅，步驟不貼！
那DROP 掉vajs 使用者下的ljb_test表後呢？
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db insert vajs.ljb_test using select * from ljb.ljb_tes
t
陣列提取/繫結大小為 15。(陣列大小為 15)
將在完成時提交。(提交的副本為 0)
最大 long 大小為 80。(long 為 80)
ERROR:
ORA-00942: table or view does not exist
看出來這個INSERT 是一定需要有表存在的。不像APPEND，可以追加。
最後實驗一下REPLACE(前面說過了，想依賴這個來實時同步兩庫的小記錄配置表）
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db replace vajs.ljb_test using select * from ljb.ljb_test
陣列提取/繫結大小為 15。(陣列大小為 15)
將在完成時提交。(提交的副本為 0)
最大 long 大小為 80。(long 為 80)
表 VAJS.LJB_TEST 已建立。
267 行選自 ljb@rqrq。
267 行已插入 VAJS.LJB_TEST。
267 行已提交至 VAJS.LJB_TEST (位於 vajs@dev_db)。
發現VAJS使用者下被DROP的表被建立了，記錄為267
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as vajs
SQL> SELECT COUNT(*) FROM vajs.ljb_test;
COUNT(*)
----------
267
--再執行一次！
SQL> copy from ljb/ljb@rqrq to vajs/vajs@dev_db replace vajs.ljb_test using select * from ljb.ljb_test
陣列提取/繫結大小為 15。(陣列大小為 15)
將在完成時提交。(提交的副本為 0)
最大 long 大小為 80。(long 為 80)
表 VAJS.LJB_TEST 已刪除。 ----呵呵，有表後的玄機就在這，不過是先刪除表再插入表而已！
表 VAJS.LJB_TEST 已建立。
267 行選自 ljb@rqrq。
267 行已插入 VAJS.LJB_TEST。
267 行已提交至 VAJS.LJB_TEST (位於 vajs@dev_db)。
發現9I資料庫的VAJS的ljb_test表記錄保持不變，看來用來做同步，還真好用：）
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as vajs
SQL> SELECT COUNT(*) FROM vajs.ljb_test;
COUNT(*)
----------
267
最後，經過測試，發現傳統CREATE TABLE XXX AS SELECT * FROM XXX@dblink 所花費的REDO最少
但是INSERT INTO XXX SELECT * FROM XXX@DBLINK卻不如COPY +APPEND所花費的REDO少。
通過這個瞭解到，如果考慮新增資料，甚至COPY+APPND還有效能上的優勢。
這個如何測試呢？我是測過了，但是考慮到文章的簡潔，限於篇幅，貼出來又刪除了。
請借鑑大家關注我的系列實驗的第一貼，呵呵，整理還是會有派場的：）
10.2 利用SQLPLUS的自動PRINT功能+REF CURSOR可以實現SQL SERVER中的過程中寫SELECT語句的寫法
這個小節源於同事提出的問題，經大牛NEWKID兄指點後，正好整理出來，作為SQLPLUS的一個功能補充
接觸過SQL SERVER的人都知道，在SQL SERVER中可以直接在過程中寫如下：
BEGIN
SELECT * FROM TEST where rownum=1;
end;
而這樣寫法在ORACLE的過程裡是行不通的，一定會報“中缺少 INTO 子句”這樣的錯誤，我最早接觸的資料庫是SQL SERVER,後來接觸到ORACLE後，立即就對這個差異產生了困惑，那我們如果要這樣實現，咋辦呢？
其實想想，這樣的需求還真是有的：
1、以前我最早使用SQL SERVER的時候，喜歡把非常非常複雜邏輯的SQL查詢語句整到過程中去，為啥，因為到時候執行查詢的時候就不要複製貼上一大段，如果名字起的好記些，我背都背住了，然後執行一下過程名就把我要的東西查詢出來了，寫什麼上線操作步驟，文章也可精煉多了！
2、如果是前臺展現語句能捕獲輸出的結果集，直接展現就OK了，否則還要插入到某表中，然後再展現某表，這個比較麻煩，（此外如果展現結果是動態的，欄位在變化，豈不是要建立N張表去展現了？）
實驗：
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (id1 int,id2 int);
Table created
SQL> insert into ljb_test select rownum,rownum+2 from dual connect by rownum<=5;
5 rows inserted
SQL> commit;
Commit complete
SQL> select * from ljb_test;
ID1 ID2
--------------------------------------- ---------------------------------------
1 3
2 4
3 5
4 6
5 7
SQL> CREATE OR REPLACE PROCEDURE p_test ( p_min in number,p_max in number,p_ref OUT SYS_REFCURSOR)
2 AS
3 BEGIN
4 OPEN p_ref for SELECT * from ljb_test where id1>=p_min and id1<=p_max;
5 END p_test;
6 /
過程已建立。
SQL>
SQL> SET AUTOPRINT ON
SQL> VAR p_ref REFCURSOR;
SQL> EXEC p_test(1,2,:p_ref);
PL/SQL 過程已成功完成。
ID1 ID2
---------- ----------
1 3
2 4
此外大家一定對select * from table(dbms_xplan.display)這個寫法不陌生，很多人一看就明白，這個是檢視執行計劃的語句（是在EXPLAIN PLAN FOR SQL語句後緊接著執行的命令），由此也可以得到啟發，但是這樣的查詢出
來的結果集是由SELECT語句觸發的，而非命令執行的。
table()典型例子如下：
SQL> CREATE or replace TYPE t_test AS OBJECT (
2 id1 NUMBER
3 ,id2 NUMBER
4 )
5 /
型別已建立。
SQL> CREATE TYPE tb_test AS TABLE OF t_test
2 /
型別已建立。
SQL> CREATE OR REPLACE FUNCTION f_test RETURN tb_test
2 AS
3 v_ret tb_test:=tb_test();
4 BEGIN
5 FOR i IN 1..2 LOOP
6 v_ret.EXTEND;
7 v_ret(i) := t_test(i,i+2);
8 END LOOP;
9 RETURN v_ret;
10 END f_test;
11 /
函式已建立。
SQL> select * from table(f_test);
ID1 ID2
---------- ----------
1 3
2 4
TABLE()的方法就得先定義巢狀表，然後從表裡用SELECT BULK COLLECT INTO取得資料(把table()典型子中那個迴圈改為SELECT取資料)，再返回這個巢狀表，就能實現這個select * from ljb_test表的輸出了，不過相對而言，當前需求用這個方法相比“SQLPLUS的自動PRINT功能+REF CURSOR”的方法有些不適當，因為非常繁瑣！
如果一定要用select * from table()來處理，具體如下（t_test 和 tb_test 的type就不再重複建立了）
SQL> CREATE OR REPLACE FUNCTION f_test(p_min IN NUMBER,p_max IN NUMBER) RETURN tb_test
2 AS
3 v_ret tb_test:=tb_test();
4 BEGIN
5 SELECT t_test(id1,id2)
6 BULK COLLECT INTO v_ret
7 FROM ljb_TEST WHERE id1>=p_min AND id1<=p_max;
8
9 RETURN v_ret;
10 END f_test;
11 /
Function created
SQL> select * from table(f_test(1,2));
ID1 ID2
---------- ----------
1 3
2 4
SQL>
10.3 SQLPLUS SPOOL 功能
SQL> select count(*) from dba_tables where wner='YXL';
COUNT(*)
----------
4
SQL> SPOOL c:drop_table.sql
SQL> SELECT 'DROP TABLE '|| table_name ||';' FROM dba_tables where wner='YXL';
'DROPTABLE'||TABLE_NAME||';'
--------------------------------------------------------------------------------
DROP TABLE TEST_YXL;
DROP TABLE TEST_YXL1;
DROP TABLE YXL_TEST;
DROP TABLE YXL_TEST1;
SQL> SPOOL OFF
SQL> show user
USER 為 "YXL"
SQL> @c:\drop_table.sql
SP2-0734: 未知的命令開頭 "SQL> SELEC..." - 忽略了剩餘的行。
SP2-0734: 未知的命令開頭 "'DROPTABLE..." - 忽略了剩餘的行。
表已刪除。
表已刪除。
表已刪除。
表已刪除。
SP2-0734: 未知的命令開頭 "SQL> SPOOL..." - 忽略了剩餘的行。
以上spool+@執行命令指令碼可以寫進SHELL中，輕鬆實現定時批量對資料庫程式操作的任務。
注：大家可能注意到執行過程中有一點錯誤提示，雖然不影響最終的正確結果，但是卻甚為不美觀。原因在於
SQL> edit c:\drop_table.sql
會發現drop_table.sql中的記錄如下：
SQL> SELECT 'DROP TABLE '|| table_name ||';' dba_tables where wner='YXL';
'DROPTABLE'||TABLE_NAME||';'
--------------------------------------------------------------------------------
DROP TABLE TEST_YXL;
DROP TABLE TEST_YXL1;
DROP TABLE YXL_TEST;
DROP TABLE YXL_TEST1;
SQL> SPOOL OFF
其實實際上，我們只要有如下的結果就足矣了：
DROP TABLE TEST_YXL;
DROP TABLE TEST_YXL1;
DROP TABLE YXL_TEST;
DROP TABLE YXL_TEST1;
現在這個是批量刪除命令，出錯無非就不執行，還好，要是進行與sqlldr程式結合的操作，那出錯sqlldr就
會出錯了。咋辦呢，繼續看下面
10.4 其他相關引數實驗
set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading off
set timing off
set verify off
set numwidth 38
SPOOL c:\delete_table.sql
SELECT 'DELETE '|| table_name ||';' from dba_tables where wner='LJB' and rownum<=3;
SPOOL OFF
檢視
SQL> SELECT 'DELETE '|| table_name ||';' from dba_tables where wner='LJB' and rownum<=3;
DELETE TEST_ABCD;
DELETE TEST222;
DELETE TEST111;
SQL> SPOOL OFF
看上去好像還有點小問題，咋有這個SQL>的東東頭尾兩個啊，呵呵，用上sqlplus -s 引數進sqlplus後，再操作就消失了，最終結果就變成是很清爽的如下了
DELETE TEST_ABCD;
DELETE TEST222;
DELETE TEST111;
引數重點說明幾個，其他就不實驗了，一一羅列出簡單在附錄中說明
1、sqlplus 的s 引數（大小寫不區分）
大家平時可能沒有注意到使用這個引數，這個引數是幹什麼用的呢，原來-s的這個含義表示silent，將互動動作的提示符給隱藏了。
下面做個實驗一看便知。
C:\Documents and Settings\fujitsu>sqlplus -s ljb/ljb
select * from ljb_test;
ID1 ID2
---------- ----------
1 3
exit
C:\Documents and Settings\fujitsu>sqlplus ljb/ljb
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 15 14:03:26 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from ljb_test;
ID1 ID2
---------- ----------
1 3
2、set heading off可以將列名去掉
C:\Documents and Settings\fujitsu>sqlplus -s ljb/ljb
set heading off
select * from ljb_test;
1 3
具體其他詳細引數設定說明，這裡我就不一一實驗了，理解上面指令碼的其他引數設定可以去相關文件搜尋。此外還有其他各類引數，有興趣也可一併研究。
11、NULL的研究
在我看來,NULL是ORACLE中最"陰險"的東東,給開發人員帶來了許多的麻煩！怎麼個陰險狡詐呢？
下面例子都來自我在工作中發現並總結的小小心得，現在以實驗的形式整理如下，希望對大家有用。
11.1、update丟失資料
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> drop table ljb_test1;
Table dropped
SQL> drop table ljb_test2;
Table dropped
SQL> create table ljb_test1 (id1 int,id2 int);
Table created
SQL> create table ljb_test2 (id1 int,id2 int);
Table created
SQL> insert into ljb_test1 (id1,id2) values( 1,17);
1 row inserted
SQL> insert into ljb_test1 (id1,id2) values( 2,18);
1 row inserted
SQL> insert into ljb_test1 (id1,id2) values( null,18);
1 row inserted
SQL> insert into ljb_test2 (id1,id2) values( 1,27);
1 row inserted
SQL> insert into ljb_test2 (id1,id2) values( 2,28);
1 row inserted
SQL> insert into ljb_test2 (id1,id2) values( null,29);
1 row inserted
SQL> commit;
Commit complete
查詢兩表記錄情況情況，各自都有三條記錄，但是分別都有空值存在
SQL> select * from ljb_test1;
ID1 ID2
--------------------------------------- ----------------------------------
1 17
2 18
18
SQL> select * from ljb_test2;
ID1 ID2
--------------------------------------- --------------------------------
1 27
2 28
29
接下來利用ljb_test2去更新ljb_test1，關聯欄位為id1，將ljb_test2表的id2的值去更新ljb_test1表的id2的值。
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
3 rows updated
更新完畢後發現，ljb_test1表記錄確實被更新了，不過卻丟失了id1為空的那條記錄
SQL> select * from ljb_test1;
ID1 ID2
--------------------------------------- -------------------------------
1 27
2 28
SQL> select * from ljb_test2;
ID1 ID2
--------------------------------------- -------------------------------
1 27
2 28
29
上述指令碼未考慮空值，闖了大禍，平空丟了資料，回退資料
SQL> rollback;
Rollback complete
更改指令碼，新增了如下程式碼where exists (select 1 from ljb_test2 where ljb_test1.id1=ljb_test2.id1)，確保更新的結果集是ljb_test2的id1列在ljb_test1中的id1列中都能找到的結果集。避免了空值問題！
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1)
where exists (select 1 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
2 rows updated
接著檢視，發現ljb_test1記錄被正確更新了，這下空值記沒丟失了，仍然是三條記錄。
SQL> select * from ljb_test1;
ID1 ID2
--------------------------------------- --------------------------------------
1 27
2 28
18
SQL> select * from ljb_test2;
ID1 ID2
--------------------------------------- ---------------------------------------
1 27
2 28
29
總結：NULL空值會造成表更新過程中資料的丟失，要千萬小心操作！
11.2 update 更新錯資料
繼續做實驗如下，還是null值，不過這回null值出現在id2列了。
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
檢視錶記錄情況如下：
SQL> select * from ljb_test1;
ID1 ID2
------------------------------------------------------------------
3
1 17
2 18
3 16
SQL> select * from ljb_test2;
ID1 ID2
-------------------------------------------------------------------
5
1 27
2 28
仍用欠考慮空值的指令碼進行更新：
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
4 rows updated
檢視結果，發現更新沒有導致丟失了資料，卻是更新出問題了，把ljb_test1表原來的id1=3，id2=16的記錄更新為id2為null了，這個顯然不是要的結果！為什麼會這樣呢？
由於在ljb_test2中不存在id1欄位值為3的記錄.這樣在UPDATE的時候,ljb_test2表中找不到就會用null去UPDATE t1表id1=3對應的id2列的值，於是值由16更新為null
SQL> select * from ljb_test1;
ID1 ID2
-------------------------------------------------------------------
3
1 27
2 28
3
SQL> select * from ljb_test2;
ID1 ID2
--------------------------------------------------------------------
5
1 27
2 28
回退程式
SQL> rollback;
Rollback complete
用改進後的指令碼執行如下
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1)
where exists (select 1 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
2 rows updated
現在才是正確的結果，沒有出現更新錯誤的情況了!
SQL> select * from ljb_test1;
ID1 ID2
--------------------------------------- --------------------------
3
1 27
2 28
3 16
SQL> select * from ljb_test2;
ID1 ID2
--------------------------------------- ----------------------------
5
1 27
2 28
總結：NULL空值會造成表更新過程中資料的更新錯誤，要千萬小心操作！
11.3、ORACLE的NULL既等又不等
Null值=Null值嗎？有的人回答說Null=Null，有的人說Null<>Null，到底是等還是不等？先做實驗看看
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> select * from dual ;
DUMMY
-----
X
SQL> select * from dual where 1=1;
DUMMY
-----
X
SQL> select * from dual where 1=2;
DUMMY
-----
通過上述操作知道，可以用dual來證明一下Null到底等於不等於Null
先看看是否相等？
SQL> select * from dual where null=null;
DUMMY
-----
看來是不等，回答不等的朋友高興了，繼續實驗如下
SQL> select * from dual where null<>null;
DUMMY
-----
這下大家都有點摸不著頭腦了，怎麼條件也是假？也就是說在ORACLE中null=null或者null<>null都不是對的！
再看下面的寫法
SQL> select * from dual where null is null;
DUMMY
-----
X
看來也不是不完全不等的！
總結：這裡這樣理解才是正確的：“ORACLE把NULL值看成了一個未知的東西！”這點很特殊，在SQL Server和Sybase等其他版本的資料庫中不是這樣的，他們認為NULL=NULL是正確的，因此要特別留意ORACLE在NULL上的特殊性，再回到前一小節中丟失資料看看，應該更加深刻的明白了為什麼會丟失的原因，如果是SQL Server，前面的更新是不會丟失資料的，只是空值列對應的id2列會被test2表的id1為空的id2的值更新！
那我們在寫程式中要注意啥呢？對了NVL(VALUE1,0)=NVL(VALUE2,0) 這個是一個不錯的應對方法！
11.4 索引與NULL
大家對count（*）是否用得到索引的常識還是有點印象吧。 select count(*) from test 原來用不到索引(object_id列有一個普通索引），我加了“where object_id is not null”後便用走索引了，換個思路，對object_id改建主鍵後，索引也可以用到了，啥原因？
究其根本，就是因為索引不能儲存NULL！
本小節我將簡要的說明索引和NULL的關係
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (x int,y int);
Table created
SQL> create unique index idx_ljb_test on ljb_test(x,y);
Index created
SQL> insert into ljb_test values(1,1);
1 row inserted
SQL> insert into ljb_test values(1,null);
1 row inserted
SQL> insert into ljb_test values(null,1);
1 row inserted
SQL> insert into ljb_test values(null,null);
1 row inserted
下面執行前面我多次使用過的analyze index idx_ljb_test validate structure;命令
SQL> analyze index idx_ljb_test validate structure;
Index analyzed
檢視索引儲存的行數，很清楚的看明白了，當前索引只儲存了三行，而實際有四行，未儲存的就是null的那行記錄
SQL> select name,lf_rows from index_stats;
NAME LF_ROWS
------------------------------ --------------------------------------------
IDX_LJB_TEST 3
SQL> insert into ljb_Test values(1,null);
insert into ljb_Test values(1,null)
ORA-00001: 違反唯一約束條件 (LJB.IDX_LJB_TEST)
看來建了唯一索引後前面的資料不允許插入很正常，那試驗一下插入null,null是什麼情況？
SQL> insert into ljb_Test values(null,null);
1 row inserted
最終的結果是發現插入成功了！有就是說ORACLE並不認為這個null,null值是重複插入過的,也就是說情況如我說的4.7.2小節的情況，oracle把索引當成了一種未知，也就是null既不等於null也不完全不等於null
最後執行一個統計語句，這下發現奇怪了，怎麼在統計分類中，oracle又把null歸為了一類，原來在oracle中考慮唯一性的時候（null,null）與(null,null不同，而聚合查詢語句又認為是一樣的了！看來這個null要好好體會一下了。
SQL> select x,y,count(*) from ljb_test group by x,y having count(*)>1;
X Y COUNT(*)
--------------------------------------- ---------------------------------------
2
總結：ORACLE的索引不能儲存空值，這點大家通過實驗應該有了比較深刻的體會！另外再問大家一點，select * from test_ljb where object_id is null ,假如object_id有索引，這個索引能用到嗎？一起研究了這麼多，這個問題應該變的相當容易回答吧！
NULL陰險歸陰險，用清楚，確認是否真的需要非空，還可以讓COUNT(*)提速，呵呵。
11.5、反連線與NULL
SQL> connect ljb/ljb;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> alter system flush buffer_cache;
System altered
SQL> explain plan for select * from dept where deptno NOT IN ( select deptno from emp ) ;
Explained
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3547749009
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 76 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 39 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "EMP" "EMP" WHERE
LNNVL("DEPTNO"<>:B1)))
3 - filter(LNNVL("DEPTNO"<>:B1))
17 rows selected
SQL> explain plan for select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 474461924
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 3 | 96 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 76 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 13 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
3 - filter("DEPTNO" IS NOT NULL)
16 rows selected
這裡看到ANTI的熟悉的關鍵字了吧，這個表示反連線的意思，其實反連線的演算法也是經過ORACLE優化過的一種內部演算法，但是當關鍵謂詞列如果不是非空，將無法使用到該演算法。
用到後咋樣呢？COST由6變5，改進了，沒打出STATISTICS，事實上邏輯讀也大大減小了。看來NULL陰險歸陰險，用好也是有好處的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-665568/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 技術探討3Oracle
- Oracle 技術探討1Oracle
- Oracle 資料庫分散式技術的探討Oracle資料庫分散式
- 雲技術應用探討
- 熱更新技術探討，該如何選型
- HTTP服務七層架構技術探討HTTP架構
- [技術討論]科學基礎的分析和探討對話
- Oracle Stream 深入探討Oracle
- 12306 技術難點大探討
- Linux挖礦木馬的技術演進探討Linux
- 加殼技術探討－加殼時處理IAT
- Android技術棧(三)依賴注入技術的探討與實現Android依賴注入
- [技術分析]探討大世界遊戲的製作流程及技術——前期流程篇遊戲
- 關於如何防止重複簽到的技術探討
- oracle 雙機部署模式探討Oracle模式
- 非技術探討：文章定時釋出功能如何實現
- 基於Html5快取的頁面P2P技術可行性探討HTML快取
- 運營級WLAN網路架構及關鍵技術探討架構
- 資料庫設計中的反規範技術探討(轉)資料庫
- GBA的Tile模式技術探討－－空間佔用篇(轉)模式
- “技術沙龍”來襲，邀您一同探討 Serverless 資料庫技術最佳實踐Server資料庫
- ORACLE技術中國使用者討論組Oracle
- 探討大世界遊戲的製作流程及技術——大場景製作技術概況篇遊戲
- WIFI探針技術WiFi
- Promise探討Promise
- 從“微盟刪庫“事件探討銀行資料安全保護技術事件
- 探討AIGC的崛起歷程，淺析其背後技術發展AIGC
- Oracle Freelist和HWM原理探討及相關效能最佳化(2)Oracle
- 對軟體專案管理的探討(2)專案管理
- 我討厭技術猿
- 深入探討 UndefinedUndefined
- IsPostBack深入探討
- 對軟體專案管理的探討（2）（轉）專案管理
- Oracle中 HWM與資料庫效能的探討Oracle資料庫
- ORACLE專題深入探討精華帖連結收集Oracle
- Oracle資料庫同步技術2Oracle資料庫
- 10、DNS隧道技術再探DNS
- 追捕oicq探測技術 (轉)