pseudocolumns, such as the ROWNUM, SYSDATE, and USER
SELECT ROWNUM, SYSDATE, and USER FROM DUAL;
TRUNC(A,B)
UPPER(last_name), LOWER(A) ,INITCAP(first_name), INITCAP(last_name)
RTRIM(first_name)? LTRIM(last_name)? TRIM(last_name)? TRIM(first_name),RPAD(last_name, 30, ' ')
SUBSTR(last_name, 1, 10),REPLACE()
TO_CHAR(SYSDATE, 'DD-MON-YYYY AD')
EXTRACT(MONTH FROM SYSDATE),TO_NUMBER('1234.99'),TO_DATE('27-OCT-98', 'DD-MON-RR')
TO_TIMESTAMP('10-Sep-05 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
MONTHS_BETWEEN(SYSDATE, HIRE_DATE)),???? ADD_MONTHS(hire_date, 3),LAST_DAY(hire_date),SYSTIMESTAMP
Aggregate functions include AVG, COUNT, DENSE_RANK, MAX, MIN, PERCENT_RANK, RANK, STDDEV, and SUM?
DISTINCT? COMMIT?? ROLLBACK
DDL statements include CREATE, ALTER, and DROP
DML statements include SELECT, INSERT, and UPDATE
ALTER TABLE tablen ADD/MODIFY/DROP ....
?
ALTER TABLE personal_info
? ADD CONSTRAINT personal_info_pkey
? PRIMARY KEY (employee_id);
-- disable a constraint
ALTER TABLE personal_info
? DISABLE CONSTRAINT personal_info_check_con;
ALTER TABLE personal_info
? ADD CONSTRAINT personal_info_fkey
? FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
? ON DELETE CASCADE;
-- add a unique constraint
ALTER TABLE personal_info
? ADD CONSTRAINT personal_info_unique_con
? UNIQUE (social_security_id);
-- add a check constraint
ALTER TABLE personal_info
? ADD CONSTRAINT personal_info_check_con
? CHECK ( dependents_claimed > 0);
-- enable a constraint
ALTER TABLE personal_info
? ENABLE CONSTRAINT personal_info_check_con;
-- drop a constraint
ALTER TABLE personal_info
? DROP CONSTRAINT personal_info_check_con;
NVL2?? NVL
ALTER TABLE my_birthdays RENAME to birthdays;
-- create an index on a single column to make queries faster on that column
CREATE INDEX emp_hiredate_idx ON employees (hire_date);
-- rename the index
ALTER INDEX emp_hiredate_idx
? RENAME TO emp_hire_date_idx;
-- drop the index
DROP INDEX emp_hire_date_idx;
-- create an index on two columns to make queries faster on the first column
-- or both columns
CREATE INDEX emp_mgr_id_ix ON employees (employee_id, manager_id);
DROP INDEX emp_mgr_id_ix;
-- a function-based index precalculates the result and speeds up queries that
-- use the function for searching or sorting, in this case UPPER(last_name)
CREATE INDEX emp_upper_last_name_ix ON employees (UPPER(last_name));
DROP INDEX emp_upper_last_name_ix;
CREATE OR REPLACE VIEW my_emp_view AS
SELECT d.department_id, d.department_name, d.manager_id,
? e.employee_id, e.first_name, e.last_name
? FROM employees e, departments d
? WHERE d.manager_id = e.employee_id;
?CURRVAL and NEXTVAL
CREATE SEQUENCE new_employees_seq START WITH 1000 INCREMENT BY 1;
DECODE(job_id, 'PU_CLERK', salary*1.05,--IF THEN? ELSE
???????????????????????????? salary) "Revised Salary" FROM employees;
CREATE SYNONYM emps for HR.employees;
PL/SQL:anonymous blocks, stored functions, stored procedures, and packages
DBMS_OUTPUT.PUT_LINE
DECLARE
? monthly_salary???????? NUMBER(6);
? number_of_days_worked? NUMBER(2);
? pay_per_day??????????? NUMBER(6,2);
-- the following is the executable part, from BEGIN to END
BEGIN
? monthly_salary := 2290;
? number_of_days_worked := 21;
? pay_per_day := monthly_salary/number_of_days_worked;
-- the following displays output from the PL/SQL block
? DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));
-- the following is an optional exception part that handles errors
EXCEPTION
? WHEN ZERO_DIVIDE THEN
????? pay_per_day := 0;
END;
/
CONSTANT? :=
use the special qualifiers %TYPE and %ROWTYPE to declare variables that hold table columns or table rows.
IF-THEN, IF-THEN-ELSE, or IF-THEN-ELSEIF-ELSE
EXIT-WHEN
-- use WHILE LOOP to process data
? WHILE i <= 10 LOOP
??? i_cubed := i**3;
??? DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i)
?????????????????????? || ' Cube: ' || TO_CHAR(i_cubed));
??? i := i + 1;
? END LOOP;
? FOR loop_counter IN 1..10 LOOP
??? DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter)
????????????????????????? || ' Square: ' || TO_CHAR(loop_counter**2));
? END LOOP;
?? GOTO print_now;
??? END IF;
? END LOOP;
? p := ' is a prime number';
<<print_now>>
IN? OUT IN OUT
A
?ALL, ALTER, AND, ANY, ARRAY, ARROW, AS, ASC, AT
?
B
?BEGIN, BETWEEN, BY
?
C
?CASE, CHECK, CLUSTERS, CLUSTER, COLAUTH, COLUMNS, COMPRESS, CONNECT, CRASH, CREATE, CURRENT
?
D
?DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DISTINCT, DROP
?
E
?ELSE, END, EXCEPTION, EXCLUSIVE, EXISTS
?
F
?FETCH, FORM, FOR, FROM
?
G
?GOTO, GRANT, GROUP
?
H
?HAVING
?
I
?IDENTIFIED, IF, IN, INDEXES, INDEX, INSERT, INTERSECT, INTO, IS
?
L
?LIKE, LOCK
?
M
?MINUS, MODE
?
N
?NOCOMPRESS, NOT, NOWAIT, NULL
?
O
?OF, ON, OPTION, OR, ORDER,OVERLAPS
?
P
?PRIOR, PROCEDURE, PUBLIC
?
R
?RANGE, RECORD, RESOURCE, REVOKE
?
S
?SELECT, SHARE, SIZE, SQL, START, SUBTYPE
?
T
?TABAUTH, TABLE, THEN, TO, TYPE
?
U
?UNION, UNIQUE, UPDATE, USE
?
V
?VALUES, VIEW, VIEWS
?
W
?WHEN, WHERE, WITH
?
?
A
?ACCESS, ADD*, ALL*, ALTER*, AND*, ANY*, AS*, ASC*, AUDIT
?
B
?BETWEEN*, BY*
?
C
?CHAR*, CHECK*, CLUSTER, COLUMN, COMMENT, COMPRESS, CONNECT*, CREATE*, CURRENT*
?
D
?DATE*, DECIMAL*, DEFAULT*, DELETE*, DESC*, DISTINCT*, DROP*
?
E
?ELSE*, EXCLUSIVE, EXISTS
?
F
?FILE, FLOAT*, FOR*, FROM*
?
G
?GRANT*, GROUP*
?
H
?HAVING*
?
I
?IDENTIFIED, IMMEDIATE*, IN*, INCREMENT, INDEX, INITIAL, INSERT*, INTERSECT*, INTO*, IS*
?
L
?LEVEL*, LIKE*, LOCK, LONG
?
M
?MAXEXTENTS, MINUS, MLSLABEL, MODE, MODIFY
?
N
?NOAUDIT, NOCOMPRESS, NOT*, NOWAIT, NULL*, NUMBER
?
O
?OF*, OFFLINE, ON*, ONLINE, OPTION*, OR*, ORDER*
?
P
?PCTREE, PRIOR*, PRIVLEGES*, PUBLIC*
?
R
?RAW, RENAME, RESOURCE, REVOKE*, ROW, ROWID, ROWNUM, ROWS*
?
S
?SELECT*, SESSION*, SET*, SHARE, SIZE*, SMALLINT*, START, SUCCESSFUL, SYNONYM, SYSDATE
?
T
?TABLE*, THEN*, TO*, TRIGGER
?
U
?UID, UNION*, UNIQUE*, UPDATE*, USER*
?
V
?VALIDATE, VALUES*, VARCHAR*, VARCHAR2, VIEW*
?
W
?WHENEVER*, WHERE, WITH*
?
&my_variable &&my_variable DEFINE &my_variable="aaa"
V$RESERVED_WORDS
SELECT * FROM USER_OBJECTS
DESCRIBE wordcount
GRANT/REVOKE EXECUTE ON wordcount TO/FROM scoot
SELECT table_name, grantee,privilege FROM USER_TAB_PRIVS_MADE ;
EXEC DBMS_OUTPUT.PUT_LINE(booktitle('00-908-77889-0'))
HEADER
IS
?Declaration Section
BEGIN?
?Execution Section
EXCEPTION
?Exception Section
END;
DESC SYS.STANDARD
SELECT * FROM V$RESERVED_WORDS; GET RESERVED WORDS OF ORACLE
?
update standarditem set 規(guī)格=replace(規(guī)格,'','')
什么是索引
索引是建立在表的一列或多個(gè)列上的輔助對(duì)象，目的是加快訪問(wèn)表中的數(shù)據(jù)；
Oracle存儲(chǔ)索引的數(shù)據(jù)結(jié)構(gòu)是B*樹(shù)，位圖索引也是如此，只不過(guò)是葉子節(jié)點(diǎn)不同B*數(shù)索引；
索引由根節(jié)點(diǎn)、分支節(jié)點(diǎn)和葉子節(jié)點(diǎn)組成，上級(jí)索引塊包含下級(jí)索引塊的索引數(shù)據(jù)，葉節(jié)點(diǎn)包含索引數(shù)據(jù)和確定行實(shí)際位置的rowid。
使用索引的目的
加快查詢速度
減少I(mǎi)/O操作
消除磁盤(pán)排序
何時(shí)使用索引
查詢返回的記錄數(shù)
排序表<40%
非排序表 <7%
表的碎片較多（頻繁增加、刪除）
索引的種類
非唯一索引（最常用）
唯一索引
位圖索引
局部有前綴分區(qū)索引
局部無(wú)前綴分區(qū)索引
全局有前綴分區(qū)索引
散列分區(qū)索引
基于函數(shù)的索引
管理索引的準(zhǔn)則
在表中插入數(shù)據(jù)后創(chuàng)建索引
。在用SQL*Loader或import工具插入或裝載數(shù)據(jù)后，建立索引比較有效；
索引正確的表和列
。經(jīng)常檢索排序大表中40%或非排序表7%的行，建議建索引；
。為了改善多表關(guān)聯(lián)，索引列用于聯(lián)結(jié)；
。列中的值相對(duì)比較唯一；
。取值范圍（大：B*樹(shù)索引，小：位圖索引）；
。Date型列一般適合基于函數(shù)的索引；
。列中有許多空值，不適合建立索引
為性能而安排索引列
。經(jīng)常一起使用多個(gè)字段檢索記錄，組合索引比單索引更有效；
。把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where條件中使用groupid或groupid,serv_id，查詢將使用索引，若僅用到serv_id字段，則索引無(wú)效；
。合并/拆分不必要的索引。
限制每個(gè)表索引的數(shù)量
。一個(gè)表可以有幾百個(gè)索引（你會(huì)這樣做嗎？），但是對(duì)于頻繁插入和更新表，索引越多系統(tǒng)CPU，I/O負(fù)擔(dān)就越重；
。建議每張表不超過(guò)5個(gè)索引。
刪除不再需要的索引
。索引無(wú)效，集中表現(xiàn)在該使用基于函數(shù)的索引或位圖索引，而使用了B*樹(shù)索引；
。應(yīng)用中的查詢不使用索引；
。重建索引之前必須先刪除索引，若用alter index … rebuild重建索引，則不必刪除索引。
索引數(shù)據(jù)塊空間使用
。創(chuàng)建索引時(shí)指定表空間，特別是在建立主鍵時(shí)，應(yīng)明確指定表空間；
。合理設(shè)定pctfress，注意：不能給索引指定pctused；
。估計(jì)索引的大小和合理地設(shè)置存儲(chǔ)參數(shù)，默認(rèn)為表空間大小，或initial與next設(shè)置成一樣大。
考慮并行創(chuàng)建索引
。對(duì)大表可以采用并行創(chuàng)建索引，在并行創(chuàng)建索引時(shí)，存儲(chǔ)參數(shù)被每個(gè)查詢服務(wù)器進(jìn)程分別使用，例如：initial為1M，并行度為8，則創(chuàng)建索引期間至少要消耗8M空間；
考慮用nologging創(chuàng)建索引
。對(duì)大表創(chuàng)建索引可以使用nologging來(lái)減少重做日志；
。節(jié)省重做日志文件的空間；
?？s短創(chuàng)建索引的時(shí)間；
。改善了并行創(chuàng)建大索引時(shí)的性能。
怎樣建立最佳索引
明確地創(chuàng)建索引
Code:
create index index_name on table_name(field_name)
? tablespace tablespace_name
? pctfree 5
? initrans 2
? maxtrans 255
? storage
? (
? minextents 1
? maxextents 16382
? pctincrease 0
? );
創(chuàng)建基于函數(shù)的索引
。常用與UPPER、LOWER、TO_CHAR(date)等函數(shù)分類上，例：
Code:
create index idx_func on emp (UPPER(ename)) tablespace
?????? tablespace_name;
創(chuàng)建位圖索引
。對(duì)基數(shù)較小，且基數(shù)相對(duì)穩(wěn)定的列建立索引時(shí)，首先應(yīng)該考慮位圖索引，例：
Code:
create bitmap index idx_bitm on class (classno) tablespace
????? tablespace_name;
明確地創(chuàng)建唯一索引
。可以用create unique index語(yǔ)句來(lái)創(chuàng)建唯一索引，例：
Code:
create unique index dept_unique_idx on dept(dept_no) tablespace
?????? idx_1;
創(chuàng)建與約束相關(guān)的索引
。可以用using index字句，為與unique和primary key約束相關(guān)的索引，例如：
Code:
alter table table_name
? add constraint PK_primary_keyname primary key (field_name)
? using index tablespace tablespace_name；
如何創(chuàng)建局部分區(qū)索引
?；A(chǔ)表必須是分區(qū)表；
。分區(qū)數(shù)量與基礎(chǔ)表相同；
。每個(gè)索引分區(qū)的子分區(qū)數(shù)量與相應(yīng)的基礎(chǔ)表分區(qū)相同；
?；A(chǔ)表的子分區(qū)中的行的索引項(xiàng)，被存儲(chǔ)在該索引的相應(yīng)的子分區(qū)中,例如:
Code:
Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
? Pctfree 5
? Tablespace TBS_AK01_IDX
? Storage (
? MaxExtents 32768
? PctIncrease 0
? FreeLists 1
? FreeList Groups 1
? )
? local
? /
如何創(chuàng)建范圍分區(qū)的全局索引
。基礎(chǔ)表可以是全局表和分區(qū)表。
create index idx_start_date on tg_cdr01(start_date)
global partition by range(start_date)
(partition p01_idx vlaues less than (‘0106’)
partition p01_idx vlaues less than (‘0111’)
…
partition p01_idx vlaues less than (‘0401’ ))
/
重建現(xiàn)存的索引
重建現(xiàn)存的索引的當(dāng)前時(shí)刻不會(huì)影響查詢；
重建索引可以刪除額外的數(shù)據(jù)塊；
提高索引查詢效率；
alter index idx_name rebuild nologging;
對(duì)于分區(qū)索引：
alter index idx_name rebuild partition partiton_name nologging;
要?jiǎng)h除索引的原因
。不再需要的索引；
。索引沒(méi)有針對(duì)其相關(guān)的表所發(fā)布的查詢提供所期望的性能改善；
。應(yīng)用沒(méi)有用該索引來(lái)查詢數(shù)據(jù)；
。該索引無(wú)效，必須在重建之前刪除該索引；
。該索引已經(jīng)變的太碎了，必須在重建之前刪除該索引；
。語(yǔ)句：drop index idx_name;drop index idx_name drop partition partition_name;
建立索引的代價(jià)
基礎(chǔ)表維護(hù)時(shí)，系統(tǒng)要同時(shí)維護(hù)索引，不合理的索引將嚴(yán)重影響系統(tǒng)資源，主要表現(xiàn)在CPU和I/O上；
插入、更新、刪除數(shù)據(jù)產(chǎn)生大量db file sequential read鎖；
SQL優(yōu)化器簡(jiǎn)介
基于規(guī)則的優(yōu)化器
?？偸鞘褂盟饕?
?？偸菑尿?qū)動(dòng)表開(kāi)始（from子句最右邊的表）
。只有在不可避免的情況下，才使用全表掃描
。任何索引都可以
基于成本的優(yōu)化器
。需要表、索引的統(tǒng)計(jì)資料
Analyze table customer compute statistics;
Analyze table customer estimate statistics sample 5000 rows;
。表中設(shè)置并行度、表分區(qū)
優(yōu)化器模式
rule模式
?？偤雎訡BO和統(tǒng)計(jì)信息而基于規(guī)則
choose模式
。Oracle根據(jù)情況選擇rule or first_rows or all_rows
first_rows 模式
。基于成本，以最快的速度返回記錄，會(huì)造成總體查詢速度的下降或消耗更多的資源，傾向索引掃描，適合OLTP系統(tǒng)
all_rows模式
?；诔杀荆_保總體查詢時(shí)間最短，傾向并行全表掃描
例如：
Select last_name from customer order by last_name;用first_rows時(shí)，迅速返回記錄，但I(xiàn)/O量大，用all_rows時(shí)，返回記錄慢，但使用資源少。
調(diào)整SQL表訪問(wèn)
全表掃描
。返回記錄：未排序表>40%，排序表>7%，建議采用并行機(jī)制來(lái)提高訪問(wèn)速度，DDS；
索引訪問(wèn)
。最常用的方法，包括索引唯一掃描和索引范圍掃描，OLTP；
快速完全索引掃描
。訪問(wèn)索引中所有數(shù)據(jù)塊，結(jié)果相當(dāng)于全表掃描，可以用索引掃描代替全表掃描，例如：
Select serv_id,count(* ) from tg_cdr01 group by serv_id;
評(píng)估全表掃描的合法性
如何實(shí)現(xiàn)并行掃描
。永久并行化（不推薦）
alter table customer parallel degree 8;
。單個(gè)查詢并行化
select /*+ full(emp) parallel(emp,8)*/ * from emp;
分區(qū)表效果明顯
優(yōu)化SQL語(yǔ)句排序
排序的操作：
。order by 子句
。group by 子句
。select distinct子句
。創(chuàng)建索引時(shí)
。union或minus
。排序合并連接
如何避免排序
。添加索引
。在索引中使用distinct子句
。避免排序合并連接
使用提示進(jìn)行調(diào)整
使用提示的原則
。語(yǔ)法：/*+ hint */
。使用表別名:select /*+ index(e dept_idx)*/ * from emp e
。檢驗(yàn)提示
常用的提示
。rule
。all_rows
。first_rows
。use_nl
。use_hash
。use_merge
。index
。index_asc
。no_index
。index_desc（常用于使用max內(nèi)置函數(shù)）
。index_combine(強(qiáng)制使用位圖索引)
。index_ffs（索引快速完全掃描）
。use_concat(將查詢中所有or條件使用union all)
。parallel
。noparallel
。full
。ordered（基于成本）
調(diào)整表連接
表連接的類型
。等連接
where 條件中用等式連接；
。外部連接（左、右連接）
在where條件子句的等式謂詞放置一個(gè)(+)來(lái)實(shí)現(xiàn)，例如：
select a.ename,b.comm from emp a,bonus b where a.ename=b.ename(+);
該語(yǔ)句返回所有emp表的記錄；
。自連接
Select a.value total, B.value hard, (A.value - b.value) soft ,
Round((b.value/a.value)*100,1) perc
From v$sysstat a,v$sysstat b
Where a.statistic# = 179
and B.statistic# = 180;
反連接
反連接常用于not in or not exists中，是指在查詢中找到的任何記錄都不包含在結(jié)果集中的子查詢；不建議使用not in or not exists;
。半連接
查詢中使用exists，含義：即使在子查詢中返回多條重復(fù)的記錄，外部查詢也只返回一條記錄。
嵌套循環(huán)連接
。被連接表中存在索引的情況下使用；
。使用use_nl。
hash連接
。Hash連接將驅(qū)動(dòng)表加載在內(nèi)存中，并使用hash技術(shù)連接第二個(gè)表，提高等連接速度。
。適合于大表和小表連接；
。使用use_hash。
排序合并連接
。排序合并連接不使用索引
。使用原則：
連接表子段中不存在可用索引；
查詢返回兩個(gè)表中大部分的數(shù)據(jù)快；
CBO認(rèn)為全表掃描比索引掃描執(zhí)行的更快。
。使用use_merge
使用臨時(shí)/中間表
多個(gè)大表關(guān)聯(lián)時(shí)，可以分別把滿足條件的結(jié)果集存放到中間表，然后用中間表關(guān)聯(lián)；
SQL子查詢的調(diào)整
關(guān)聯(lián)與非關(guān)聯(lián)子查詢
。關(guān)聯(lián)：子查詢的內(nèi)部引用的是外部表，每行執(zhí)行一次；
。非關(guān)聯(lián)：子查詢只執(zhí)行一次，存放在內(nèi)存中。
調(diào)整not in 和not exists語(yǔ)句
?？梢允褂猛獠窟B接優(yōu)化not in子句，例如：
Code:
select ename from emp where dept_no not in
? (select dept_no from dept where dept_name =‘Math’);
改為：
Code:
select ename from emp,dept
? where emp.dept_no=dept.dept_no
? and dept.dept_name is null;
使用索引調(diào)整SQL
Oracle 為什么不使用索引
。檢查被索引的列或組合索引的首列是否出現(xiàn)在PL/SQL語(yǔ)句的WHERE子句中，這是“執(zhí)行計(jì)劃”能用到相關(guān)索引的必要條件。
?？床捎昧四姆N類型的連接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在兩張表連接，且內(nèi)表的目標(biāo)列上建有索引時(shí)，只有Nested Loop才能有效地利用到該索引。SMJ即使相關(guān)列上建有索引，最多只能因索引的存在，避免數(shù)據(jù)排序過(guò)程。HJ由于須做HASH運(yùn)算，索引的存在對(duì)數(shù)據(jù)查詢速度幾乎沒(méi)有影響。
。看連接順序是否允許使用相關(guān)索引。假設(shè)表emp的deptno列上有索引，表dept的列deptno上無(wú)索引，WHERE語(yǔ)句有emp.deptno=dept.deptno條件。在做NL連接時(shí)，emp做為外表，先被訪問(wèn)，由于連接機(jī)制原因，外表的數(shù)據(jù)訪問(wèn)方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描。
。是否用到系統(tǒng)數(shù)據(jù)字典表或視圖。由于系統(tǒng)數(shù)據(jù)字典表都未被分析過(guò)，可能導(dǎo)致極差的“執(zhí)行計(jì)劃”。但是不要擅自對(duì)數(shù)據(jù)字典表做分析，否則可能導(dǎo)致死鎖，或系統(tǒng)性能下降。
。索引列是否函數(shù)的參數(shù)。如是，索引在查詢時(shí)用不上。
。是否存在潛在的數(shù)據(jù)類型轉(zhuǎn)換。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會(huì)自動(dòng)將字符型用to_number()函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致上一種現(xiàn)象的發(fā)生。
。是否為表和相關(guān)的索引搜集足夠的統(tǒng)計(jì)數(shù)據(jù)。對(duì)數(shù)據(jù)經(jīng)常有增、刪、改的表最好定期對(duì)表和索引進(jìn)行分析，可用SQL語(yǔ)句“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映實(shí)際的統(tǒng)計(jì)數(shù)據(jù)，才有可能做出正確的選擇。
。索引列的選擇性不高。 我們假設(shè)典型情況，有表emp，共有一百萬(wàn)行數(shù)據(jù)，但其中的emp.deptno列，數(shù)據(jù)只有4種不同的值，如10、20、30、40。雖然emp數(shù)據(jù)行有很多，ORACLE缺省認(rèn)定表中列的值是在所有數(shù)據(jù)行均勻分布的，也就是說(shuō)每種deptno值各有25萬(wàn)數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DEPTNO=10，利用deptno列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高。
。索引列值是否可為空（NULL）。如果索引列值可以是空值，在SQL語(yǔ)句中那些要返回NULL值的操作，將不會(huì)用到索引，如COUNT（*），而是用全表掃描。這是因?yàn)樗饕写鎯?chǔ)值不能為全空。
。看是否有用到并行查詢（PQO）。并行查詢將不會(huì)用到索引。
。如果從以上幾個(gè)方面都查不出原因的話，我們只好用采用在語(yǔ)句中加hint的方式強(qiáng)制ORACLE使用最優(yōu)的“執(zhí)行計(jì)劃”。 hint采用注釋的方式，有行注釋和段注釋兩種方式。 如我們想要用到A表的IND_COL1索引的話，可采用以下方式： “SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 = XXX;"
如何屏蔽索引
語(yǔ)句的執(zhí)行計(jì)劃中有不良索引時(shí)，可以人為地屏蔽該索引，方法：
。數(shù)值型：在索引字段上加0，例如
Code:
select * from emp where emp_no+0 = v_emp_no;
。字符型：在索引字段上加‘’，例如
Code:
select * from tg_cdr01 where msisdn||’’=v_msisdn;
?
[1] 基本的索引概念
??? 查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過(guò)USER_INDEXES的方法來(lái)檢索模式(schema)的索引。訪問(wèn)USER_IND_COLUMNS視圖可得到一個(gè)給定表中被索引的特定列。
[2] 組合索引
??? 當(dāng)某個(gè)索引包含有多個(gè)已索引的列時(shí)，稱這個(gè)索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引訪問(wèn)方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個(gè)組合索引鍵，該索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中對(duì)第一列（empno）指定一個(gè)值，否則就不能使用這個(gè)索引鍵進(jìn)行一次范圍掃描。
??? 特別注意：在Oracle9i之前，只有在使用到索引的前導(dǎo)索引時(shí)才可以使用組合索引！
[3] ORACLE ROWID
??? 通過(guò)每個(gè)行的ROWID，索引Oracle提供了訪問(wèn)單行數(shù)據(jù)的能力。ROWID其實(shí)就是直接指向單獨(dú)行的線路圖。如果想檢查重復(fù)值或是其他對(duì)ROWID本身的引用，可以在任何表中使用和指定rowid列。???
[4] 限制索引
??? 限制索引是一些沒(méi)有經(jīng)驗(yàn)的開(kāi)發(fā)人員經(jīng)常犯的錯(cuò)誤之一。在SQL中有很多陷阱會(huì)使一些索引無(wú)法使用。下面討論一些常見(jiàn)的問(wèn)題：
???
??? 4.1 使用不等于操作符（<>、!=）
??????? 下面的查詢即使在cust_rating列有一個(gè)索引，查詢語(yǔ)句仍然執(zhí)行一次全表掃描。
???????? select cust_Id,cust_name
???????? from?? customers
???????? where? cust_rating <> 'aa';
???????? 把上面的語(yǔ)句改成如下的查詢語(yǔ)句，這樣，在采用基于規(guī)則的優(yōu)化器而不是基于代價(jià)的優(yōu)化器（更智能）時(shí)，將會(huì)使用索引。
???????? select cust_Id,cust_name
???????? from?? customers
???????? where? cust_rating < 'aa' or cust_rating > 'aa';
???? 特別注意：通過(guò)把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。
????
???? 4.2 使用IS NULL 或IS NOT NULL
???????? 使用IS NULL 或IS NOT NULL同樣會(huì)限制索引的使用。因?yàn)镹ULL值并沒(méi)有被定義。在SQL語(yǔ)句中使用NULL會(huì)有很多的麻煩。因此建議開(kāi)???? 發(fā)人員在建表時(shí)，把需要索引的列設(shè)成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會(huì)使用這個(gè)索引（除非索引是一個(gè)位圖索???? 引，關(guān)于位圖索引在稍后在詳細(xì)討論）。
???? 4.3 使用函數(shù)
???????? 如果不使用基于函數(shù)的索引，那么在SQL語(yǔ)句的WHERE子句中對(duì)存在索引的列使用函數(shù)時(shí)，會(huì)使優(yōu)化器忽略掉這些索引。
???????? 下面的查詢不會(huì)使用索引（只要它不是基于函數(shù)的索引）
????????? select empno,ename,deptno
????????? from?? emp
????????? where? trunc(hiredate)='01-MAY-81';
????????? 把上面的語(yǔ)句改成下面的語(yǔ)句，這樣就可以通過(guò)索引進(jìn)行查找。
????????? select empno,ename,deptno
????????? from?? emp
????????? where? hiredate<(to_date('01-MAY-81')+0.9999);
???? 4.4 比較不匹配的數(shù)據(jù)類型
???????? 比較不匹配的數(shù)據(jù)類型也是比較難于發(fā)現(xiàn)的性能問(wèn)題之一。
???????? 注意下面查詢的例子，account_number是一個(gè)VARCHAR2類型，在account_number字段上有索引。下面的語(yǔ)句將執(zhí)行全表掃描。
???????? select bank_name,address,city,state,zip
???????? from?? banks
???????? where? account_number = 990354;
???????? Oracle可以自動(dòng)把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：
???????? select bank_name,address,city,state,zip
???????? from?? banks
???????? where? account_number ='990354';
???? 特別注意：不匹配的數(shù)據(jù)類型之間比較會(huì)讓Oracle自動(dòng)限制索引的使用，即便對(duì)這個(gè)查詢執(zhí)行Explain Plan也不能讓您明白為什么做了一?????????????? 次“全表掃描”。
[5] 選擇性
??? 使用USER_INDEXES視圖，該視圖中顯示了一個(gè)distinct_keys列。比較一下唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷索引的選擇性。選擇性越高，索引返回的數(shù)據(jù)就越少。
[6] 群集因子(Clustering Factor)
??? Clustering Factor位于USER_INDEXES視圖中。該列反映了數(shù)據(jù)相對(duì)于已索引的列是否顯得有序。如果Clustering Factor列的值接近于索引中的樹(shù)葉塊(leaf block)的數(shù)目，表中的數(shù)據(jù)就越有序。如果它的值接近于表中的行數(shù)，則表中的數(shù)據(jù)就不是很有序。
???
[7] 二元高度(Binary height)
??? 索引的二元高度對(duì)把ROWID返回給用戶進(jìn)程時(shí)所要求的I/O量起到關(guān)鍵作用。在對(duì)一個(gè)索引進(jìn)行分析后，可以通過(guò)查詢DBA_INDEXES的B-level列查看它的二元高度。二元高度主要隨著表的大小以及被索引的列中值的范圍的狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會(huì)增加。更新索引列也類似于刪除操作，因?yàn)樗黾恿艘褎h除鍵的數(shù)目。重建索引可能會(huì)降低二元高度。
[8] 快速全局掃描
??? 在Oracle7.3后就可以使用快速全局掃描(Fast Full Scan)這個(gè)選項(xiàng)。這個(gè)選項(xiàng)允許Oracle執(zhí)行一個(gè)全局索引掃描操作?？焖偃謷呙枳x取B-樹(shù)索引上所有樹(shù)葉塊。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)可以控制同時(shí)被讀取的塊的數(shù)目。
[9] 跳躍式掃描
??? 從Oracle9i開(kāi)始，索引跳躍式掃描特性可以允許優(yōu)化器使用組合索引，即便索引的前導(dǎo)列沒(méi)有出現(xiàn)在WHERE子句中。索引跳躍式掃描比全索引掃描要快的多。下面的程序清單顯示出性能的差別：
??? create index skip1 on emp5(job,empno);
??? index created.
??? select count(*)
??? from emp5
??? where empno=7900;
??? Elapsed:00:00:03.13
??? Execution Plan
??? 0???? SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
??? 1? 0??? SORT(AGGREGATE)
??? 2? 1????? INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)
??? Statistics
??? 6826 consistent gets
??? 6819 physical?? reads
??? select /*+ index(emp5 skip1)*/ count(*)
??? from emp5
??? where empno=7900;
??? Elapsed:00:00:00.56
??? Execution Plan
??? 0???? SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
??? 1? 0??? SORT(AGGREGATE)
??? 2? 1????? INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)
??? Statistics
??? 21 consistent gets
??? 17 physical?? reads
[10] 索引的類型
???? B-樹(shù)索引
???? 位圖索引
???? HASH索引
???? 索引編排表
???? 反轉(zhuǎn)鍵索引
???? 基于函數(shù)的索引
???? 分區(qū)索引
???? 本地和全局索引