Oracle9i模式對(duì)象ppt課件

1、Oracle 9i 方式和方式對(duì)象 表table 表table為數(shù)據(jù)庫中數(shù)據(jù)存儲(chǔ)的根本單位，其數(shù)據(jù)按行、列存儲(chǔ)。每個(gè)表具有一表名和列的集合。每一列有一個(gè)列名、數(shù)據(jù)類型、寬度或精度、比例。一行是對(duì)應(yīng)單個(gè)記錄的列信息的集合。不同類型的表RegulartableClusterPartitionedtableIndex-organizedtable創(chuàng)建表CREATE TABLE employees(empno NUMBER(4), last_name VARCHAR2(30)deptno NUMBER(2)PCTFREE 20 PCTUSED 50STORAGE(INITIAL 200K NEXT 2

2、00KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE data01;刪除列SQL alter table lunartest 2 drop column OBJECT_NAME;表已更改。SQL或者SQL alter table lunartest 2 drop (SUBOBJECT_NAME,OBJECT_ID);表已更改。SQL將列標(biāo)志為“未運(yùn)用UNUSEDSQL ALTER TABLE lunartest SET UNUSED (DATA_OBJECT_ID);表已更改。SQL desc lunartest;或者SQL ALTER TABLE lunarte

3、st SET UNUSED (OBJECT_TYPE,CREATED);表已更改。SQL刪除未運(yùn)用的列SQL ALTER TABLE lunartest DROP UNUSED COLUMNS;表已更改。SQL或者SQL ALTER TABLE lunartest DROP UNUSED COLUMNS CHECKPOINT 20;表已更改。SQL并行創(chuàng)建復(fù)制表CREATE TABLE testSTORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)NOLOGGINGPARALLELTABLESPACE USERSASSELECT *

4、 FROM USER_OBJECTS;何時(shí)可以并行執(zhí)行？Create 部分：在CREATE TABLE AS SELECT 語句中包含了PARALLEL 子句指定了ALTER SESSION FORCE PARALLEL DDL 語句Select 部分： 查詢中包含了一個(gè)并行提示闡明PARALLEL， 或CREATE 部分包含PARALLEL 至少在一個(gè)查詢中指定的表需求全表掃描或索引范圍掃描跨越多個(gè)分區(qū)刪除約束運(yùn)用下屬命令刪除約束:用下屬命令刪除表及外鍵ALTER TABLE testDROP CONSTRAINT test_ln_uk;DROP TABLE testCASCADE CONS

5、TRAINTS;創(chuàng)建暫時(shí)表CREATE GLOBAL TEMPORARY TABLE一切會(huì)話都可以看到暫時(shí)表的定義，但只需將數(shù)據(jù)插入該表中的會(huì)話才干看見暫時(shí)表中的數(shù)據(jù)ON COMMIT DELETE ROWS子句(缺省)指出，每次事務(wù)提交后舍棄表刪除一切行ON COMMIT PRESERVE ROWS子句指出，終了會(huì)話時(shí)舍棄表刪除一切行Free space after deleteUnused blockHigh water markExtent ID 01234Extent ID 01234Used blockHigh Water MarkAfter insertsAfter deletes

6、找出High Water Mark:Extent ID 01234High water markLAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_IDTOTAL_BLOCKSUNUSED_BLOCKSDBMS_SPACE.UNUSED_SPACEFree space after deleteUnused blockHigh water mark釋放之前Used block釋放未運(yùn)用的空間ALTER TABLE scott.employeesDEALLOCATE UNUSED;釋放之后0 1 2 3 4清空表TRUNCATE TABLE scott

7、.employees;Extent ID 01High water mark Free space刪除表DROP TABLE scott.departmentsCASCADE CONSTRAINTS;檢索表的信息DBA_OBJECTSOWNEROBJECT_NAMEOBJECT_IDDATA_OBJECT_IDCREATEDDBA_SEGMENTSOWNERSEGMENT_NAMETABLESPACE_NAMEHEADER_FILEHEADER_BLOCKDBA_TABLESOWNERTABLE_NAMEPCT_FREEPCT_USEDINITIAL_EXTENTNEXT_EXTENTMIN_

8、EXTENTSMAX_EXTENTSPCT_INCREASECACHEBLOCKSEMPTY_BLOCKSCHAIN_CNT視圖view) 一個(gè)視圖view)是由一個(gè)或多個(gè)表或其他視圖中的數(shù)據(jù)的一種定制的表示，是用一個(gè)查詢定義 。視圖不分配任何存儲(chǔ)空間，視圖不真正地包含數(shù)據(jù) 視圖的優(yōu)點(diǎn)：經(jīng)過限制對(duì)表的行預(yù)定義集合的存取，為表提供附加的平安性隱藏?cái)?shù)據(jù)復(fù)雜性。為用戶簡化命令為根本表的數(shù)據(jù)提供另一種觀念。可將運(yùn)用隔離根本表定義的修正用于不用視圖無法表示的查詢?？捎糜诒９軓?fù)雜查詢。聚集cluster 一個(gè)聚集是一組表，將具有同一公共列值的行存儲(chǔ)在一同，并且它們經(jīng)常一同運(yùn)用。這些公共列構(gòu)成聚集碼。 索引

9、(index) 索引是為提高數(shù)據(jù)檢索的性能而建立 索引可建立在一表的一列或多列上 索引分類邏輯上單列或組合索引獨(dú)一非獨(dú)一索引留意：組合索引最多可以為32個(gè)列，并且，一切列的大小不能超越塊的1/3物理上 分區(qū)或非分區(qū) B 樹 正?；蚍聪蜴I 位圖DML 操作對(duì)索引的影響插入操作導(dǎo)致在適當(dāng)?shù)膲K中插入索引項(xiàng)刪除行只導(dǎo)致邏輯刪除索引項(xiàng)，刪除的行所占用的空間不能用于新項(xiàng)，直到刪除塊中的一切項(xiàng)PCTFREE 對(duì)索引沒有影響索引行格式每個(gè)索引行有獨(dú)一的表示create unique index iname on tname (a,b,c)create index iname on tname (a,b,c)L

10、abel：存儲(chǔ)列數(shù)和鎖定信息abcrowidlabelabcrowidlabel23KEY ROWIDEMPNO (BLOCK# ROW# FILE#)- -1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F.0001.00019947 0000000F.0000.0001.逆向鍵值索引Index on EMP (EMPNO) EMP tableEMPNO ENAME JOB .- - -7

11、499 ALLEN SALESMAN7369 SMITH CLERK7521 WARD SALESMAN .7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER7782 CLARK MANAGER.Bitmap索引row1234NameJoeJaneJohnJamesM_StatusSingleMarriedDivorcedMarriedStatePACACAPAGenderMFMMM_Status-IDXSingleMarriedDivorcedState-IDXCAPAGender-IDXMF1 0 0 00 1 0 00 0 1

12、 00 1 1 01 0 0 11 0 1 10 1 0 0Select count(*) from customerswhere M_Status = married ANDState = CA AND Gender = M25更適宜Bitmap的情況 當(dāng)表很大如數(shù)百萬行，且關(guān)鍵字列的基數(shù)低如性別列時(shí)，即該列有非常少的獨(dú)特值例 當(dāng)查詢經(jīng)常運(yùn)用涉及OR 運(yùn)算符的多個(gè)WHERE 條件組合時(shí) 當(dāng)關(guān)鍵字列上存在只讀或很少的更新操作時(shí)B 樹索引和位圖索引B 樹適宜高基數(shù)的列更新關(guān)鍵字列的費(fèi)用相對(duì)較低運(yùn)用OR 謂詞的查詢效率低對(duì)OLTP 有用位圖適宜低基數(shù)的列更新關(guān)鍵字列的費(fèi)用非常昂貴運(yùn)用OR 謂詞的查

13、詢效率高對(duì)數(shù)據(jù)倉庫有用建立通常的B-Tree 索引CREATE UNIQUE INDEX schema. indexON schema. table(column ASC | DESC , column ASC | DESC .)TABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING| NOLOGGING NOSORT 索引和空值B*Tree索引不存儲(chǔ)完全NULL(空)的條目，但是位圖索引和聚族索引存儲(chǔ)完全NULL(空)的條目。oracle中除了group by

14、會(huì)以為(NULL,NULL)=(NULL,NULL)，其他任何情況下的空值條目比較都視為不同的空值，即(NULL,NULL) (NULL,NULL)為了保證真正的獨(dú)一性，每個(gè)獨(dú)一約束unique index應(yīng)該至少有一個(gè)NOT NULL字段。索引和視圖 假設(shè)根本表中定義了索引，普通情況下還是應(yīng)該想方法用到那些索引 一些子句闡明INITRANS 指定每塊中預(yù)先分配的事務(wù)項(xiàng)的數(shù)目，缺省值和最小值為2MAXTRANS 限制分配給每塊的事務(wù)項(xiàng)的數(shù)目，缺省值為255建立B-TREE的例子CREATE INDEX test_idxON test(a)PCTFREE 30STORAGE(INITIAL 20

15、0K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx;建立索引的原那么平衡查詢和DML 需求放在一個(gè)單獨(dú)的表空間內(nèi)非 SYSTEM, RBS, TABLE運(yùn)用一致的區(qū)大小(閱歷值： 五塊的倍數(shù)或表空間MININUM EXTENT 大小的倍數(shù)思索對(duì)大索引運(yùn)用NOLOGGING假設(shè)新的關(guān)鍵字值有能夠位于當(dāng)前范圍內(nèi), 那么設(shè)置高PCTFREE建立逆向索引CREATE UNIQUE INDEX schema. indexON schema. table(column ASC | DESC , column ASC | DESC .)TABLE

16、SPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING| NOLOGGING REVERSE留意，這里不能運(yùn)用 nosort建立逆向索引的例子SQL CREATE UNIQUE INDEX test_rsidx 2 ON test(b) REVERSE 3 PCTFREE 30 4 STORAGE(INITIAL 200K NEXT 200K 5 PCTINCREASE 0 MAXEXTENTS 50) 6 TABLESPACE indx;索引已創(chuàng)建。SQL建立Bitma

17、p索引 CREATE BITMAP INDEX schema. indexON schema. table(column ASC | DESC , column ASC | DESC .)TABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING| NOLOGGING NOSORT 留意，Bitmap 不能是 unique 的建立Bitmap索引的例子SQL CREATE BITMAP INDEX test_bm_idx 2 ON test(c) 3 PCTFREE

18、 30 4 STORAGE(INITIAL 200K NEXT 200K 5 PCTINCREASE 0 MAXEXTENTS 50) 6 TABLESPACE indx;索引已創(chuàng)建。SQL創(chuàng)建基于函數(shù)的索引SQL CREATE INDEX TEST_BFI ON TEST(A + B);索引已創(chuàng)建。SQL SELECT * FROM TEST WHERE A+B 分配釋放索引空間SQL ALTER INDEX test_bm_idx 2 ALLOCATE EXTENT (SIZE 200K 3 DATAFILE E:ORACLEORA92TESTINDX01.DBF);索引已更改。SQLSQ

19、L ALTER INDEX test_bm_idx 2 DEALLOCATE UNUSED;索引已更改。SQL留意：釋放HWM之上的重建索引ALTER INDEX test_bm_idx REBUILDTABLESPACE users;重建索引命令可用來:將索引移到另一個(gè)表空間改良空間利用率將反向索引改為通常的 B-tree 索引，或者將B-tree 索引改為反向索引重建索引的特點(diǎn)運(yùn)用現(xiàn)有索引作為數(shù)據(jù)源建立新索引運(yùn)用現(xiàn)有索引建立索引時(shí)無須排序從而使性能更佳在建立新索引期間需求有足夠的空間以包容新舊索引在建立新索引的過程中查詢可繼續(xù)運(yùn)用現(xiàn)有索引重建索引的語法ALTER INDEX schema.

20、 index REBUILDTABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING | NOLOGGING REVERSE | NOREVERSE 聯(lián)機(jī)創(chuàng)建或者重建索引可以在最低限制的表鎖定下完成重建索引，例如：ALTER INDEX test_bm_idx REBUILD ONLINE;或者 CREATE INDEX TEST_BT_IDX ON TEST (A,B) ONLINE;不能在暫時(shí)表中重建索引不能重建整個(gè)分區(qū)索引，必需分別重建各分區(qū)思索UNIQUE

21、 和PRIMARY KEY約束手工控制UNIQUE 和PRIMARY KEY的相應(yīng)索引。創(chuàng)建大索引分配一個(gè)較大的暫時(shí)表空間或者創(chuàng)建一個(gè)新的大尺寸的暫時(shí)表空間用ALTER USER 語句讓用戶運(yùn)用該暫時(shí)表空用CREATE INDEX 語句創(chuàng)建索引用DROP TABLESPACE 語句刪除該表空間，然后將用戶的暫時(shí)表空間復(fù)原成以前的暫時(shí)表空間監(jiān)視索引能否被運(yùn)用開場監(jiān)視：ALTER INDEX index MONITORING USAGE停頓監(jiān)視：ALTER INDEX index NOMONITORING USAGE查詢視圖V$OBJECT_USAGE 來查詢正在被監(jiān)視的索引驗(yàn)證索引ANALYZE

22、 INDEX TEST_BFI VALIDATE STRUCTURE;INDEX_STATS刪除索引在批量裝載之前刪除刪除不常需求的索引引刪除和重建無效INVALID索引DROP INDEX test_bm_idx;獲取索引信息DBA_INDEXESOWNERINDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAMEUNIQUENESSTABLESPACE_NAMELOGGINGSTATUSDBA_IND_COLUMNSINDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITIONCOL

23、UMN_LENGTH 查詢當(dāng)前用戶的索引及索引列的信息SQL COLUMN COLUMN_NAME FORMAT A15SQL COLUMN INDEX_NAME FORMAT A15SQL COLUMN TABLE_NAME FORMAT A15SQL SELECT c.COLUMN_NAME, COLUMN_POSITION, 2 i.INDEX_NAME, i.TABLE_NAME, i.TABLE_OWNER 3 FROM user_ind_columns c, user_indexes i 4 where c.INDEX_NAME=i.INDEX_NAME 5 /COLUMN_NAM

24、E COLUMN_POSITION INDEX_NAME TABLE_NAME TABLE_OWNE- - - - -A1 1 AI A LUNARB1 1 BI B LUNARB2 2 BI B LUNARC1 1 CI C LUNARC2 2 CI C LUNARC 1 TEST_BFI TEST LUNAR已選擇6行。SQL查詢當(dāng)前用戶的索引信息SQL column INDEX_NAME format a30SQL column INDEX_TYPE format a10SQL column TABLE_NAME format a10SQL column TABLE_OWNER form

25、at a10SQL select INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_OWNER 2 from user_indexes;INDEX_NAME INDEX_TYPE TABLE_NAME TABLE_OWNE- - - -AI NORMAL A LUNARBI NORMAL B LUNARCI NORMAL C LUNARSYS_IL0000028348C00003$ LOB JY_RENWU LUNARSYS_IL0000028348C00004$ LOB JY_RENWU LUNARTEST_BM_IDX BITMAP TEST LUNAR已選擇6

26、行。SQL檢查索引及其有效性SQL SELECT index_name, tablespace_name, index_type,2 uniqueness, status3 FROM dba_indexes4 WHERE owner=SUMMIT;INDEX_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS- - - - -EMPLOYEE_LAST_. INDX NORMAL NONUNIQUE VALIDORDERS_ID_IDX INDX NORMAL UNIQUE VALIDORDERS_REGION_. INDX02 BITMAP NO

27、NUNIQUE VALID3 rows selected.列出一切反向鍵索引的稱號(hào)SQL SELECT o.object_name2 FROM dba_objects o3 WHERE owner=SUMMIT4 AND o.object_id IN (SELECT i.obj#5 FROM ind$ i6 WHERE BITAND(perty,4) = 4);OBJECT_NAME-ORDERS_ID_IDX1 row selected列出某個(gè)用戶所擁有的全部索引及相關(guān)信息SQL SELECT index_name, table_owner, table_name, column_

28、name2 FROM dba_ind_columns3 WHERE index_owner = SUMMIT4 ORDER BY index_name, column_position;INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME- - - -EMPLOYEE_LAST_NAME. SUMMIT EMPLOYEE LAST_NAMEORDERS_ID_IDX SUMMIT ORDERS IDORDERS_REGION_ID. SUMMIT ORDERS REGION_ID3 rows selected.序列(sequence) 序列為ORACLE整

29、數(shù)，最多可有38位數(shù)字 序列號(hào)生成是獨(dú)立于表，所以同一序列生成器可用于一個(gè)和多個(gè)表 序列號(hào)可用于生成獨(dú)一的主碼 創(chuàng)建序列SQL CREATE SEQUENCE test_sequence 2 INCREMENT BY 1 3 START WITH 1 4 NOMAXVALUE 5 NOCYCLE 6 CACHE 10;序列已創(chuàng)建。SQL運(yùn)用序列SQL select test_sequence.currval from dual ;select test_sequence.currval from dual *ERROR 位于第 1 行:ORA-08002: 序列TEST_SEQUENCE.CU

30、RRVAL 尚未在此進(jìn)程中定義SQL select test_sequence.nextval from dual ; NEXTVAL- 1SQL select test_sequence.currval from dual ; CURRVAL- 1SQL刪除序列SQL drop sequence test_sequence;序列已丟棄。SQL同義詞(synonym) 一個(gè)同義詞(synonym)為任何表、視圖、快照、序列、過程、函數(shù)或包的別名，其定義存儲(chǔ)在數(shù)據(jù)字典中 有兩種同義詞：公用和公用 。公用同義詞可為數(shù)據(jù)庫中每一個(gè)用戶所存取 ；公用同義詞是包含在指定用戶的方式中，僅為該用戶和授權(quán)的用

31、戶所運(yùn)用。 創(chuàng)建同義詞SQL select * from tab;TNAME TABTYPE CLUSTERID- - -A TABLEB TABLEC TABLEJY_PER TABLEJY_RENWU TABLEPLAN_TABLE TABLESQLEXPERT_PLAN1 TABLETEST TABLE已選擇8行。SQL CREATE PUBLIC SYNONYM testsyn FOR lunar.aaa;同義詞已創(chuàng)建。SQL刪除同義詞刪除私有同義詞：DROP SYNONYM testsyn;刪除公共同義詞：DROP PUBLIC SYNONYM testsyn1;例如：SQL CRE

32、ATE PUBLIC SYNONYM testsyn1 FOR lunar.aaa;同義詞已創(chuàng)建。SQL drop SYNONYM testsyn1;drop SYNONYM testsyn1 *ERROR 位于第 1 行:ORA-01434: 要?jiǎng)h除的隱含同義詞不存在SQL drop public SYNONYM testsyn1;同義詞已丟棄。SQL數(shù)據(jù)完好性ApplicationcodeIntegrityconstraint(首選)DatabasetriggerTableData62約束的類型約束NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK闡明列不能

33、包含空值列或列的組合 的值獨(dú)一列或列的組合 為表的主鍵列或列的組合 為表的外鍵表中每一行必需滿足的特殊條件約束形狀延遲約束Deferred ConstraintsDML 語句檢查非延遲約束commit檢查延遲約束外鍵操作刪除主表Truncate 主表刪除含主表的表空間主表做DML操作時(shí)防止子表 鎖定子表 上做DML應(yīng)運(yùn)用的選項(xiàng)級(jí)聯(lián)約束(Cascade constraints)Disable/drop foreign keyUse CASCADE CONSTRAINTS clauseCreate index on foreign key確保包含父表索引的表空間聯(lián)機(jī)父表所在表空間可以不聯(lián)機(jī)涉及父表

34、的DDL 刪除父表之前必需刪除外鍵在未刪除或禁用外鍵之前無法刪除父表：DROP TABLE table CASCADE CONSTRAINTS 刪除包含父表的表空間之前必需刪除外鍵：DROP TABLESPACE tablespace INCLUDING CONTENTSCASCADE CONSTRAINTS建立表時(shí)定義約束SQL CREATE TABLE lunar_emp( 2 id NUMBER(7) 3 CONSTRAINT employee_id_pk PRIMARY KEY 4 DEFERRABLE 5 USING INDEX 6 STORAGE(INITIAL 100K NEXT 100K) 7 TABLESPACE indx, 8 last_name VARCHAR2(25) 9 CONSTRAINT employee_last_name_nn NOT NULL, 10 dept_id NUM