如何提高oracle視圖的查詢(xún)速度？

1、可以縮小到5張表，因?yàn)楹芏喽际菑囊粡埍砝锶〕鰜?lái)的數(shù)據(jù)；

創(chuàng)新互聯(lián)專(zhuān)業(yè)為企業(yè)提供饒河網(wǎng)站建設(shè)、饒河做網(wǎng)站、饒河網(wǎng)站設(shè)計(jì)、饒河網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、饒河企業(yè)網(wǎng)站模板建站服務(wù)，十余年饒河做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

2、不能子查詢(xún)因?yàn)槭且@示數(shù)據(jù)子查詢(xún)只是查詢(xún)條件；

3不能建立索引，因?yàn)檫@樣會(huì)影響表的增刪改，它里面都是導(dǎo)入進(jìn)去的一次增加上千條都有可能；

4、定期結(jié)轉(zhuǎn)是什么意思，表示沒(méi)看懂。時(shí)間發(fā)的太長(zhǎng)的話(huà)就算了；

5、定期結(jié)轉(zhuǎn)的意思就是，將你要建立視圖的幾種表數(shù)據(jù)“轉(zhuǎn)移”到一張新表里面去，不用視圖查詢(xún)。數(shù)據(jù)庫(kù)全文檢索是RDBMS自帶的擴(kuò)展功能，可以實(shí)現(xiàn)高速查詢(xún)。全文檢索建議搜索下關(guān)鍵字，什么lucene之類(lèi)的就出來(lái)了。

如何加速Oracle大批量數(shù)據(jù)處理？

一、 提高DML操作的辦法：\x0d\x0a簡(jiǎn)單說(shuō)來(lái)：\x0d\x0a1、暫停索引，更新后恢復(fù).避免在更新的過(guò)程中涉及到索引的重建.\x0d\x0a2、批量更新，每更新一些記錄后及時(shí)進(jìn)行提交動(dòng)作.避免大量占用回滾段和或臨時(shí)表空間.\x0d\x0a3、創(chuàng)建一臨時(shí)的大的表空間用來(lái)應(yīng)對(duì)這些更新動(dòng)作．\x0d\x0a\x0d\x0a4、批量更新，每更新一些記錄后及時(shí)進(jìn)行提交動(dòng)作.避免大量占用回滾段和或臨時(shí)表空間.\x0d\x0a\x0d\x0a5、創(chuàng)建一臨時(shí)的大的表空間用來(lái)應(yīng)對(duì)這些更新動(dòng)作．\x0d\x0a\x0d\x0a6、加大排序緩沖區(qū)\x0d\x0a alter session set sort_area_size=100000000;\x0d\x0a insert into tableb select * from tablea;\x0d\x0a commit;\x0d\x0a\x0d\x0a如果UPDATE的是索引字段，就會(huì)涉及到索引的重建，暫停索引不會(huì)提高多少的速度，反而有可能降低UPDATE速度，\x0d\x0a因?yàn)樵诟率撬饕梢蕴岣邤?shù)據(jù)的查詢(xún)速度，重建索引引起的速度降低影響不大。\x0d\x0a\x0d\x0aORACLE優(yōu)化修改參數(shù)最多也只能把性能提高15%，大部分都是SQL語(yǔ)句的優(yōu)化！\x0d\x0a\x0d\x0aupdate總體來(lái)說(shuō)比insert要慢 ：\x0d\x0a幾點(diǎn)建議： \x0d\x0a 1、如果更新的數(shù)據(jù)量接近整個(gè)表，就不應(yīng)該使用index而應(yīng)該采用全表掃描 \x0d\x0a 2、減少不必要的index，因?yàn)閡pdate表通常需要update index \x0d\x0a 3、如果你的服務(wù)器有多個(gè)cpu，采用parellel hint，可以大幅度的提高效率\x0d\x0a 另外，建表的參數(shù)非常重要，對(duì)于更新非常頻繁的表，建議加大PCTFREE的值，以保證數(shù)據(jù)塊中有足夠的空間用于UPDATE， 從而降低CHAINED_ROWS。 \x0d\x0a\x0d\x0a二、 各種批量DML操作：\x0d\x0a（1）、oracle批量拷貝：\x0d\x0aset arraysize 20\x0d\x0a set copycommit 5000\x0d\x0a copy from username/password@oraclename append table_name1\x0d\x0a using select * from table_name2;\x0d\x0a （2）、常規(guī)插入方式：\x0d\x0a insert into t1 select * from t;\x0d\x0a 為了提高速度可以使用下面方法，來(lái)減少插入過(guò)程中產(chǎn)生的日志:\x0d\x0a alter table t1 nologging;\x0d\x0ainsert into t1 select * from t;\x0d\x0acommit;\x0d\x0a (3)、CTAS方式：\x0d\x0a create table t1\x0d\x0aas\x0d\x0aselect * from t;\x0d\x0a為了提高速度可以使用下面方法，來(lái)減少插入過(guò)程中產(chǎn)生的日志，并且可以制定并行度:\x0d\x0acreate table t1 nologging parallel(degree 2) as select * from t;\x0d\x0a （4）、Direct-Path插入：\x0d\x0a insert /*+append*/ into t1 select * from t;\x0d\x0a commit;\x0d\x0a 為了提高速度可以使用下面方法，來(lái)減少插入過(guò)程中產(chǎn)生的日志：\x0d\x0a alter table t1 nologging;\x0d\x0a insert /*+append*/ into t1 select * from t;\x0d\x0a \x0d\x0a Direct-Path插入特點(diǎn)：\x0d\x0a1、 append只在insert ? select ?中起作用，像insert /*+ append */ into t values(?)這類(lèi)的語(yǔ)句是不起作用的。在update、delete操作中，append也不起作用。\x0d\x0a2、 Direct-Path會(huì)使數(shù)據(jù)庫(kù)不記錄直接路徑導(dǎo)入的數(shù)據(jù)的重做日志，會(huì)對(duì)恢復(fù)帶來(lái)麻煩。\x0d\x0a3、 Direct-Path直接在表段的高水位線(xiàn)以上的空白數(shù)據(jù)塊中寫(xiě)數(shù)據(jù)，不會(huì)重用高水位線(xiàn)以下的空間，會(huì)對(duì)空間的使用造成一定的浪費(fèi)，對(duì)查詢(xún)的性能也會(huì)造成一定的影響。而常規(guī)插入會(huì)優(yōu)先考慮使用高水位線(xiàn)之下有空閑空間存在的數(shù)據(jù)塊。因此理論上Direct-Path插入會(huì)比常規(guī)插入速度更快，因?yàn)镈irect-Path直接使用新數(shù)據(jù)塊，而常規(guī)插入要遍歷freelist獲取可用空閑數(shù)據(jù)塊，如果同 nologging 配合，這種速度優(yōu)勢(shì)會(huì)更加明顯。\x0d\x0a4、 以append方式插入記錄后，要執(zhí)行commit，才能對(duì)表進(jìn)行查詢(xún)。否則會(huì)出現(xiàn)錯(cuò)誤：ORA-12838: 無(wú)法在并行模式下修改之后讀/修改對(duì)象。\x0d\x0a5、 用append導(dǎo)入數(shù)據(jù)后，如果沒(méi)有提交或者回滾，在其他會(huì)話(huà)中任何對(duì)該表的DML都會(huì)被阻塞（不會(huì)報(bào)錯(cuò)），但對(duì)該表的查詢(xún)可以正常執(zhí)行。\x0d\x0a6、 在歸檔模式下，要把表設(shè)置為nologging，然后以append方式批量添加記錄，才會(huì)顯著減少redo數(shù)量。在非歸檔模式下，不必設(shè)置表的 nologging屬性，即可減少redo數(shù)量。如果表上有索引，則append方式批量添加記錄，不會(huì)減少索引上產(chǎn)生的redo數(shù)量，索引上的redo 數(shù)量可能比表的redo數(shù)量還要大。\x0d\x0a7、 數(shù)據(jù)直接插入數(shù)據(jù)文件，繞過(guò)buffer cache并且忽略了引用完整性約束。\x0d\x0a8、 不管表是否在nologging 下，只要是 direct insert，就不會(huì)對(duì)數(shù)據(jù)內(nèi)容生成undo。\x0d\x0a9、 Oracle在Direct-Path INSERT 操作末尾，對(duì)具有索引的表執(zhí)行索引維護(hù)，這樣就避免了在drop掉索引后，再rebuild。\x0d\x0a10、 Direct-Path INSERT比常規(guī)的插入需要更多的空間。因?yàn)樗鼘?shù)據(jù)插入在高水位之上。并行插入非分區(qū)表需要更多的空間，因?yàn)樗枰獮槊恳粋€(gè)并行線(xiàn)程創(chuàng)建臨時(shí)段。\x0d\x0a11、 在插入期間，數(shù)據(jù)庫(kù)在表上獲得排他鎖，用戶(hù)不能在表上執(zhí)行并行插入、更新或者刪除操作，并行的索引創(chuàng)建和build也不被允許。但卻可以并行查詢(xún)，但查詢(xún)返回的是插入之前的結(jié)果集。\x0d\x0a （5）、并行DML：\x0d\x0a 如果你的服務(wù)器有多個(gè)cpu，采用parellel hint，可以大幅度的提高效率\x0d\x0a ALTER SESSION ENABLE PARALLEL DML;\x0d\x0a\x0d\x0a INSERT /*+ PARALLEL(tableA, 2) */INTO tableA \x0d\x0a SELECT * FROM tableB;\x0d\x0a\x0d\x0a 為了提高速度可以使用下面方法，來(lái)減少插入過(guò)程中產(chǎn)生的日志：\x0d\x0a\x0d\x0a INSERT /*+ PARALLEL(tableA, 2) */INTO tableA NOLOGGING\x0d\x0a SELECT * FROM tableB;\x0d\x0a\x0d\x0aoracle默認(rèn)并不會(huì)打開(kāi)PDML，對(duì)DML語(yǔ)句必須手工啟用。即需要執(zhí)行\(zhòng)x0d\x0aalter table enable parallel dml命令。\x0d\x0a \x0d\x0a并行DML特點(diǎn)：\x0d\x0a1、在并行DML模式中，默認(rèn)的就是DIRECT-PATH插入，為了運(yùn)行并行DML模式，必須滿(mǎn)足以下條件：\x0d\x0aa、必須是Oracle企業(yè)版；\x0d\x0ab、必須在session中使并行DML生效，執(zhí)行以下sql語(yǔ)句：\x0d\x0aALTER SESSION { ENABLE | FORCE } PARALLEL DML;\x0d\x0ac、必須指定table的并行屬性，在創(chuàng)建的時(shí)候或者其他時(shí)候，或者在insert操作時(shí)使用“PARALLEL”提示。\x0d\x0ad、為了使Direct-Path Insert模式失效，在INSERT語(yǔ)句中指定“NOAPPEND”提示，覆蓋并行DML模式。\x0d\x0a 2、并行Direct-Path INSERT到分區(qū)表：\x0d\x0a 類(lèi)似于serial Direct-Path INSERT，每個(gè)并行操作分配給一個(gè)或者多個(gè)分區(qū)，每個(gè)并行操作插入數(shù)據(jù)到各自的分區(qū)段的高水位標(biāo)志之上，commit之后，用戶(hù)就能看到更新的數(shù)據(jù)。\x0d\x0a 3、并行Direct-Path INSERT到非分區(qū)表：\x0d\x0a 每個(gè)并行執(zhí)行分配一個(gè)新的臨時(shí)段，并插入數(shù)據(jù)到臨時(shí)段。當(dāng)commit運(yùn)行后，并行執(zhí)行協(xié)調(diào)者合并新的臨時(shí)段到主表段，用戶(hù)就能看到更新的數(shù)據(jù)。\x0d\x0a 4、Direct-Path INSERT可以使用Log或者不使用Log。\x0d\x0a 5、另外不得不說(shuō)的是，并行不是一個(gè)可擴(kuò)展的特性，只有在數(shù)據(jù)倉(cāng)庫(kù)或作為DBA等少數(shù)人的工具在批量數(shù)據(jù)操作時(shí)利于充分利用資源，而在OLTP環(huán)境下使用并行需要非常謹(jǐn)慎。事實(shí)上PDML還是有比較多的限制的，例如不支持觸發(fā)器，引用約束，高級(jí)復(fù)制和分布式事務(wù)等特性，同時(shí)也會(huì)帶來(lái)額外的空間占用，PDDL同 樣是如此。

如何提高oracle 插入效率

Oracle 數(shù)據(jù)導(dǎo)入方法比較 每個(gè)數(shù)據(jù)庫(kù)管理員都會(huì)面臨數(shù)據(jù)導(dǎo)入的問(wèn)題，這有可能發(fā)生在數(shù)據(jù)庫(kù)的新老移植過(guò)程中，或者是在數(shù)據(jù)庫(kù)崩潰后的恢復(fù)重建過(guò)程中，還有可能是在創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)的模擬環(huán)境過(guò)程中，總之作為一名合格的數(shù)據(jù)庫(kù)管理員，你應(yīng)該做好接受各種數(shù)據(jù)導(dǎo)入請(qǐng)求的技術(shù)儲(chǔ)備，同時(shí)還要盡量滿(mǎn)足人本能的對(duì)導(dǎo)入速度的苛求。本文僅針對(duì) Oracle 數(shù)據(jù)庫(kù)所提供的加速數(shù)據(jù)導(dǎo)入的各種特性和技術(shù)進(jìn)行探討，其中的一些方法也可以轉(zhuǎn)化應(yīng)用于其他數(shù)據(jù)庫(kù)。以下七種數(shù)據(jù)導(dǎo)入方法哪個(gè)最適用需要針對(duì)具體情況具體分析，我也附帶列舉了影響導(dǎo)入速度的各種因素供斟酌。為了比較各種數(shù)據(jù)導(dǎo)入方法的效果，我創(chuàng)建了示例表和數(shù)據(jù)集，并用各種方法導(dǎo)入示例數(shù)據(jù)集來(lái)計(jì)算總體導(dǎo)入時(shí)間和導(dǎo)入進(jìn)程占用 CPU 時(shí)間，這里得出的時(shí)間僅供參考。需要說(shuō)明的是，建議你使用 Oracle 9i 企業(yè)版數(shù)據(jù)庫(kù)，當(dāng)然你也可以嘗試使用 Oracle 7.3 以上的標(biāo)準(zhǔn)版數(shù)據(jù)庫(kù)。本文使用的機(jī)器配置為：CPU Intel P4，內(nèi)存 256M，數(shù)據(jù)庫(kù) Oracle 9i 企業(yè)版

示例表結(jié)構(gòu)和數(shù)據(jù)集

為了演示和比較各種數(shù)據(jù)導(dǎo)入方法，我假定數(shù)據(jù)導(dǎo)入任務(wù)是將外部文件數(shù)據(jù)導(dǎo)入到 Oracle 數(shù)據(jù)庫(kù)的CALLS表中，外部數(shù)據(jù)文件包含十萬(wàn)條呼叫中心記錄，將近 6MB 的文件大小，具體的數(shù)據(jù)示例如下:

82302284384,2003-04-18:13:18:58,5001,投訴,手機(jī)三包維修質(zhì)量82302284385,2003-04-18:13:18:59,3352,咨詢(xún),供水熱線(xiàn)的號(hào)碼82302284386,2003-04-18:13:19:01,3142,建議,增設(shè)公交線(xiàn)路

接受導(dǎo)入數(shù)據(jù)的表名是 CALLS，表結(jié)構(gòu)如下：

Name Null? Type Comment ------------ --------- ------------- ----------------- CALL_ID NOT NULL NUMBER Primary key CALL_DATE NOT NULL DATE Non-unique index EMP_ID NOT NULL NUMBER CALL_TYPE NOT NULL VARCHAR2(12) DETAILS VARCHAR2(25)

逐條數(shù)據(jù)插入INSERT

數(shù)據(jù)導(dǎo)入的最簡(jiǎn)單方法就是編寫(xiě) INSERT 語(yǔ)句，將數(shù)據(jù)逐條插入數(shù)據(jù)庫(kù)。這種方法只適合導(dǎo)入少量數(shù)據(jù)，如 SQL*Plus 腳本創(chuàng)建某個(gè)表的種子數(shù)據(jù)。該方法的最大缺點(diǎn)就是導(dǎo)入速度緩慢，占用了大量的 CPU 處理時(shí)間，不適合大批量數(shù)據(jù)的導(dǎo)入；而其主要優(yōu)點(diǎn)就是導(dǎo)入構(gòu)思簡(jiǎn)單又有修改完善的彈性，不需要多做其它的準(zhǔn)備就可以使用。如果你有很多時(shí)間沒(méi)法打發(fā)，又想折磨一下數(shù)據(jù)庫(kù)和 CPU，那這種方法正適合你。:)

為了與其它方法做比較，現(xiàn)將十萬(wàn)條記錄通過(guò)此方法導(dǎo)入到 CALLS 表中，總共消耗 172 秒，其中導(dǎo)入進(jìn)程占用 CPU 時(shí)間為 52 秒。

逐條數(shù)據(jù)插入 INSERT，表暫無(wú)索引

為什么上一種方法占用了較多的 CPU 處理時(shí)間，關(guān)鍵是 CALLS 表中已創(chuàng)建了索引，當(dāng)一條數(shù)據(jù)插入到表中時(shí)，Oracle 需要判別新數(shù)據(jù)與老數(shù)據(jù)在索引方面是否有沖突，同時(shí)要更新表中的所有索引，重復(fù)更新索引會(huì)消耗一定的時(shí)間。因此提高導(dǎo)入速度的好辦法就是在創(chuàng)建表時(shí)先不創(chuàng)建索引或者在導(dǎo)入數(shù)據(jù)之前刪除所有索引，在外部文件數(shù)據(jù)逐條插入到表中后再統(tǒng)一創(chuàng)建表的索引。這樣導(dǎo)入速度會(huì)提高，同時(shí)創(chuàng)建的索引也很緊湊而有效，這一原則同樣適用于位圖索引（Bitmap Index）。對(duì)于主要的和唯一的關(guān)鍵約束(key constraints)，可以使之先暫時(shí)失效(disabling)或者刪除約束來(lái)獲得同樣的效果，當(dāng)然這些做法會(huì)對(duì)已經(jīng)存在的表的外鍵約束產(chǎn)生相關(guān)的影響，在刪除前需要通盤(pán)斟酌。

需要說(shuō)明的是，這種方法在表中已存在很多數(shù)據(jù)的情況下不太合適。例如表中已有九千萬(wàn)條數(shù)據(jù)，而此時(shí)需要追加插入一千萬(wàn)條數(shù)據(jù)，實(shí)際導(dǎo)入數(shù)據(jù)節(jié)省的時(shí)間將會(huì)被重新創(chuàng)建一億條數(shù)據(jù)的索引所消耗殆盡，這是我們不希望得到的結(jié)果。但是，如果要導(dǎo)入數(shù)據(jù)的表是空的或?qū)氲臄?shù)據(jù)量比已有的數(shù)據(jù)量要大得多，那么導(dǎo)入數(shù)據(jù)節(jié)省的時(shí)間將會(huì)少量用于重新創(chuàng)建索引，這時(shí)該方法才可以考慮使用。

加快索引創(chuàng)建是另一個(gè)需要考慮的問(wèn)題。為了減少索引創(chuàng)建中排序的工作時(shí)間，可以在當(dāng)前會(huì)話(huà)中增加 SORT_AREA_SIZE 參數(shù)的大小，該參數(shù)允許當(dāng)前會(huì)話(huà)在內(nèi)存的索引創(chuàng)建過(guò)程中執(zhí)行更多的排序操作。同樣還可以使用 NOLOGGING 關(guān)鍵字來(lái)減少因創(chuàng)建索引而生成的 REDO 日志量，NOLOGGING 關(guān)鍵字會(huì)對(duì)數(shù)據(jù)庫(kù)的恢復(fù)和 Standby 備用數(shù)據(jù)庫(kù)產(chǎn)生明顯的影響，所以在使用之前要仔細(xì)斟酌，到底是速度優(yōu)先還是穩(wěn)定優(yōu)先。

運(yùn)用這種方法，先刪除 CALLS 表的主鍵和不唯一的索引，然后逐條導(dǎo)入數(shù)據(jù)，完成后重新創(chuàng)建索引( 表在導(dǎo)入數(shù)據(jù)前是空的)。該方法總共消耗 130 秒，包括重建索引的時(shí)間，其中導(dǎo)入進(jìn)程占用 CPU 時(shí)間為 35秒。

這種方法的優(yōu)點(diǎn)是可以加快導(dǎo)入的速度并使索引更加緊湊有效；缺點(diǎn)是缺乏通用性，當(dāng)你對(duì)表增加新的復(fù)雜的模式元素（索引、外鍵等）時(shí)你需要添加代碼、修改導(dǎo)入執(zhí)行程序。另外針對(duì) 7*24 在線(xiàn)要求的數(shù)據(jù)庫(kù)在線(xiàn)導(dǎo)入操作時(shí)，刪除表的索引會(huì)對(duì)在線(xiàn)用戶(hù)的查詢(xún)有很大的性能影響，同時(shí)也要考慮，主要或唯一的關(guān)鍵約束條件的刪除或失效可能會(huì)影響到引用它們的外鍵的使用。

批量插入，表暫無(wú)索引

在Oracle V6 中 OCI 編程接口加入了數(shù)組接口特性。數(shù)組操作允許導(dǎo)入程序讀取外部文件數(shù)據(jù)并解析后，向數(shù)據(jù)庫(kù)提交SQL語(yǔ)句，批量插入 SQL 語(yǔ)句檢索出的數(shù)據(jù)。Oracle 僅需要執(zhí)行一次 SQL 語(yǔ)句，然后在內(nèi)存中批量解析提供的數(shù)據(jù)。批量導(dǎo)入操作比逐行插入重復(fù)操作更有效率，這是因?yàn)橹恍枰淮谓馕?SQL 語(yǔ)句，一些數(shù)據(jù)綁訂操作以及程序與數(shù)據(jù)庫(kù)之間來(lái)回的操作都顯著減少，而且數(shù)據(jù)庫(kù)對(duì)每一條數(shù)據(jù)的操作都是重復(fù)可知的，這給數(shù)據(jù)庫(kù)提供了優(yōu)化執(zhí)行的可能。其優(yōu)點(diǎn)是數(shù)據(jù)導(dǎo)入的總體時(shí)間明顯減少，特別是進(jìn)程占用 CPU 的時(shí)間。

需要提醒的是，通過(guò) OCI 接口確實(shí)可以執(zhí)行數(shù)據(jù)批量導(dǎo)入操作，但是許多工具和腳本語(yǔ)言卻不支持使用此功能。如果要使用該方法，需要研究你所使用的開(kāi)發(fā)工具是否支持 OCI 批量操作功能。導(dǎo)入程序需要進(jìn)行復(fù)雜的編碼并可能存在錯(cuò)誤的風(fēng)險(xiǎn)，缺乏一定的彈性。

運(yùn)用上述方法，程序?qū)⑼獠繑?shù)據(jù)提取到內(nèi)存中的數(shù)組里，并執(zhí)行批量插入操作（100行/次），保留了表的刪除/重建索引操作，總的導(dǎo)入時(shí)間下降到 14 秒，而進(jìn)程占用 CPU 的時(shí)間下降到7秒，可見(jiàn)實(shí)際導(dǎo)入數(shù)據(jù)所花費(fèi)的時(shí)間顯著下降了 95%。

CREATE TABLE AS SELECT，使用Oracle9i的External Table

Oracle 9i 的一項(xiàng)新特性就是 External Table，它就象通常的數(shù)據(jù)庫(kù)表一樣，擁有字段和數(shù)據(jù)類(lèi)型約束，并且可以查詢(xún)，但是表中的數(shù)據(jù)卻不存儲(chǔ)在數(shù)據(jù)庫(kù)中，而是在與數(shù)據(jù)庫(kù)相關(guān)聯(lián)的普通外部文件里。當(dāng)你查詢(xún) External Table 時(shí)，Oracle 將解析該文件并返回符合條件的數(shù)據(jù)，就象該數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)表中一樣。

需要注意的是，你可以在查詢(xún)語(yǔ)句中將 External Table 與數(shù)據(jù)庫(kù)中其他表進(jìn)行連接（Join），但是不能給 External Table 加上索引，并且不能插入/更新/刪除數(shù)據(jù)，畢竟它不是真正的數(shù)據(jù)庫(kù)表。另外，如果與數(shù)據(jù)庫(kù)相關(guān)聯(lián)的外部文件被改變或者被刪除，這會(huì)影響到 External Table 返回查詢(xún)結(jié)果，所以在變動(dòng)前要先跟數(shù)據(jù)庫(kù)打招呼。

這種方法為導(dǎo)入數(shù)據(jù)打開(kāi)了新的一扇門(mén)。你可以很容易的將外部文件與數(shù)據(jù)庫(kù)相關(guān)聯(lián)，并且在數(shù)據(jù)庫(kù)中創(chuàng)建對(duì)應(yīng)的 External Table，然后就可以立即查詢(xún)數(shù)據(jù)，就象外部數(shù)據(jù)已經(jīng)導(dǎo)入到數(shù)據(jù)庫(kù)表中一樣。唯一的不足需要明確，數(shù)據(jù)并未真正導(dǎo)入到數(shù)據(jù)庫(kù)中，當(dāng)外部文件被刪除或覆蓋時(shí)，數(shù)據(jù)庫(kù)將不能訪問(wèn) External Table 里的數(shù)據(jù)，而且索引沒(méi)有被創(chuàng)建，訪問(wèn)數(shù)據(jù)速度將有所緩慢。創(chuàng)建 CALLS_EXTERNAL（External Table表）如下，使之與外部數(shù)據(jù)文件關(guān)聯(lián)：

CREATE TABLE calls_external (call_id NUMBER, call_date DATE, emp_id NUMBER, call_type VARCHAR2(12), details VARCHAR2(25)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY extract_files_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (call_id, call_date CHAR DATE_FORMAT DATE MASK "yyy-mm-dd:hh24:mi:ss", emp_id, call_type, details ) ) LOCATION ('calls.dat') );

然后將 External Table 與真正被使用的表 CALLS 關(guān)聯(lián)同步，刪除 CALLS 表并重建它：

CREATE TABLE calls ( call_id NUMBER NOT NULL, call_date DATE NOT NULL, emp_id NUMBER NOT NULL, call_type VARCHAR2(12) NOT NULL, details VARCHAR2(25) ) TABLESPACE tbs1 NOLOGGING AS SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;

因?yàn)?CALLS 表是真正的數(shù)據(jù)庫(kù)表，可以創(chuàng)建索引來(lái)加快訪問(wèn)，表中的數(shù)據(jù)將被保留，即使外部數(shù)據(jù)文件被更新或被刪除。在建表語(yǔ)句中NOLOGGING關(guān)鍵字用于加快索引重建。

運(yùn)用這種方法導(dǎo)入數(shù)據(jù)，總的導(dǎo)入時(shí)間為 15 秒，進(jìn)程占用 CPU 的時(shí)間為8秒，這比前一種方法稍微慢些，但不能就此認(rèn)為使用 External Table 導(dǎo)入數(shù)據(jù)一定比 OCI 批量插入慢。

這種方法的優(yōu)點(diǎn)是，未經(jīng)進(jìn)行大量的編寫(xiě)代碼就取得了不錯(cuò)的結(jié)果，不象 OCI 批量插入存在編碼錯(cuò)誤風(fēng)險(xiǎn)，它還可以使用 dbms_job 包調(diào)度數(shù)據(jù)導(dǎo)入進(jìn)程，實(shí)現(xiàn)數(shù)據(jù)導(dǎo)入的自動(dòng)化。其缺點(diǎn)是目標(biāo)表必須先刪除后重建，如果只需要導(dǎo)入增量數(shù)據(jù)時(shí)此方法就不合適了，另外用戶(hù)在表的重建過(guò)程中訪問(wèn)數(shù)據(jù)時(shí)會(huì)遇到 "table or view does not exist" 的錯(cuò)誤，它僅適用于 Oracle 9i 以上版本的數(shù)據(jù)庫(kù)。

INSERT Append as SELECT，使用 Oracle9i 的 External Table

上一種方法演示了如何創(chuàng)建與外部數(shù)據(jù)文件關(guān)聯(lián)的數(shù)據(jù)庫(kù)表，其表的數(shù)據(jù)是由外部數(shù)據(jù)文件映射過(guò)來(lái)。缺點(diǎn)是數(shù)據(jù)庫(kù)表需要被先刪除再重建來(lái)保持與外部數(shù)據(jù)文件的一致和同步，對(duì)導(dǎo)入增量的數(shù)據(jù)而不需要?jiǎng)h除已有數(shù)據(jù)的情況不合適。針對(duì)這種需求，Oracle 提供了 INSERT 語(yǔ)句外帶 APPEND 提示來(lái)滿(mǎn)足。

INSERT /*+ APPEND */ INTO calls (call_id, call_date, emp_id, call_type, details) SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;

該語(yǔ)句讀取引用外部數(shù)據(jù)文件的 CALLS_EXTERNAL 表中內(nèi)容，并將之增加到表 CALLS 中。Append 提示告訴 Oracle 使用快速機(jī)制來(lái)插入數(shù)據(jù)，同時(shí)可以配合使用表的 NOLOGGING 關(guān)鍵字。

可以預(yù)見(jiàn)這種方法與前一方法消耗了相同的時(shí)間，畢竟它們是使用 External Table 特性導(dǎo)入數(shù)據(jù)的不同階段解決方法。如果目標(biāo)表不是空的，那將會(huì)消耗稍微長(zhǎng)的時(shí)間（因?yàn)橐亟ǜL(zhǎng)的索引），而前一 CREATE TABLE as SELECT 方法是整體創(chuàng)建索引。

SQL*Loader的強(qiáng)大功能

SQL*Loader 是 Oracle 提供的導(dǎo)入實(shí)用程序，特別針對(duì)從外部文件導(dǎo)入大批量數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)表。該工具已經(jīng)有多年的歷史，每一次版本升級(jí)都使其更加強(qiáng)大、靈活和快捷，但遺憾的是它的語(yǔ)法卻是神秘而不直觀，并且只能從命令行窗口處進(jìn)行調(diào)用。

盡管它有不直觀的缺點(diǎn)，但卻是最快最有效的導(dǎo)入數(shù)據(jù)方法。缺省情況下它使用 "conventional path" 常規(guī)選項(xiàng)來(lái)批量導(dǎo)入數(shù)據(jù)，其性能提高度并不明顯。我建議使用更快速的導(dǎo)入?yún)?shù)選項(xiàng)，在命令行添加"direct=true" 選項(xiàng)調(diào)用 "direct path" 導(dǎo)入選項(xiàng)。在 "direct path" 導(dǎo)入實(shí)現(xiàn)中，程序在數(shù)據(jù)庫(kù)表的新數(shù)據(jù)塊的 high water mark 處直接寫(xiě)入導(dǎo)入數(shù)據(jù)，縮短了數(shù)據(jù)插入的處理時(shí)間，同時(shí)優(yōu)化使用了非常有效的B+二叉樹(shù)方法來(lái)更新表的索引。

運(yùn)用這種方法，如果使用缺省的 conventional path 導(dǎo)入選項(xiàng)，總的導(dǎo)入時(shí)間是 81 秒，進(jìn)程占用 CPU 時(shí)間大約是 12 秒，這包括了更新表的索引時(shí)間。如果使用 direct path 導(dǎo)入選項(xiàng)，總的導(dǎo)入時(shí)間竟是 9 秒，進(jìn)程占用 CPU 時(shí)間也僅僅是 3 秒，也包括了更新表的索引時(shí)間。

由此可見(jiàn)，盡管表中的索引在數(shù)據(jù)導(dǎo)入之前并沒(méi)有被刪除，使用SQL*Loader的direct path 導(dǎo)入選項(xiàng)仍然是快速和有效的。當(dāng)然它也有缺點(diǎn)，就像NOLOGGING關(guān)鍵字一樣該方法不生成REDO日志數(shù)據(jù)，導(dǎo)入進(jìn)程出錯(cuò)后將無(wú)法恢復(fù)到先前狀態(tài)；在數(shù)據(jù)導(dǎo)入過(guò)程中表的索引是不起作用的，用戶(hù)此時(shí)訪問(wèn)該表時(shí)將出現(xiàn)遲緩，當(dāng)然在數(shù)據(jù)導(dǎo)入的過(guò)程中最好不要讓用戶(hù)訪問(wèn)表。

分區(qū)交換 (Partition Exchange)

以上討論的數(shù)據(jù)導(dǎo)入方法都有一個(gè)限制，就是要求用戶(hù)在導(dǎo)入數(shù)據(jù)完成之后才可以訪問(wèn)數(shù)據(jù)庫(kù)表。面對(duì)7×24不間斷訪問(wèn)數(shù)據(jù)庫(kù)來(lái)說(shuō)，如果我們只是導(dǎo)入需要增加的數(shù)據(jù)時(shí)，這種限制將對(duì)用戶(hù)的實(shí)時(shí)訪問(wèn)產(chǎn)生影響。Oracle在這方面提供了表分區(qū)功能，它可以減少導(dǎo)入數(shù)據(jù)操作對(duì)用戶(hù)實(shí)時(shí)訪問(wèn)數(shù)據(jù)的影響，操作模式就象使用可熱插拔的硬盤(pán)一樣，只不過(guò)這里的硬盤(pán)換成了分區(qū)（Partition）而已。需要聲明的是 Partitioning 分區(qū)功能只有在企業(yè)版數(shù)據(jù)庫(kù)中才提供。

在一個(gè)被分區(qū)過(guò)的表中，呈現(xiàn)給用戶(hù)的表是多個(gè)分區(qū)段（segments）的集合。分區(qū)可以在需要時(shí)被添加，在維護(hù)時(shí)被卸載或刪除，分區(qū)表可以和數(shù)據(jù)庫(kù)中的表交換數(shù)據(jù)，只要它們的表結(jié)構(gòu)和字段類(lèi)型是一致的，交換后的分區(qū)表將擁有與之互動(dòng)的表的數(shù)據(jù)。需要注意的是，這種交換只是在Oracle數(shù)據(jù)庫(kù)的數(shù)據(jù)字典層面上進(jìn)行，并沒(méi)有數(shù)據(jù)被實(shí)際移動(dòng)，所以分區(qū)表交換是極其快速的。

為了創(chuàng)建實(shí)驗(yàn)環(huán)境，先假設(shè)CALLS表是個(gè)分區(qū)表，要?jiǎng)?chuàng)建一個(gè)空的分區(qū)PART_01012004，用來(lái)保存2004年1月1日的呼叫數(shù)據(jù)。然后需要再創(chuàng)建一臨時(shí)表為CALLS_TEMP，該表與CALLS表?yè)碛邢嗤淖侄魏蛿?shù)據(jù)類(lèi)型。

我們使用先前介紹的導(dǎo)入方法將十萬(wàn)條數(shù)據(jù)導(dǎo)入到CALLS_TEMP表中，可以耐心等待數(shù)據(jù)完全導(dǎo)入到CALLS_TEMP表中，并且創(chuàng)建好索引和相關(guān)約束條件，所有這一切操作并不影響用戶(hù)實(shí)時(shí)訪問(wèn)CALLS表，因?yàn)槲覀冎粚?duì)CALLS_TEMP臨時(shí)表進(jìn)行了操作。一旦數(shù)據(jù)導(dǎo)入完成，CALLS_TEMP表就存有2004年1月1日的呼叫數(shù)據(jù)。同時(shí)利用CALLS表中名為PART_01012004的空分區(qū)，使用如下語(yǔ)句執(zhí)行分區(qū)交換：

ALTER TABLE calls EXCHANGE PARTITION part_01012004 WITH TABLE calls_temp INCLUDING INDEXES WITHOUT VALIDATION;

分區(qū)交換操作將非常快速地只更新CALLS表的數(shù)據(jù)字典，PART_01012004分區(qū)表即刻擁有CALLS_TEMP表的所有數(shù)據(jù)，而CALLS_TEMP表變?yōu)榭毡?。假定CALLS表使用局部索引而非全局索引，上述語(yǔ)句中的INCLUDING INDEXES將保證分區(qū)交換包括索引的可用性，WITHOUT VALIDATION 指明不檢查交替表中數(shù)據(jù)的匹配，加快了交換的速度。

結(jié)論

以上探討了Oracle數(shù)據(jù)庫(kù)的多種數(shù)據(jù)導(dǎo)入方法，每種方法都有其優(yōu)缺點(diǎn)和適用環(huán)境，能夠滿(mǎn)足你不同的導(dǎo)入需求，當(dāng)然你需要在了解了這些方法后，在速度、簡(jiǎn)易性、靈活性、可恢復(fù)性和數(shù)據(jù)可用性之間尋求最佳導(dǎo)入方案。

為了對(duì)比各種方法的效果，我們創(chuàng)建了一個(gè)實(shí)例來(lái)展示各種方法的導(dǎo)入效率和效果，從中你可以選擇最適合的方法用于今后的數(shù)據(jù)導(dǎo)入工作。同時(shí)請(qǐng)記住，本文并未囊括所有的ORACLE數(shù)據(jù)導(dǎo)入技術(shù)（比如并行數(shù)據(jù)導(dǎo)入技術(shù)），這需要我們繼續(xù)不懈的探索和嘗試。

如何提升Oracle數(shù)據(jù)庫(kù)搜索效率

需要用索引來(lái)解決，索引的創(chuàng)建規(guī)則如下：

1、表的

主鍵

、

外鍵

必須有索引；

2、數(shù)據(jù)量超過(guò)300的表應(yīng)該有索引；

3、經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；

4、經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；

5、索引應(yīng)該建在選擇性高的字段上；

6、索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引；

7、復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：

A、

正確選擇

復(fù)合索引中的主列字段，一般是選擇性較好的字段；

B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢(xún)是否極少甚至沒(méi)有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；

C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引；

D、如果復(fù)合索引所包含的字段超過(guò)3個(gè)，那么仔細(xì)考慮其必要性，考慮減少?gòu)?fù)合的字段；

E、如果既有單字段索引，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；

8、頻繁進(jìn)行

數(shù)據(jù)操作

的表，不要建立太多的索引；

9、刪除無(wú)用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響；

以上是一些普遍的建立索引時(shí)的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過(guò)仔細(xì)分析，要有建立的依據(jù)。因?yàn)樘嗟乃饕c不充分、不正確的索引對(duì)性能都毫無(wú)益處：在表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)，索引對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開(kāi)銷(xiāo)。另外，過(guò)多的復(fù)合索引，在有單字段索引的情況下，一般都是沒(méi)有

存在價(jià)值

的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，特別是對(duì)頻繁更新的表來(lái)說(shuō)，負(fù)面影響更大。

如何提高oracle模糊查詢(xún)的性能？

1、使用兩邊加‘%’號(hào)的查詢(xún)，Oracle是不通過(guò)索引的，所以查詢(xún)效率很低。

例如：select count(*) from lui_user_base t where t.user_name like '%cs%';

2、like '...%'和 like'%...'雖然走了索引，但是效率依然很低。

3、有人說(shuō)使用如下sql，他的效率提高了10倍，但是數(shù)據(jù)量小的時(shí)候

select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')

我拿100w跳數(shù)據(jù)做了測(cè)試，效果一般，依然很慢，原因：

select rowid from lui_user_base t where t.user_name like '%cs%' ? 這條sql執(zhí)行很快，那是相當(dāng)?shù)目欤欠诺絪elect count(*) from lui_user_base where rowid in（）里后，效率就會(huì)變的很慢了。

4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0

這種查詢(xún)效果很好，速度很快，推薦使用這種。因?yàn)槲覍?duì)oracle內(nèi)部機(jī)制不是很懂，只是對(duì)結(jié)果做了一個(gè)說(shuō)明。

5、有人說(shuō)了用全文索引，我看了，步驟挺麻煩，但是是個(gè)不錯(cuò)的方法，留著備用：

對(duì)cmng_custominfo 表中的address字段做全文檢索：

1，在oracle9201中需要?jiǎng)?chuàng)建一個(gè)分詞的東西：

BEGIN

ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');

--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用

end;

2，創(chuàng)建全文檢索：

CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');

3，查詢(xún)時(shí)候，使用：

select * from cmng_custominfo where contains (address, '金色新城')1;

4，需要定期進(jìn)行同步和優(yōu)化：

同步：根據(jù)新增記錄的文本內(nèi)容更新全文搜索的索引。

begin

ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');

end;

優(yōu)化：根據(jù)被刪除記錄清除全文搜索索引中的垃圾

begin

ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');

end;

5，采用job做步驟4中的工作：

1)該功能需要利用oracle的JOB功能來(lái)完成

因?yàn)閛racle9I默認(rèn)不啟用JOB功能，所以首先需要增加ORACLE數(shù)據(jù)庫(kù)實(shí)例的JOB配置參數(shù):

job_queue_processes=5

重新啟動(dòng)oracle數(shù)據(jù)庫(kù)服務(wù)和listener服務(wù)。

2)同步 和 優(yōu)化

--同步 sync:

variable jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',

SYSDATE, 'SYSDATE + (1/24/4)');

commit;

END;

--優(yōu)化

variable jobno number;

begin

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');

commit;

END;

其中， 第一個(gè)job的SYSDATE + (1/24/4)是指每隔15分鐘同步一次，第二個(gè)job的SYSDATE + 1是每隔1天做一次全優(yōu)化。具體的時(shí)間間隔，可以根據(jù)應(yīng)用的需要而定。

6，索引重建

重建索引會(huì)刪除原來(lái)的索引，重新生成索引，需要較長(zhǎng)的時(shí)間。

重建索引語(yǔ)法如下：

ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

據(jù)網(wǎng)上一些用家的體會(huì)，oracle重建索引的速度也是比較快的，有一用家這樣描述：

Oracle 的全文檢索建立和維護(hù)索引要比ms sql server都要快得多，筆者的65萬(wàn)記錄的一個(gè)表建立索引只需要20分鐘，同步一次只需要1分鐘。

因此，也可以考慮用job的辦法定期重建索引。