不準(zhǔn)確分析及規(guī)避方法
1 案例回訪(fǎng)
硬件環(huán)境：IBM XSERIES 3650
操作系統(tǒng)：Windows2003標(biāo)準(zhǔn)版+Sp02
數(shù)據(jù)庫(kù)版本：Oracle9.2.0.1
PL/SQL Developer版本：7.0.2.1076
由于業(yè)務(wù)需要，需先drop一張分區(qū)表T_SMS，再重建此表，要求表結(jié)構(gòu)、索引等完全一致。但當(dāng)時(shí)開(kāi)局版本一時(shí)無(wú)法找到，故決定利用PL/SQL Developer工具“View SQL”獲取到的SQL語(yǔ)句來(lái)重建分區(qū)表。(此分區(qū)表按天進(jìn)行分區(qū)，且由于每天數(shù)據(jù)量龐大，故每天凌晨都會(huì)truncate上個(gè)月的數(shù)據(jù)，保證表中最多只保留30天數(shù)據(jù)。)
重建表后第二天發(fā)現(xiàn)此表索引失效，導(dǎo)致無(wú)法進(jìn)行insert等操作。經(jīng)過(guò)分析，truncate分區(qū)操作最可能造成索引失效。但此表建立的是本地分區(qū)索引，按天truncate操作后，Oracle會(huì)自動(dòng)重建本地分區(qū)索引，不應(yīng)該需要手工干預(yù)，且以前此表沒(méi)有出現(xiàn)過(guò)索引失效的問(wèn)題。故初步判斷可能是新舊表結(jié)構(gòu)不同造成，立即查看此表索引類(lèi)型，發(fā)現(xiàn)索引并不是本地索引，且從PL/SQL Developer工具中導(dǎo)出的建索引語(yǔ)句中也并不是本地索引。
那為何原表索引為本地分區(qū)索引，利用PL/SQL Developer工具導(dǎo)出腳本后索引類(lèi)型就改變了呢?是不是PL/SQL Developer工具在導(dǎo)出索引腳本方面存在bug?
2 案例分析
剛才已經(jīng)通過(guò)查看當(dāng)時(shí)建表語(yǔ)句即可定位為PL/SQL Developer工具導(dǎo)出的腳本問(wèn)題，我們可以再通過(guò)以下幾個(gè)試驗(yàn)來(lái)驗(yàn)證一下。
2.1 建索引時(shí)加local關(guān)鍵字
(1)建索引語(yǔ)句如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY)
local tablespace SMS_DAT;
(2)利用PL/SQL Developer工具“View SQL”獲取的建索引腳本如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY);
(3)分析：
可見(jiàn)，建本地分區(qū)索引后再利用PL/SQL Developer工具導(dǎo)出腳本即變?yōu)榱巳炙饕?，索引?lèi)型發(fā)生了變化。
2.2 建索引時(shí)不加local關(guān)鍵字或加global關(guān)鍵字
(1)建索引語(yǔ)句如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY)
[global] tablespace SMS_DAT;
(2)利用PL/SQL Developer工具“View SQL”獲取的建索引腳本如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY)
tablespace SMS_DAT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
(3)分析：
可見(jiàn)，建索引時(shí)無(wú)論不加local關(guān)鍵字還是加global關(guān)鍵字再利用PL/SQL Developer工具導(dǎo)出的腳本都變?yōu)榱巳炙饕?，同時(shí)多了些存儲(chǔ)相關(guān)的選項(xiàng)。
2.3 分別利用PL/SQL Developer 工具的“Oracle Export”、“SQL Insert”、“PL/SQL Developer”三種方式導(dǎo)出
通過(guò)導(dǎo)出后再導(dǎo)入的方式來(lái)驗(yàn)證，只有“Oracle Export”方式導(dǎo)出再導(dǎo)入后，表結(jié)構(gòu)和索引結(jié)構(gòu)沒(méi)有發(fā)生變化，另兩種方式都改變了索引類(lèi)型。 3 規(guī)避方法介紹
從上述對(duì)比試驗(yàn)中可以看出，只有Oracle自帶的Export命令導(dǎo)出再導(dǎo)入后的索引結(jié)構(gòu)和原有結(jié)構(gòu)一致，其他方式都無(wú)法準(zhǔn)確獲取到分區(qū)索引的腳本。
雖然我們可以通過(guò)把Export導(dǎo)出條件設(shè)置為不存在結(jié)果集來(lái)導(dǎo)出空表，再利用導(dǎo)出的dump文件導(dǎo)入到其他庫(kù)來(lái)新建表和索引。但這種方式無(wú)法直觀(guān)的看到建表的SQL腳本，可擴(kuò)展性差。
本地索引：
CREATE INDEX T_SMS_IDMONTHDAY ON T_SMS
(MONTHDAY)
TABLESPACE SMS_DAT
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P01
LOGGING
NOCOMPRESS
TABLESPACE SMS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P02
LOGGING
NOCOMPRESS
TABLESPACE SMS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
……（此處省略部分代碼）
)
NOPARALLEL;
全局索引：
CREATE INDEX T_SMS_IDMONTHDAY ON T_SMS
(MONTHDAY)
LOGGING
TABLESPACE SMS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
1 案例回訪(fǎng)
硬件環(huán)境：IBM XSERIES 3650
操作系統(tǒng)：Windows2003標(biāo)準(zhǔn)版+Sp02
數(shù)據(jù)庫(kù)版本：Oracle9.2.0.1
PL/SQL Developer版本：7.0.2.1076
由于業(yè)務(wù)需要，需先drop一張分區(qū)表T_SMS，再重建此表，要求表結(jié)構(gòu)、索引等完全一致。但當(dāng)時(shí)開(kāi)局版本一時(shí)無(wú)法找到，故決定利用PL/SQL Developer工具“View SQL”獲取到的SQL語(yǔ)句來(lái)重建分區(qū)表。(此分區(qū)表按天進(jìn)行分區(qū)，且由于每天數(shù)據(jù)量龐大，故每天凌晨都會(huì)truncate上個(gè)月的數(shù)據(jù)，保證表中最多只保留30天數(shù)據(jù)。)
重建表后第二天發(fā)現(xiàn)此表索引失效，導(dǎo)致無(wú)法進(jìn)行insert等操作。經(jīng)過(guò)分析，truncate分區(qū)操作最可能造成索引失效。但此表建立的是本地分區(qū)索引，按天truncate操作后，Oracle會(huì)自動(dòng)重建本地分區(qū)索引，不應(yīng)該需要手工干預(yù)，且以前此表沒(méi)有出現(xiàn)過(guò)索引失效的問(wèn)題。故初步判斷可能是新舊表結(jié)構(gòu)不同造成，立即查看此表索引類(lèi)型，發(fā)現(xiàn)索引并不是本地索引，且從PL/SQL Developer工具中導(dǎo)出的建索引語(yǔ)句中也并不是本地索引。
那為何原表索引為本地分區(qū)索引，利用PL/SQL Developer工具導(dǎo)出腳本后索引類(lèi)型就改變了呢?是不是PL/SQL Developer工具在導(dǎo)出索引腳本方面存在bug?
2 案例分析
剛才已經(jīng)通過(guò)查看當(dāng)時(shí)建表語(yǔ)句即可定位為PL/SQL Developer工具導(dǎo)出的腳本問(wèn)題，我們可以再通過(guò)以下幾個(gè)試驗(yàn)來(lái)驗(yàn)證一下。
2.1 建索引時(shí)加local關(guān)鍵字
(1)建索引語(yǔ)句如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY)
local tablespace SMS_DAT;
(2)利用PL/SQL Developer工具“View SQL”獲取的建索引腳本如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY);
(3)分析：
可見(jiàn)，建本地分區(qū)索引后再利用PL/SQL Developer工具導(dǎo)出腳本即變?yōu)榱巳炙饕?，索引?lèi)型發(fā)生了變化。
2.2 建索引時(shí)不加local關(guān)鍵字或加global關(guān)鍵字
(1)建索引語(yǔ)句如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY)
[global] tablespace SMS_DAT;
(2)利用PL/SQL Developer工具“View SQL”獲取的建索引腳本如下：
create index T_SMS_IDMONTHDAY on T_SMS (MONTHDAY)
tablespace SMS_DAT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
(3)分析：
可見(jiàn)，建索引時(shí)無(wú)論不加local關(guān)鍵字還是加global關(guān)鍵字再利用PL/SQL Developer工具導(dǎo)出的腳本都變?yōu)榱巳炙饕?，同時(shí)多了些存儲(chǔ)相關(guān)的選項(xiàng)。
2.3 分別利用PL/SQL Developer 工具的“Oracle Export”、“SQL Insert”、“PL/SQL Developer”三種方式導(dǎo)出
通過(guò)導(dǎo)出后再導(dǎo)入的方式來(lái)驗(yàn)證，只有“Oracle Export”方式導(dǎo)出再導(dǎo)入后，表結(jié)構(gòu)和索引結(jié)構(gòu)沒(méi)有發(fā)生變化，另兩種方式都改變了索引類(lèi)型。 3 規(guī)避方法介紹
從上述對(duì)比試驗(yàn)中可以看出，只有Oracle自帶的Export命令導(dǎo)出再導(dǎo)入后的索引結(jié)構(gòu)和原有結(jié)構(gòu)一致，其他方式都無(wú)法準(zhǔn)確獲取到分區(qū)索引的腳本。
雖然我們可以通過(guò)把Export導(dǎo)出條件設(shè)置為不存在結(jié)果集來(lái)導(dǎo)出空表，再利用導(dǎo)出的dump文件導(dǎo)入到其他庫(kù)來(lái)新建表和索引。但這種方式無(wú)法直觀(guān)的看到建表的SQL腳本，可擴(kuò)展性差。
本地索引：
CREATE INDEX T_SMS_IDMONTHDAY ON T_SMS
(MONTHDAY)
TABLESPACE SMS_DAT
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P01
LOGGING
NOCOMPRESS
TABLESPACE SMS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P02
LOGGING
NOCOMPRESS
TABLESPACE SMS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
……（此處省略部分代碼）
)
NOPARALLEL;
全局索引：
CREATE INDEX T_SMS_IDMONTHDAY ON T_SMS
(MONTHDAY)
LOGGING
TABLESPACE SMS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;