Oracle基礎 12 物件 objects 同義詞/序列/試圖/索引

--建立同義詞
create public synonym employees for hr.employees;  --公共同義詞
需要 create public synonym 許可權

表的所有使用者授予公共許可權 
grant select on employees to public;

create synonym t1_s for t1;   --私有同義詞
如果想在不同的環境中通過不同的別名引用同一個表時，要建立私有同義詞。
 
編譯同義詞 
alter synonym t1_s compile;
 
管理同義詞
select table_name, synonym_name 
from dba_synonyms
where owner='IKKI';
 
刪除同義詞
drop public synonym employees;
drop synonym t1_s;

--建立序列
  create sequence seq1
  increment by 1
  start with 1
  maxvalue 100 | minvalue 10 | nomaxvalue
  cache | nocache
  cycle | nocycle;

  例如：
  create sequence seq1
  increment by 1
  start with 10
  maxvalue 999
  nocache
  nocycle;

檢視序列
  select * from user_sequences;
  select seq1.nextval from dual;
  select seq1.currval from dual;

使用序列
  create table emp2(
    id number(10),
    name varchar2(20),
    dept varchar2(20),
    bh number(10)
  );
  insert into emp2(bh,id,name,dept)
  values(seq1.nextval,302,'tom',2002);

--rowid
  select rowid,bh,id,name 
  from emp2;

  select * from emp2
  where rowid='AAASOVAAEAAAAKOAAB';

--建立試圖
  create view my_employees as
  select employee_id,first_name,last_name,salary
  from employees
  where manager_id=122
  with read only;            --使用者只能從試圖進行查詢
 
刪除試圖
  drop view my_employees;

物化試圖
  可以在物化試圖上做以下事情：
  在物化試圖上建立索引；
  在分割槽表上建立物化試圖；
  對物化試圖進行分割槽。

 
重新整理方式
  on commit：用此方式，當一個主表的更改資料被提交時，物化試圖被自動重新整理以反應資料的更改。
  on demand：用此方式，你必須執行諸如 DBMS_MVIEW.REFREST 這樣的過程來更新物化試圖。

 
重新整理型別
  complete：此重新整理型別將完全重新計算基於物化試圖的查詢。如果物化試圖原來花費12小時建立，那麼重建也將花費同樣的時間。顯然，當主表中很少一部分行被更改、刪除或插入時，不應該使用此選項。
  fast：Oracle 將使用物化試圖日誌來記錄主表的所有更改。然後使用物化試圖日誌來更新物化試圖。物化試圖日誌是基於相應物化試圖的表。在物化試圖與聯接相關的每個表都需要有自己的物化試圖日誌，以便捕捉表的更改。
  force：如果選擇此選項，Oracle 將盡量使用 fast 重新整理機制，如果由於某些原因而不能使用該機制，則將使用 complete 重新整理方法。此選項預設的重新整理方法。
  never：此重新整理選項不重新整理物化試圖。顯然，對於主表有許多更改的物化試圖來說，這不是一個可行的選項。

  使用 DBMS_MVIEW 程式包

 
建立物化試圖
1、授予必要的許可權
   grant create materialized view to ikki;
   grant query rewrite to ikki;   
 
2、建立物化試圖日誌
   create materialized view log
   on products with sequence,rowid
   (prod_id, prod_name, prod_desc, prod_subcategory,
    prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id,
    prod_status, prod_list_price, prod_min_price)
    including new values;

  create materialized view log on sales
  with sequence,rowid
  (prod_id, cust_id, time_id, channel_id, promo_id,
   quantity_sold, amount_sold)
   including new values;

 
3、建立物化試圖
  create materialized view product_sales_mv
  build immediate
  refresh fast
  enable query rewrite
  as select p.prod_name, sum(s.amount_sold) as dollar_sales,
  count(*) as cnt, count(s.amount_sold) as cnt_amt
  from sales s, products p
  where s.prod_id=p.prod_id
  group by p.prod_name;

  build immediate:立即建立物化試圖，這是預設選項。另一種方法是使用 build deferred 選項，此選項將在以後某個指定時刻裝載物化試圖及其資料。
  refresh fast:說明物化試圖採用 fast 重新整理方法。
  enable query rewrite:表示 Oracle 優化程式將透明的重寫查詢以使用新建立的物化試圖而不是基礎的主表。

SQL> create materialized view log on ygb;
Materialized view log created.

SQL> create materialized view mv_ygb
  2  refresh fast
  3  as select * from ygb;
Materialized view created.

刪除物化檢視日誌
SQL> drop materialized view log on ygb;
Materialized view log dropped.

刪除物化檢視
SQL> drop materialized view mv_ygb;
Materialized view dropped.

--建立索引
  create index emp_tb_id on emp2(id);

刪除索引
  drop index emp_tb_id;

重建索引
  alter index emp_tb_id rebuild;

  alter index emp_tb_id rebuild online;

在進行索引的聯機重建時，可以執行所有的 DML 操作，但不能執行任何 DDL 操作。

 

不可見索引
你可以把不可見索引作為臨時索引，以便執行某些特殊操作，或者用來在索引成為正式索引前先對其進行測試。此外，有時使一個索引不可見可見替代刪除索引或使其不可用。你可以使一個索引臨時不可見來測試刪除此索引的效果。

1、建立不可見索引
create index idx_test on test(tname)
invisible;

2、更改索引使其不可見
alter index idx_test invisible;
alter index idx_test visible;

3、查詢索引是否可見
select index_name,visibility from user_indexes
where index_name='IDX_TEST';

--關於建立 Oracle 表的有效索引的準則：
    1、如果需要訪問的資料不超過表中的4%或5%，則可以使用索引。全表掃描適用於請求的資料佔全表資料百分比較高的查詢。請記住，用索引來檢索行需要兩個讀操作：即讀索引和讀表。
    2、相對較小的表應該避免使用索引。全表掃描很適合小表。對於小表不需要同時儲存表的資料和索引的資料。
    3、為所有表建立主鍵。在指定一列作為主鍵時，Oracle自動建立該列的索引。
    4、對包括在多表聯接操作中使用的列建立索引。
    5、對 where 子句中頻繁使用的列建立索引。
    6、對包括在 order by 和 group by 操作的列或涉及排序的 union 和 distinct 等其他操作中的列建立索引。由於索引已經被排序，因此執行前述操作的排序要求將顯著減少。
    7、由長字串組成的列通常不是索引的候選列。
    8、被頻繁更改的列由於涉及開銷問題，理論上不建索引。
    9、使索引數目較少。
    10、當唯一列值可能不唯一時需要使用組合索引。在組合索引中，驅動列或第一列應該為選擇性最強的列。
建立索引的黃金法則：表的索引應該基於你鎖期望的查詢中出現的表列。一個表可以建立多個索引：可以選擇建立X列或Y列或二者的組合索引。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27633655/viewspace-1080364/，如需轉載，請註明出處，否則將追究法律責任。