Oracle學習筆記—(四)

Oracle學習筆記---(四)

     四

   在system使用者中建立使用者和授權：
            create user usera  identified
by  usera  defalut tablespace test;
            grant connect,resource to
usera;            

一，同義字

   分私人和公用

  
私人:普通使用者建立的，只有建立該同義字的使用者才可以使用
        前提：普通使用者具有對scott使用者的表emp具有訪問權利
        

         connect scott/tiger;
         grant all on  emp to  usera;    
--all  包括：select ,update ,insert ,delete
        
connect  usera/usera;
         create synonym  emp for scott.emp;
        
select * from emp;
   公用:公用一般由dba建立，需要具有create public synonym
系統許可權,如普通使用者要建立需要
        connect system/manager;
        grant  create
public synonym  to usera;
        conect usera/usera;

        create public synonym  emp for
scott.emp;
        其他使用者也可以使用公用同義字  emp  
          
  
查看使用者自己建立的同義字:user_synonyms  
        select synonym_name from
user_synonyms;

二，序列

    通常和表一起使用，用來產生唯一主索引值，在插入資料時使用，但不屬於任何錶，獨立於表存在      

            create
sequence   deptseq
            increment  by   10
            start
with    50

      insert
into  dept  values(deptseq.nextval,'Sale_dept','HaizhuStreet');
    
使用偽列nextval  和  currval  
     nextval:建立序列後第一次使用返回序列初始值，既start
with指定的值，後續使用增量後的值
     currval:返回序列的當前值
      資料詞典:
user_sequences

三、視圖

      在建立視圖時可以使用group by ,order by 子句,函數等,使用函數時需要指定列別名
  1,
簡單視圖：使用單表查詢建立的視圖
    
      可以對視圖進行增刪改操作,除了一些限定外，如：建立唯讀視圖，或帶有with chek
option 選項的視圖，或視圖中不包含
      基表中不允許為空白的列(不允許向視圖中插入資料)等

     1）使用with check option  可以限定對單表視圖的修改,  不能更新無法通過該視圖查看的行
        create
or replace view  emp_view  as select * from emp where job='SALESMAN' with check
option constraint empv;

   SQL> select * from emp_view;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL      
COMM     DEPTNO
---------- ---------- --------- ---------- ----------
---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698
20-2月 -81       1600        300         30
      7521 WARD      
SALESMAN        7698 22-2月 -81       1250        500         30
      7654
MARTIN     SALESMAN        7698 28-9月 -81       1250       1400        
30
      7844 TURNER     SALESMAN        7698 08-9月 -81      
1500          0         30

       update emp_view  set job='CLERK' where empno=7499;將無法更新
      
如建立視圖時沒有with check  option， 修改視圖後將在視圖查不到該行資料

    2） 使用with read  only  建立唯讀視圖
        create or replace
view  emp_view  as select * from emp where job='SALESMAN' with  read
only;
    3)  建立視圖時使用force,強制建立視圖      
      
如果視圖定義中引用了不存在的表或表中有不存在的列，或建立視圖的所有者沒有訪問表的許可權，都可以建立，
      
但建立的視圖有錯誤，在稍後建立表或得到存取權限後，ORACLE自動重新編譯使視圖有效，可以手動編譯
            
      
假設dept表也不存在a1列，但還可以建立視圖，但會提示錯誤，在表dept添加列a1後,視圖自動編譯為有效
       create or
replace force view emp_view  as select  a1,dname  from dept;
       可以
使用  alter view   emp_view  compile ;   手動編譯
    
      
   2, 複雜視圖
：
         1)使用多表聯結建立視圖,
         2)建立視圖時包括函數或group by 等
        

       複雜視圖通常不能直接進行增刪改，但對於使用多表聯結建立的視圖，可以使用INSTEAD OF
觸發器可以修改(後面課程)
       create or replace view emp_dept_view as select
empno,ename,dname,emp.deptno
       from dept,emp where
dept.deptno=emp.deptno;
      
   3,
鍵保留表
      鍵保留表:複雜視圖中出現的表。
      如果滿足條件:1)
主鍵列全部顯示在視圖中，並且它們的值在視圖中都是唯一且非空的.
      如:       create or replace view
emp_dept_view as
              select empno,ename,dname,dept.deptno

                from dept,emp where dept.deptno=emp.deptno;

       empn是emp表的主鍵,並且它的值在emp_dept_view 中是唯一且非空.所以emp表是鍵保留表
            
而deptno的值在視圖中不唯一,所以dept不是鍵保留表
      可以
修改視圖中鍵保留表中的資料,主索引值除外
              update emp_dept_view set ename='JOE' where
empno=7566;  //可以
              update emp_dept_view set dname='abc' where
deptno=30;  //不行   dname是dept表中的列,dept不是鍵保留表      
      
  
4,分區視圖
     create or replace view  v_emp  as select * from
emp1  partition(p1) union all select * from  emp1 partition(p2);

     資料字典: user_views    
     使用方法：在SQL*PLUS下
             col
view_name for a10     --定製顯示的列寬
             col text for for
a50
             set linesize 200          --設定每行顯示的字元數
            
select view_name,text from user_views;  
--查看使用者建立的視圖情況
      
四，索引    
    
      使用索引的目的：
      1）強制唯一    ：在primary
key 列或UNIQUE 列上自動建立唯一索引
      2）提高查詢速度: 當查詢使用索引時，速度會有戲劇性的提高
    
只有在查詢中使用where 或 order by 時，才使用索引
    1，唯一索引
        create
unique  index  ind2  on  dept(deptno);
        定義索引的列中，表的任意兩行的值都不相同
    2,
複合式索引
       在表的多列上建立的索引
       create index  comind1  on  
emp(deptno,empno)
    3, 反向鍵索引
       該索引反轉索引列中的每一個位元組
      
使插入操作分布在整個索引上，避免插入資料時索引效能的降低。但會使查詢變慢
       create   index  revind1  on  
salgrade(grade) reverse;

       可將反向鍵索引改為標準索引
       alter
index  revind1  rebuild  noreverse;

    4,位元影像索引(可在單個或多個串連表上建立)
      如果列重複的次數超過了100次，則可以考慮在該列上建立位元影像索引。既不同值的數目比行數少的情況
      表有1000000
行，某列有10000個不同值,可在該列上建立位元影像索引
      在位元影像索引中，使用每個索引值的位元影像，而不是使用rowid。位元影像中的每一位對應一個可能的rowid
                          
映射函數
                  每位位置---------rowid(確定表的一行)
     　當where 子句中有and 或
or  條件時，可直接在位元影像上進行布爾運算
               create bitmap index bitemp on
emp(deptno);

    5,索引組織表
      對錶是基於主鍵的訪問，而不是基於rowid的訪問
      表中的資料象索引一樣被儲存，表中就沒有rowid,因此使用者不能從
索引組織表
中選取rowid偽列的值
      對於要求精確匹配的查詢和基於範圍的搜尋，它提供了一種更快的訪問資料的方法
　　　表的資料存放區在與其關聯的索引中，索引中不儲存rowid
,而是儲存表的實際資料

       create table  indorg (
                            
a1  number(2)  primary key,
                            
a2  varchar2(10)
                            )
        organization  
index;
       使用者不能在indorg表的其它列上建立索引，當資料很少變化時，使用索引組織表
      
    
6,基於函數的索引
      
        如果在where
中某列上使用了函數，即使在列上建立了索引，索引也不會被使用。所以可以建立函數索引
         create index exind1  on
emp(length(ename));    --基於函數建立索引
         create index exind2  on
emp(sal+nvl(comm,0));  --基於運算式建立索引

     7,鍵壓縮索引
       為了節省索引塊的空間,相同的列值，作為首碼儲存一次,尾碼是不同的rowid
        
create index exind1  on emp(length(ename)) compress 1;
--作為首碼處理的鍵列的數目為1
         create index exind2  on emp(deptno) compress
1;

     8,索引分割區
                
      
當使用者建立了分區表後，也可以在該表上建立索引,這個索引可以按照用於表分區同樣值範圍進行劃分
　　　
       create table
emp1(
       empno  number(4),
       ename  varchar2(10),
       job  
varchar2(9),
       mgr   number(4),
       hiredate date,
      
sal  number(7,2),
       comm  number(7,2),
      
deptno  number(2))
       partition  by  range(empno)
       (
      
partition  p1   values  less than  (7566),
       partition  p2  
values  less than  (7900),
       partition  p3   values  less
than  (maxvalue)       )
  
    
　     1)  使用local
關鍵字，可以為每一個分區建立一個獨立的索引,
           這個索引命令將建立３個獨立的索引，每個索引對應一個分區
          
資料字典：
           select index_name,partition_name ,tablespace_name from
user_ind_partitions

           i)   本地無首碼索引
                create index  emp_ind1  on
emp1(ename)   local

           ii)  本地首碼索引(索引的第一列與分區的第一列相同)
　　　　　　　  create
index  emp_ind1  on emp1(empno)   local;
               或
指定不同的索引資料表空間
               create index  emp_ind1  on emp1(empno)  
local
               (  partition  p1   tablespace
test1_idx,
                  partition  p2   tablespace
test2_idx,
                  partition  p3   tablespace
test3_idx)
            如果分區上執行了修改，會自動重建索引
     SQL> col segment_name
for a20
     SQL> select
segment_name,partition_name,segment_type,tablespace_name from user_segments
where segment_name='EMP_IND1';

     SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE      
TABLESPACE_NAME
     -------------------- ------------------------------
------------------ ------------------------------
     EMP_IND1            
P1                             INDEX PARTITION    SYSTEM
    
EMP_IND1             P2                             INDEX
PARTITION    SYSTEM
     EMP_IND1             P3                            
INDEX PARTITION    SYSTEM

　　　 2)  使用global
關鍵字,建立全域索引
            i)  全域無首碼索引
                create index
emp_ind2  on emp1(ename) global;              
      
      　　　ii)
全域首碼索引
                建立全域首碼索引(empno為首碼，不能使用ename做索引列)  

                create index emp_ind2  on emp1(empno,ename)

                global
                partition
by  range(empno)
                (
                
partition  p1  values  less than  (6000) ,
                
partition  p2  values  less than  (7500) ,
                
partition  p3  values  less than
(maxvalue)
                )
          對分區的任何操作都會使全域索引無效，需要重建索引

     注釋：不能手動刪除索引分割區，當所引用的資料從表分區中刪除時，索引分割區自動刪除

　五，簇

　　　　簇是一組表，由兩個或多個擁有公用列的表組成
　　　　簇是用來儲存表的方法，這些表相互聯絡密切並通常相串連在磁碟的相同地區上。
　　　　如：emp
表格儲存體在磁碟的一個地區(section)
內,而dept表格儲存體在另一地區內，它們的行可以被串連到同一地區上，該地區
　　　　稱為簇（cluster）,簇關鍵字通常是在查詢中把表串連起來的一列或幾列(如dept,emp中的deptno),要聚簇這些表，使用者必須
　　　　是表的所有者
        
        1,建立了空簇
          create
cluster  emp_dept(deptno_key  number(2));
        建立了空簇（如同建表一樣，也設定了空間）
      

        2,下面建立包含在簇中的表
          create  table  dept
          (
            
deptno  number(2)  primary key,
             dname  char(14),
            
local  char(13)
          )    
            cluster  emp_dept(deptno);  
          
--deptno為表中將要儲存在簇關鍵字deptno_key中的列名                    

          create table
emp
          (
          empno  number(4),
          ename  varchar2(10),
          job  
varchar2(9),
          mgr   number(4),
          hiredate
date,
          sal  number(7,2),
          comm  number(7,2),
          deptno  number(2)
references
dept
          )
          cluster  emp_dept(deptno);
            

      3, 建立簇索引(在向簇表插入資料之前建立簇索引)
           create index  
emp_dept_NDX  on  cluster  emp_dept;  

      4, 插入資料

          insert into  emp select * from scott.emp;
          insert into
dept select * from scott.dept;  

         兩個表的資料實際儲存在同一位置， 簇好象是包含兩個表資料的一個大表