目的: 創建簡單和複雜視圖 視圖復取數據 創建、維護和使用序列 創建和維護索引 創建私有和公有同義詞 資料庫對象: 視圖【View】 什麼是視圖? 視圖呈現邏輯子集或數據的組合。視圖是基於表或其他視圖的邏輯表。一個視圖不包含自己的數據,而是像一個視窗,通過視窗可以查看或更改表中的數據。視圖所基於的表 ...
目的: 創建簡單和複雜視圖 視圖復取數據 創建、維護和使用序列 創建和維護索引 創建私有和公有同義詞 資料庫對象:
Object | Description |
Table | Basic unit of storage; composed of rows |
View | Logically represents subsets of data from one or more tables |
Sequence | Generates numeric values |
Index | Improves the performance of some queries |
Synonym | Gives alternative names to objects |
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery--子查詢中可包含一個複雜的select查詢。 [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];OR REPLACE :已存在的將進行覆蓋重建。 FORCE :無論基表是否存在,都進行創建。 NOFORCE :使用基表存在的才能創建(預設值) view_name :視圖名稱 alias :別名為視圖查詢選擇的表達式指定名稱(別名數必須與視圖選擇的表達式數量相匹配。) subquery :子查詢是一個完整的SELECT語句(您可以在SELECT列表中使用別名)。 WITH CHECK OPTION :指定僅可以插入或更新視圖可訪問的那些行 WITH READ ONLY :確保在此視圖上不能執行DML操作 視圖上執行DML操作: 簡單視圖可以執行DML操作 含以下條件則不能對視圖執行DML操作 -組函數 -GROUP BY子句 -DISTINCT 關鍵字 -ROWNUM 關鍵字 -列上含有表達式,如(salary * 12) -基表NOT NULL的列未在視圖列表中。 移除視圖:
DROP VIEW view_name;序列【Sequence】 序列是一個生成數值的資料庫對象。可以創建序列,然後用來生成數值。 序列的特征: -能自動產生唯一的數值 -它是共用對象 -能用於主鍵值 -替換應用代碼 -它的值緩存在記憶體中提升訪問效率 序列是用戶創建的資料庫對象,可以由多個用戶共用以生成整數。 您可以定義一個序列來生成唯一的值,或者重覆使用相同的數字。 序列的典型用法是創建一個主鍵值,每個行必須是唯一的。 該序列由內部Oracle常式生成並遞增(或遞減)。 這可以是一個節省時間的對象,因為它可以減少編寫序列生成常式所需的應用程式代碼量。 序列號是獨立存儲和生成的。 因此,相同的序列可以用於多個表格。 創建序列的語法:
CREATE SEQUENCE sequence_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];INCREMENT BY n:指定序列號之間的間隔,其中n是一個整數(如果忽略此值,則序列增加1) START WITH n:指定要生成的第一個序列號(如果省略此值,則序列以1開頭) MAXVALUE n:指定序列可以生成的最大值 NOMAXVALUE:指定遞增序列的最大值10^27,對遞減序列指定-1(預設選項) MINVALUE n:指定最小序列值 NOMINVALUE:為遞增序列指定最小值1,對於遞增序列指定-(10^26)(預設選項) CYCLE | NOCYCLE:指定序列達到最大值或最小值是否繼續生成值(NOCYCLE是預設選項) CACHE| NOCACHE:指定Oracle伺服器預分頻和保留在記憶體中的值(預設情況下,Oracle伺服器緩存20個值)
CREATE SEQUENCE sequence_t INCREMENT BY 1 START WITH 10 MAXVALUE 999 NOCACHE NOCYCLE;序列生成後需要NEXTVAL和 CURRVAL偽列引用序列值 NEXTVAL:偽列用於從指定序列中提取連續的序列號。必須使用序列名稱來限定nextval。當引用sequence.nextval是,會生成一個新的序列號,並將當前序列置於currval。 CURRVAL:偽列用於引用當前用戶剛生成的序列號。必須使用nextval在當前用戶會話生成一個序列號,然後才能引用currval。
16:53:05 SQL> select sequence_t.nextval from dual; NEXTVAL ---------- 10 16:53:17 SQL> select sequence_t.currval from dual; CURRVAL ---------- 10緩存序列的值: 在記憶體中緩存序列,可以快速訪問這些序列值。當第一次參考序列時,將會自動填充緩存。從緩存的序列中檢索下一個序列值,在使用到最後的序列值之後,序列的下一個請求會將另一個序列的緩存拉入記憶體中緩存。 序列的丟失: -連續生成序列的值時,在事務中回滾會導致數值丟失。 -系統的崩潰,導致緩存中的序列丟失。 -多表共用同一序列,會導致序列丟失。 修改序列:
ALTER SEQUENCE sequence_name INCREMENT BY 20 MAXVALUE 99999 NOCACHE NOCYCLE;刪除序列:
DROP SEQUENCE sequence_name;
索引【Index】
索引是資料庫對象,用於提高某些查詢的性能。創建主鍵或唯一性約束時資料庫也會自動創建。
索引:
-它是一個schema對象
-它使用指針關聯每一個行提高訪問速度
-它減少磁碟I/O,快速訪問本地數據
-它依賴表
-資料庫伺服器自動維護和使用它。
Oracle伺服器索引是一個模式對象,可以通過使用指針來加速行的檢索。 索引可以顯式或自動創建。 如果在列上沒有索引,則會發生全表掃描。
索引提供對錶中行的直接和快速訪問。 其目的是通過使用索引路徑快速定位數據來減少磁碟I / O。 該索引由Oracle伺服器自動維護和使用。 索引創建後,用戶不需要直接的活動。
索引在邏輯上和物理上獨立於索引表。 這意味著它們可以隨時創建或刪除,並且不會影響基表或其他索引。
註意:刪除表格時,相應的索引也會被刪除。
索引類型:
唯一性索引:PRIMARY KEY和UNIQUE約束會自動創建唯一性索引。
非唯一性索引:用戶自定義創建索引。
註意:創建唯一性索引時,建議創建一個唯一性約束,因為它會隱式的創建一個唯一性索引。
創建索引語法:
CREATE INDEX schema.index_name ON schema.table_name (column[,column]...);創建索引的條件: -列上的資料跨度大 -列上的資料有大量的空值 -一個或多個列經常在WHERE子句或連接條件中一起使用 -該表很大,大多數查詢預計將檢索少於2%到4%的行 不建議創建索引: -索引列沒有用於SQL語句查詢 -檢索資料大於總資料的2%或4% -該列上有大量的更新操作 -該列查詢被用於表達式 註意:索引越多並不是更好,表上索引越多不會產生更快的查詢。在有索引列的表上進行DML操作意味索引也必須更新。索引依賴表被刪除,索引也會跟著刪除。 刪除索引:
DROP INDEX schema.index_name ;重建索引:
ALTER INDEX schema.index_name REBUILD [PARALLEL n]; 14:40:24 SQL> create index hr.hr_emp_01 on hr.emp_01(salary); 14:40:51 SQL> alter index hr.hr_emp_01 rebuild parallel 4; 14:41:06 SQL> drop index hr.hr_emp_01;同義詞【Synonym】 同義詞是能夠以其他名稱調用表(其他對象)的資料庫對象。可以創建同義詞,給一個表(或其他對象)的替代名稱。 創建語法:
CREATE [PUBLIC] SYNONYM synonym_name FOR schema.object_name;同義詞分類: PUBLIC:公有同義詞,其他用戶均可以訪問。 PRIVATE:私有同義詞,只能是創建用戶所擁有或訪問(預設值)。 刪除同義詞:
DROP [PUBLIC] SYNONYM synonym_name; 註意:public公有同義詞用戶必須擁有drop public synonym許可權才能刪除。 14:57:16 SQL> drop public synonym dbmonitor_book2 15:00:20 2 ; drop public synonym dbmonitor_book2 * ERROR 在行 1: ORA-01031: 權限不足 15:00:25 SQL> drop synonym dbmonitor_book2 15:00:31 2 ; 已刪除同義字.