本文只講最最平常最最簡單的索引 就是以create index ix on tx(a b c);形式創(chuàng)建的索引 而不講位圖索引 反向鍵索引 倒序索引 基于函數(shù)的索引等等 其實呢 只要是基于B樹的索引 不管是在Oracle Mysql 還是其它數(shù)據(jù)庫中 原理應(yīng)當都是一樣的
創(chuàng)新互聯(lián)建站專業(yè)為企業(yè)提供平桂網(wǎng)站建設(shè)、平桂做網(wǎng)站、平桂網(wǎng)站設(shè)計、平桂網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計與制作、平桂企業(yè)網(wǎng)站模板建站服務(wù)，十余年平桂做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡(luò)服務(wù)。
索引最重要的一個性質(zhì)應(yīng)該就是有序 索引中的每一項 是從左到右 從小到大 以嚴格的順序排列好的
下面的討論都以上面的索引ix(a b c)為例
把這棵索引的葉子節(jié)點畫到紙上 大概是這樣的
a a a an
b b b bn
c c c cn
上面這個 ×n的矩陣 每一列代表了一條記錄 同時這一列記錄 也對應(yīng)了表里的唯一一條記錄 當然 在Oracle里 對于non unique索引 需要補上rowid 才是真正唯一的 上面的索引相當于create unique index ix on tx(a b c rowid); 我們把這個細節(jié)忽略掉
把每一列看作一個向量 vi = (ai bi ci)
有序的含義就是
vi vj iff i j;
vi vj這么定義
(ai aj) or (ai = aj and bi bj) or (ai = aj and bi = bj and ci cj)
從這個基本性質(zhì) 我們可以得到一些其它性質(zhì)(為了打字方便 ai+k表示a(i+k) 而不是a(i)+k)
) 如果ai ai+ …… ai+k 都是相等的 那么
bi = bi+ = …… = bi+k
) 如果ai ai+ …… ai+k是相等的 而且bi bi+ …… bi+k也是相等的 那么
ci = ci+ = …… ci+k
但是從 ai ai+ …… ai+k相等 我們得不到
ci = ci+ = …… = ci+k這個結(jié)論
索引相關(guān)的很多問題 都和上面提到的這幾個性質(zhì)有關(guān)系
下面來看幾個常見的查詢:
q ) select * from tx where a = :va and b = :vb;
q ) select * from tx where b = :vb and c = :vc;
q ) select * from tx where a = :va and c = :vc;
q ) select * from tx where a = :va order by b;
q ) select * from tx where a = :va order by b c;
q ) select * from tx where a = :va order by c;
q ) select * from tx where a = :va order by b c desc;
q ) select * from tx where a = :va order by b desc c desc;
q ) select * from tx where a = :va and b = :vb
qa) select * from tx where a = :va and b = :vb
qb) select * from tx where a = :va and c = :vc
qc) select * from tx where a = :va and b = :vb order by c
大家可以考慮一下這些查詢各自會以怎樣的方式執(zhí)行 不同查詢之間有什么區(qū)別?
同樣 為什么在索引字段上作了函數(shù)運算之后 索引不可用?
考慮下面這個語句:
select * from tx where f(a) = :vfa;
首先 在字段 a上作了函數(shù)運算之后 排序的規(guī)則是否仍舊一樣? a b 與 f(a) f(b)是否等價?
其次 就算f(a)和a的排序規(guī)則一樣 但是索引塊中存的a 但是你傳給它的是經(jīng)過了函數(shù)運算的值:vfa 只有oracle知道函數(shù)f的反函數(shù)inv_f 并在vfa上做inv_f(:vfa)計算之后 才能通過索引的B樹結(jié)果進行查找
當然 現(xiàn)實中f可能不是顯示的 而是隱式的 如傳入?yún)?shù)和字段類型不匹配的情況下 Oracle可能在字段上作函數(shù)運算 從語句上可能看不出索引字段上被做了函數(shù)運算 但Oracle內(nèi)部已經(jīng)在字段上運用了函數(shù) 這樣也會導(dǎo)致索引不可用 這種情況下用hint強制使用索引也是沒用的
通過dbms_xplan display_cursor可以或許可以查看到這種隱式類型轉(zhuǎn)換
通過v$sql_bind_metadata應(yīng)當可以查看到每個綁定變量的類型
通過v$sql_bind_capture這個視圖甚至可以看到每個綁定變量具體的值 不要把bind_capture和bind peek搞混哦 而且這里bind_cature也不會每綁定一次變量就capture一次 不然對執(zhí)行量非常高 綁定頻繁的語句 capture以同樣頻率進行的話 開銷可能還是有點大的
上面講到了索引的有序性 下面來講講索引另外一個有趣的性質(zhì) 其實 我們完全可以把索引看作一張表 這張表包含和主表一樣多的記錄(如果不考慮null) 只不過每條記錄只有主表的部分字段 開個玩笑 我們是不是可以把索引叫做有序視圖呢?或者精確一點 有序物化視圖:)
那么 我在執(zhí)行一些查詢的時候 如果所有字段都包含在索引中 是不是只要訪問索引就可以了呢?
這些字段可以出現(xiàn)在select列表中 where條件中 order by字段中 也可以出現(xiàn)在兩個表連接時的連接條件中
那么 根據(jù)業(yè)務(wù)的需求 我們是不是可以設(shè)計或調(diào)整索引以減少對主表的訪問呢?或者 是不是可以適當?shù)恼{(diào)整應(yīng)用的設(shè)計或?qū)崿F(xiàn)來滿足索引呢?
同時 考慮到索引的有序性 是不是可以利用索引來避免排序呢?
當然 我們不能忽略null的存在 如果一條記錄在索引中的所有字段上都是null的 那么oracle是不會索引這條記錄的 比如如果記錄ri的ai bi ci字段都是null的 索引中是找不到這條記錄的 這會有什么問題呢?首先表中的記錄和索引中的記錄從數(shù)量上來說就不一樣了
考慮一下Oracle會怎樣執(zhí)行下面這個查詢:
select count(*) from tx;
這個呢 hint起作用了嗎?
select /*+ parallel(tx ) */ count(*) from tx;
大家可以測試一下 怎樣把count(*)這個操作并行化 從這里或許可以得到一些Oracle怎么處理hint的提示
最后 講一下Oracle CBO計算索引訪問成本的公式
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
這個公式相信很多地方可以找到(我是從cost base oracle fundamentals這本書里copy出來的) 簡單說一下我自己對這個公式的理解
blevel是索引樹的高度
leaf_blocks是索引的頁子節(jié)點的個數(shù)
effective index selectivity (eis)怎么算呢?
還是舉幾個例子
where a = :va and b = :vb c = :vc
這里eis是 (selectivity a) * (selectivity b) * (selectivity c)
where a = :va and c = :vc
這里eis是 selectivity a
where b = :vb and c = :vc
這里eis是
where a = :va and b = :vb and c = :vc
這里eis是 (selectivity a) * (selectivity range b)
就是說按索引字段的順序 第一個不在where條件中出現(xiàn)的字段 或者第一個做了范圍運算的字段 之后出現(xiàn)的字段的selectivity是不能乘到effective index selectivity里去的
簡單的說 ceiling(leaf_blocks * effective index selectivity)表示的是Oracle需要訪問的索引葉子節(jié)點的個數(shù)
clustering_factor表示的是按索引的順序 從頭走到尾 需要訪問多少次數(shù)據(jù)塊 這里需要考慮到Oracle的一個優(yōu)化 如果連續(xù)n條記錄在同一個表塊中 那么oracle認為只需要訪問一次數(shù)據(jù)塊
那么clustering_factor的值的范圍就很容易確定了 cf = table blocks and cf = rows in index
effective table selectivity 這個計算就容易了 把索引中所有字段的selectivity乘起來就可以了
如果查詢中還有其它條件 比如 d = :vd and e = :ve 但是d e這些字段又不在索引中 那么在這些列上的過濾條件 需要回表后把這些值取出來才能判斷 所以d e這些列的selectivity是不能乘到effective table selectivity里去的
ceiling(clustering_factor * effective table selectivity)表示需要回表的次數(shù)
所以上面索引訪問的cost就是走某個索引 需要訪問的數(shù)據(jù)塊的個數(shù)
當然 前面的討論忽略了index skip scan這種情況 因為本人對index skip scan也不是很明白
什么情況下會走skip scan?
select * from tx where a = :va and c = :vc 是不是會在c這個字段上也作一個skip scan呢?
lishixinzhi/Article/program/Oracle/201311/16730
你這個是聯(lián)合索引，a2索引本身
它是當你有前導(dǎo)列時，條件中只有前導(dǎo)列這個條件時也會用到組合索引，不用兩個列作為條件。
比如
(A,B)建了組合索引
當WHERE A='sf'時會用到索引的
而WHERE B='sd'是不會用到組合索引的。
如果聯(lián)合索引中的多個字段都在where謂詞中出現(xiàn)了，則聯(lián)合索引效率比單列索引高，因為通過多個條件可以從索引中過濾得到更少的記錄條數(shù)，也就減少了需要回表掃描的次數(shù)，甚至可以直接在聯(lián)合索引中得到所查的所有結(jié)果，則不再需要回表。
表創(chuàng)建標準語法:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] , …);
--設(shè)計要求：建立一張用來存儲學(xué)生信息的表，表中的字段包含了學(xué)生的學(xué)號、姓名、年齡、入學(xué)日期、年級、班級、email等信息，
--并且為grade指定了默認值為1，如果在插入數(shù)據(jù)時不指定grade得值，就代表是一年級的學(xué)生
--DML是不需要commit的，隱式事務(wù)
create table student
(
stu_id number(10),
name varchar2(20),
age number(2),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);
-- 注意日期格式要轉(zhuǎn)換，不能是字符串，varchar2類型要用引號，否則出現(xiàn)類型匹配
--DML 需要收到commit
insert into student values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'2','1',' 123@qq.com ');
insert into student(stu_id,name,age,hiredate,classes,email) values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'1',' 1234@qq.com ');
select * from student;
-- 給表添加列，添加新列時不允許為not null,因為與舊值不兼容
alter table student add address varchar(100);
-- 刪除列
alter table student drop column address;
--修改列
alter table student modify(email varchar2(100));
正規(guī)表設(shè)計使用power disinger
--表的重命名
rename student to stu;
-- 表刪除
drop table stu;
**
在刪除表的時候，經(jīng)常會遇到多個表關(guān)聯(lián)的情況(外鍵)，多個表關(guān)聯(lián)的時候不能隨意刪除，使用如下三種方式:
2.表的約束（constraint）
約束:創(chuàng)建表時，指定的插入數(shù)據(jù)的一些規(guī)則
約束是在表上強制執(zhí)行的數(shù)據(jù)校驗規(guī)則
Oracle 支持下面五類完整性約束:
1). NOT NULL 非空約束 ---- 插入數(shù)據(jù)時列值不能空
2). UNIQUE Key 唯一鍵約束 ----限定列唯一標識，唯一鍵的列一般被用作索引
3). PRIMARY KEY 主鍵約束 ----唯一且非空，一張表最好有主鍵，唯一標識一行記錄
4). FOREIGN KEY 外鍵約束---多個表間的關(guān)聯(lián)關(guān)系，一個表中的列值，依賴另一張表某主鍵或者唯一鍵
-- 插入部門編號為50的，部門表并沒有編號為50的，報錯
insert into emp(empno,ename,deptno) values(9999,'hehe',50);
5). CHECK 自定義檢查約束---根據(jù)用戶需求去限定某些列的值，使用check約束
-- 添加主鍵約束/not null約束/check約束/唯一鍵約束
create table student
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age0 and age126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
);
-- 添加外鍵約束
create table stu
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age0 and age126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
FOREIGN KEY(deptno) references dept(deptno)
);
-- 創(chuàng)建表時沒添加外鍵約束 也可以修改 其中fk_0001為外鍵名稱
alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);
索引創(chuàng)建有兩種方式:
組合索引：多個列組成的索引
--索引：加快數(shù)據(jù)剪碎
create index i_ename on emp(ename);
--當創(chuàng)建某個字段索引后，查詢某個字段會自動使用到索引
select * from emp where ename = 'SMITH';
--刪除索引 索引名稱也是唯一的
drop index i_ename;
一些概念：
回表：
覆蓋索引
組合索引
最左匹配
使用oracle10g的朋友可能會發(fā)現(xiàn)以前刪除的表在數(shù)據(jù)庫中出現(xiàn)了很多垃圾表，如：BINjR8PK5HhrrgMK8KmgQ9nw==之類的表無法刪除無法用delete 刪除,一般不影響正常使用，但是有以下情況使我們必須刪除它
1,這些表占用空間
2,如果使用Middlegen-Hibernate-r5的朋友會發(fā)現(xiàn)一些問題，不你象以前用oracle9時那么順的生成hibernate配置文件,原因就在這里將它刪除就沒有問題了．
3,其它情況
BINjR8PK5HhrrgMK8KmgQ9nw==之類的表這個是10g 的新特性。
Drop Table 后，沒有真正的刪除表，而是在“垃圾站”中了?？梢酝ㄟ^：
SQL SHOW RECYCLEBIN
看到。如果要徹底刪除，使用：
SQL PURGE TABLE "BINjR8PK5HhrrgMK8KmgQ9nw==";
刪除這一個或
SQL PURGE RECYCLEBIN;
刪除全部。
重慶分公司
重慶分公司
