12、Oracle中的其它資料庫物件

画个一样的我發表於2024-07-14
Oracle資料庫物件

最近專案要用到Oracle，奈何之前沒有使用過，所以在B站上面找了一個學習影片，用於記錄學習過程以及自己的思考。
影片連結：
【尚矽谷】Oracle資料庫全套教程，oracle從安裝到實戰應用
如果有侵權，請聯絡刪除，謝謝。

學習目標：

  • 建立、維護和使用序列

  • 建立和維護索引

  • 建立同義詞

1、什麼是序列?

**序列: **可供多個使用者用來產生唯一數值的資料庫物件

  • 自動提供唯一的數值
  • 共享物件
  • 主要用於提供主鍵值
  • 將序列值裝入記憶體可以提高訪問效率

2、序列基礎操作

2.1、CREATE SEQUENCE 語句

注意：Oracle中並沒有自動遞增的功能，想要實現此功能，一種方式是藉助 序列 + 觸發器 來完成。

定義序列:

CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增長的數值
       [START WITH n]    --從哪個值開始
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要迴圈
       [{CACHE n | NOCACHE}];  --是否快取登入

建立序列示例：

  • 建立序列 DEPT_DEPTID_SEQ為表 DEPARTMENTS 提供主鍵
  • 不使用 CYCLE 選項
CREATE SEQUENCE dept_deptid_seq
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;
Sequence created.

-- 建立 觸發器

CREATE OR REPLACE
TRIGGER EMP_ID_TRIGGER BEFORE
INSERT
	ON
	EMP REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
		SELECT
	EMP_SEQ.nextval
INTO
	:new.ID
FROM
	dual;
END;


CREATE OR REPLACE
TRIGGER EMP_ID_TRIGGER BEFORE
INSERT
	ON
	EMP REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
		SELECT
	EMP_SEQ.nextval
INTO
	:new.ID
FROM
	dual;
END;

2.2、查詢序列

  • 查詢資料字典檢視 USER_SEQUENCES 獲取序列定義資訊
SELECT	sequence_name, min_value, max_value, 
	increment_by, last_number
FROM	user_sequences;


結果：
SEQUENCE_NAME     |MIN_VALUE|MAX_VALUE                   |INCREMENT_BY|LAST_NUMBER|
------------------+---------+----------------------------+------------+-----------+
APWJ06_SEQ        |        1|                  9999999999|           1|          1|
CHAMBERMAPPING_SEQ|        1|                  9999999999|           1|         81|
CVDN02_SEQ        |        1|                  9999999999|           1|          1|
  • 如果指定NOCACHE 選項，則列LAST_NUMBER 顯示序列中下一個有效的值

2.3、NEXTVAL 和 CURRVAL 偽列

  • NEXTVAL 返回序列中下一個有效的值，任何使用者都可以引用
  • CURRVAL 中存放序列的當前值
  • NEXTVAL 應在 CURRVAL 之前指定 ，否則會報CURRVAL 尚未在此會話中定義的錯誤。
SELECT APWJ06_SEQ.nextval,  APWJ06_SEQ.currval FROM dual;

在一個資料庫會話(session)中，必須先呼叫序列(sequence)的 NEXTVAL 方法，才能呼叫 CURRVAL 方法，否則會報錯，提示 CURRVAL 尚未在此會話中定義。

具體解釋如下：

  • NEXTVAL：獲取序列的下一個值，並使序列遞增。每次呼叫 NEXTVAL 時，序列的值都會增加，並返回新的值。
  • CURRVAL：獲取序列在當前會話中的當前值。這個值是上一次呼叫 NEXTVAL 所返回的值。

錯誤的產生原因是，CURRVAL 依賴於 NEXTVAL，即 CURRVAL 返回的是當前會話中上一次呼叫 NEXTVAL 得到的值。如果在當前會話中沒有呼叫過 NEXTVAL，那麼 CURRVAL 無法獲取到任何值，因此會報錯。

例子：

-- 先呼叫 NEXTVAL 獲取序列的下一個值
SELECT my_sequence.NEXTVAL FROM dual;

-- 然後可以呼叫 CURRVAL 獲取當前會話中序列的當前值
SELECT my_sequence.CURRVAL FROM dual;

如果順序顛倒，即先呼叫 CURRVAL 而沒有先呼叫 NEXTVAL，則會出現錯誤：

-- 直接呼叫 CURRVAL 會報錯
SELECT my_sequence.CURRVAL FROM dual;
-- 錯誤提示：CURRVAL 尚未在此會話中定義

因此，要正確使用 CURRVAL，必須確保在同一會話中先呼叫過 NEXTVAL。

2.4、序列應用舉例

INSERT INTO departments(department_id, 
            department_name, location_id)
VALUES      (dept_deptid_seq.NEXTVAL, 
            'Support', 2500);
1 row created.
  • 序列 DEPT_DEPTID_SEQ 的當前值
SELECT	dept_deptid_seq.CURRVAL
FROM	dual;

2.5、使用序列

  • 將序列值裝入記憶體可提高訪問效率
  • 序列在下列情況下出現裂縫:
    • 回滾
    • 系統異常
    • 多個表同時使用同一序列
  • 如果不將序列的值裝入記憶體(NOCACHE), 可使用表 USER_SEQUENCES 檢視序列當前的有效值

2.6、修改序列

修改序列的增量, 最大值, 最小值, 迴圈選項, 或是否裝入記憶體

ALTER SEQUENCE dept_deptid_seq
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;
Sequence altered.

2.7、修改序列的注意事項

  • 必須是序列的擁有者或對序列有 ALTER 許可權
  • 只有將來的序列值會被改變
  • 改變序列的初始值只能透過刪除序列之後重建序列的方法實現

2.8、刪除序列

  • 使用 DROP SEQUENCE 語句刪除序列
  • 刪除之後，序列不能再次被引用
DROP SEQUENCE dept_deptid_seq;
Sequence dropped.

3、索 引

索引:

  • 一種獨立於表的模式物件, 可以儲存在與表不同的磁碟或表空間中
  • 索引被刪除或損壞, 不會對錶產生影響, 其影響的只是查詢的速度
  • 索引一旦建立, Oracle 管理系統會對其進行自動維護, 而且由 Oracle 管理系統決定何時使用索引。使用者不用在查詢語句中指定使用哪個索引
  • 在刪除一個表時,所有基於該表的索引會自動被刪除
  • 透過指標加速 Oracle 伺服器的查詢速度
  • 透過快速定位資料的方法，減少磁碟 I/O

3.1、建立索引

**自動建立: **在定義 PRIMARY KEY 或 UNIQUE 約束後系統自動在相應的列上建立唯一性索引
手動建立: 使用者可以在其它列上建立非唯一的索引，以加速查詢

  • 在一個或多個列上建立索引
CREATE INDEX index
ON table (column[, column]...);
  • 在表 EMPLOYEES的列 LAST_NAME 上建立索引
CREATE INDEX 	emp_last_name_idx
ON 		employees(last_name);
Index created.

3.2、什麼時候建立索引

以下情況可以建立索引:

  • 列中資料值分佈範圍很廣（資料區分度大，夠稀疏）
  • 列經常在 WHERE 子句或連線條件中出現
  • 表經常被訪問而且資料量很大 ，訪問的資料大概佔資料總量的2%到4%

3.3、什麼時候不要建立索引

下列情況不要建立索引:

  • 表很小
  • 列不經常作為連線條件或出現在WHERE子句中
  • 查詢的資料大於2%到4%
  • 表經常更新

索引不需要用，只是說我們在用name進行查詢的時候，速度會更快。當然查的速度快了，插入的速度就會慢。因為插入資料的同時，還需要維護一個索引。

3.3、查詢索引

可以使用資料字典檢視 USER_INDEXES 和 USER_IND_COLUMNS 檢視索引的資訊

SELECT	ic.index_name, ic.column_name,
	ic.column_position col_pos,ix.uniqueness
FROM	user_indexes ix, user_ind_columns ic
WHERE	ic.index_name = ix.index_name
AND	ic.table_name = 'EMPLOYEES';


結果：
INDEX_NAME   |COLUMN_NAME|COL_POS|UNIQUENESS|
-------------+-----------+-------+----------+
EMP_EMAIL_UK |EMAIL      |      1|UNIQUE    |
EMP_EMP_ID_PK|EMPLOYEE_ID|      1|UNIQUE    |

3.4、刪除索引

  • 使用DROP INDEX 命令刪除索引
DROP INDEX index;
  • 刪除索引UPPER_LAST_NAME_IDX
DROP INDEX upper_last_name_idx;
Index dropped.
  • 只有索引的擁有者或擁有DROP ANY INDEX 許可權的使用者才可以刪除索引
  • 刪除操作是不可回滾的

4、同義詞-synonym

使用同義詞訪問相同的物件:

  • 方便訪問其它使用者的物件
  • 縮短物件名字的長度
CREATE [PUBLIC] SYNONYM synonym
FOR    object;

示例：

CREATE SYNONYM e FOR employees;

select * from e;

4.1、建立和刪除同義詞

  • 為檢視DEPT_SUM_VU 建立同義詞
CREATE SYNONYM  d_sum
FOR  dept_sum_vu;
Synonym Created.
  • 刪除同義詞
DROP SYNONYM d_sum;
Synonym dropped.

相關文章