物化視圖，索引視圖，函數索引，創建索引時使用DESC

老板交給的一個任務，搜了一下資料，覺得還是總結一下比較好。假如以後用到了呢？圍繞兩個主題：一是視圖上能夠建索引，二是在創建索引時是否可以使用DESC關鍵字。
 

一、能否在視圖上創建索引
 

因為普通的視圖並沒有存儲實際的信息，它所操作的數據來自於基本表，所以在普通視圖上不可以創建索引。

在oracle中執行如下的語句，會報“視圖不適用於此處”的錯誤

create view test_car
as
select license
from cars;

create index index_vew_car on test_car(license);

那當需要對視圖進行大量查詢，而查詢效率較低時，如何處理呢？有以下幾種方法：
Oracle 中有物化視圖可以建立索引，SQLServer 中索引視圖可以建立索引，Oracle 中基於函數的索引也可以解決這個問題。

1、物化視圖
 

Oracle的物化視圖是包括一個查詢結果的數據庫對像，它是遠程數據的的本地副本，或者用來生成基於數據表求和的匯總表。物化視圖存儲基於遠程表的數據，也可以稱為快照。

物化視圖可以用於預先計算並保存表連接或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。物化視圖有很多方面和索引很相似：使用物化視圖的目的是為了提高查詢性能；物化視圖對應用透明，增加和刪除物化視圖不會影響應用程序中SQL語句的正確性和有效性；物化視圖需要占用存儲空間；當基表發生變化時，物化視圖也應當刷新。

物化視圖可以查詢表，視圖和其它的物化視圖。

通常情況下，物化視圖被稱為主表（在復制期間）或明細表（在數據倉庫中）。
對於復制，物化視圖允許你在本地維護遠程數據的副本,這些副本是只讀的。如果你想修改本地副本，必須用高級復制的功能。當你想從一個表或視圖中抽取數據時，你可以用從物化視圖中抽取。
對於數據倉庫，創建的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接視圖。
在復制環境下，創建的物化視圖通常情況下主鍵，rowid,和子查詢視圖。

物化視圖由於是物理真實存在的，故可以創建索引。

 

對比普通視圖
 

普通視圖不存儲物理信息，僅僅是一個sql語句。所以不能在其上創建索引。

物化視圖是物理真實存在的，可以創建索引。

2、SQLServer 中的索引視圖
 

SQLServer 中可以創建索引視圖，類似於物化視圖的概念。

SQL Server 2008 創建索引視圖(物化視圖) 的一點總結

3、Oracle 中基於函數的索引
 

概念：在DML操作時如果經常使用某個表達式作為條件，那麼可以建立基於該函數的索引。在創建此類索引時，Oracle首先對包含索引列的函數或表達式進行求值，然後對這些值進行排序，最後再存儲到索引中。基於函數的索引可以是普通的B樹索引，也可以是位圖索引。

動機：比如執行如下一條SQL語句：select * from emp where upper(ename) = 'KING'，即使在ename上建立了索引，還是會全表掃描emp表，將裡面的ename字段改成大寫跟常量KING進行比較。如果我們建立一個基於函數的索引，比如：create index emp_upper_idx on emp(upper(ename)); 這個時候，我們只需要按區間掃描小部分數據，然後獲取rowid取訪問表中的數據，這個速度是比較快的。

基於函數的索引，類似於普通的索引，只是普通的索引是建立在列上，而它是建立在函數上。當然這回對插入數據有一定影響，因為需要通過函數計算一下，然後生成索引。但是插入數據一般都是少量插入，而查詢數據一般數據量比較大。為了優化查詢速度，稍微降低點插入速度是可以承擔的。
 

一個例子
 

談到任何對列的操作都可能導致全表掃描，例如：

select * from emp where substr(ename,1,2)=’SM’; 

但是這種查詢在客服系統又經常使用，我們可以創建一個帶有substr函數的基於Oracle函數索引，

create index emp_ename_substr on eemp ( substr(ename,1,2) ); 

這樣在執行上面的查詢語句時，這個基於函數的索引將排上用場，執行計劃將是（INDEX RANGE SCAN）。
上面的例子中，我們創建了基於函數的索引，但是如果執行下面的查詢：

select * from emp where substr(ename,1,1)=’S’ 

得到的執行計劃將還是（TABLE ACCESS FULL），因為只有當數據列能夠等式匹配時，基於函數的索引才能生效，這樣對於這種索引的計劃和維護的要求都很高。請注意，向表中添加索引是非常危險的操作，因為這將導致許多查詢執行計劃的變更。然而，如果我們使用基於函數的索引就不會產生這樣的問題，因為Oracle只有在查詢使用了匹配的內置函數時才會使用這種類型的索引。（我的理解是，創建普通的索引時會導致查詢執行計劃的變更；而創建函數索引，只有當查詢條件匹配時，執行計劃才會考慮這個索引。）

二、創建索引時能否用 DESC
 

Oracle 創建索引的語法如下：

create [ unique | bitmap ] index [ schema. ] < index_name >
on [ schema. ] < table_name >
(< column_name > | < expression > ASC | DESC,
< column_name > | < expression > ASC | DESC, ...)
[ tablespace < tablespace_name >]
[ storage ( < storage_settings > ) ]
[ logging | nologging ]
[ nosort | reverse ]
[ partition | global partition ]

其中ASC和DESC表示創建的索引為升序、降序排列。創建索引時可以指定多個字段或多個表達式，之間用逗號隔開。

如：

create table cars
(
  license int,
  owner varchar(8),
  model varchar(8)
);

create index idx_carlcs on cars(license DESC);