感謝各位訂閱者的支持,我們將在近期推出一系列「手把手教你」文章,並在不久的將來,將連續發布「零基礎學習」系列文章,內容將涵蓋SQL基礎,PL/SQL基礎,資料庫體系結構等普及類文章,每個系列都將有10-20篇文章組成,將以連載的方式集中發布,期待您的點閱。
Oracle Database 19c當中包含的新特性眾多,我們今天向大家介紹「自動索引」。索引對大家來說都不陌生,我們創建索引是為了加速查詢,但是大家都知道,索引的維護和存儲往往是一個大問題。有時候我們為不必要的欄位創建了索引,浪費了存儲空間、付出不必要的維護成本,有時候我們因為沒有在應有索引的欄位創建索引而損失了性能。我們之前就在想,什麼時候索引的創建和刪除可以由資料庫自己來完成呢?在19c,這個功能終於實現了。今天就請您和我們一起來體驗自動索引帶給我們的方便與高效。
我們首先創建一個19c環境,今天我們使用Linux為我們的作業系統。在Linux上安裝Oracle Database 19c只需要執行3個腳本即可。您可以先觀看下方3分鐘左右的錄像,體驗一下在19c安裝的方便與快捷。
第一步:執行環境準備腳本
我們在Linux當中安裝之前版本的資料庫安裝時,也會安裝類似的安裝包,在Oracle Enterprise Linux5(以下簡稱OEL)的時候,在圖形界面中就可以選擇這個安裝包,但是在後續的OEL中,有些朋友反映找不到這個包,其實它一直都在,只不過名字會發生變更。
在安裝19c時,您可以像我一樣在線安裝(如果您的Linux可以聯網的話),您也可以來到我公司官網進行下載,我將這個預先環境準備包的rpm文件下載地址放在文末的「閱讀原文」連結中,您可以點擊「閱讀原文」進行下載。
這個安裝包將幫助大家對Linux系統進行配置,創建oracle用戶及修改系統參數等。
第二步:下載安裝包並執行安裝動作
您可以來到我公司官網下載19.3資料庫rpm包,地址如下:https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html
我將下載好的rpm放在/u01下面,然後通過root用戶使用yum的方式進行安裝,安裝大概需要5-10分鐘的時間。
安裝結束後,您將看到如下界面。
按照上面的提示,我們可以使用root用戶執行一個腳本,創建出一個示例資料庫,容器資料庫的名字是ORCLCDB,PDB的名字是ORCLPDB1,如果您不喜歡默認的資料庫配置,您可以通過修改下面紅框中的腳本對資料庫進行客制化。今天我們暫時不對配置文件做修改,直接執行。
資料庫創建的時間大概10分鐘左右,具體時間要看您的硬體環境。
當您看到如下界面,表示資料庫已經創建完畢,下圖中紅框告知您創建的資料庫信息。
為了方便操作,我們設定oracle用戶的.bash_profile,請按照如下方式進行設定。請注意,oracle用戶已經在安裝預先環境rpm的時候為大家創建好,大家從root用戶直接su - oracle就可以轉換過去。
如上圖,我們使用SQL Plus看看資料庫情況。
但是我們創建的資料庫沒有測試數據,我們接下來執行系統自帶腳本,將HR用戶創建出來,並將我們在教學時經常使用的表創建出來。請按照下圖執行腳本,並給出參數,因為參數都很簡單,提示也非常明確,這裡就不再贅述了。
至此,資料庫準備環境就完成了。
第一步:首先我們看看自動所以是否已經啟動,請按照下圖進行操作:
我們通過觀察發現「AUTO_INDEX_MODE」是OFF狀態,說明還沒有啟動自動索引功能。
第二步:啟動自動索引
我們通過DBMS_AUTO_INDEX這個Package中的存儲過程啟動自動索引。
自動索引有三種狀態:
我們可以通過執行如下語句來修改自動索引的狀態:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
我們先將自動索引的狀態設定為IMPLEMENT,自動索引目前只在Exadata上運行的19c可以使用。
然後來到容器資料庫,設定自動索引功能。
執行腳本看看自動索引是否啟動,通過觀察發現已經啟動了。
我們來到PDB看看,首先要打開PDB,因為剛才我們將CDB重啟了,之後沒有執行PDB的open命令。
通過觀察,發現PDB上的自動索引沒有啟動。
那麼我們現在將PDB上的自動索引也啟動起來。
第三步:設定自動索引所使用的表空間
大家知道索引是需要存儲空間的。默認情況下,自動索引將使用系統默認的永久表空間作為自己的存儲空間,如果您覺得這不合適,您可以創建屬於自動索引自己的表空間。我們將/u01/dbf作為數據文件存儲的位置。
然後創建表空間
接下來,通過存儲過程設定我們剛創建好的表空間為自動索引的存儲表空間。
如果您執行了如下語句,將使用系統默認的永久表空間作為自動索引的存儲表空間。
第四步:設定schema的自動索引功能
默認情況下,所有的schemas都將被使用自動索引功能,但如果您想有針對性地啟用自動索引功能,可以通過DBMS_AUTO_INDEX.CONFIGURE這個存儲過程進行配置。我們首先看看在沒有進行特定schema配置之前的自動索引狀態。
通過觀察,在auto_index_schema當中,是空白,沒有特殊設定,表示所有的schema都啟用了自動索引。
我們現在想通過設定,只對HR這個schema啟用自動索引。
可以通過將schema設定為NULL,恢復到初始狀態,即對所有的schemas都啟用自動索引功能。
剛才設定的是:指定某些schemas使用自動索引功能,我們可以將上面的參數修改一下,指定某些schemas不使用自動索引。比如,我們創建一個schema叫做HY,讓這個schema不使用自動索引的功能。
可以通過下面的設定,清除這種「不包含」設定。
關於自動索引的其他設定,比如是否啟用壓縮、自動索引日誌保留時間等,今天就不為大家介紹了,感興趣的朋友可以參考我公司的官方網文檔:https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_INDEX.html
第五步:驗證自動索引功能
我們在ORCLPDB1這個PDB當中的HR schema下面創建一個比較大的表,然後我們通過PL/SQL去反覆查詢這個表中的一個欄位,看看系統是否可以創建出自動索引來。
建表語句如下:
create table hr.big1 as select rownum id,t.* from dba_objects t;
然後我們通過一個PL/SQL反覆去查詢它的ID欄位:
declare
a varchar2(2000) := '';
begin
for x in 1.. 10000 loop
select object_name into a from hr.big1 where id=x;
end loop;
end;
/
您現在可以去做其他的事情,為什麼?因為自動索引的JOB是每15分鐘執行一次。所以,如果剛執行完上面的語句,馬上就去通過下面紅色框的SQL語句去查詢自動索引創建的狀態,可能看不到任何結果。請稍等一會兒,然後再去查詢。
我們在等待的時候,可以看看系統後臺是否有這樣的JOB,通過下面的查詢,我們看到有3個JOB是與自動索引有關的。
通過觀察上面的結果發現,系統自己創建了索引,名字是以SYS_AI開頭的,AI就是自動索引的意思吧。
關於更多的自動索引的信息,您可以查詢Oracle官方文檔。https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-D1285CD5-95C0-4E74-8F26-A02018EA7999
今天的內容就到這裡,感謝您的點閱,謝謝。
掃描下方QR Code即刻預約ADW演示
編輯:殷海英