oracle
oracle是國企里用得最多的關系型數(shù)據(jù)庫了，其對大并發(fā)纷纫、大訪問量支持力度更好；在企業(yè)級應用比例達到40%
作為java后端開發(fā)人員三妈，需要關注的oracle知識點：大概的執(zhí)行的流程畜埋；常接觸的對象；常用的sql操作畴蒲；執(zhí)行計劃悠鞍；索引及優(yōu)化；復雜的統(tǒng)計腳本
大概的執(zhí)行的流程
1. client請求oracle服務時模燥，建立基于TCP長連接的一個session咖祭，oracle單獨為該連接分配一個處理進程PID和內存區(qū)域PGA；
2. oracle判斷sql語句語法和對象權限是否有誤蔫骂；
3. oracle進行語法分析心肪，去SGA查找是否有該sql（哈希得到散列值）的執(zhí)行計劃。沒有時纠吴，基于成本或者優(yōu)先級的硬鞍，生成最優(yōu)的執(zhí)行計劃；以防后續(xù)復用戴已，緩存到SGA中的sharedpool固该；
4. 執(zhí)行執(zhí)行計劃，首先會去SGA中的database buffer cache（存儲數(shù)據(jù)塊糖儡，是oracle進行操作的最小單位）找數(shù)據(jù)伐坏，沒有的話從底層datafile中取數(shù)據(jù)并放入SGA中，便于后續(xù)復用握联；
5. 最后取到數(shù)據(jù)返回給client
會話查詢及死鎖查詢
在 Oracle 系統(tǒng)中能自動發(fā)現(xiàn)死鎖桦沉，并選擇代價最小的，即完成工作量最少的事務予以撤消金闽，釋放該事務所擁有的全部鎖
-- 查出鎖住該表的會話id,serial#
SELECT o.object_name,s.sid, s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
AND o.object_name='SJ_AFFAIR'
--查詢除了用戶的纯露，還包括系統(tǒng)內的對象
SELECT o.object_name,s.sid, s.serial#
FROM v$lock l, dba_objects o, v$session s
WHERE l.id1 = o.object_id
AND l.SID = s.sid
--刪除掉被鎖住的會話
alter system kill session 'sid, serial#';
查詢后oracle清除緩存
性能測試的時候，遇到第一次讀取數(shù)據(jù)庫很慢代芜，以后幾次都瞬間讀取完成埠褪。 應該是Oracle緩存的作用，第一次讀完以后放入緩存挤庇，以后讀取就很快了钞速。
ALTER SYSTEM FLUSH SHARED_POOL
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM FLUSH GLOBAL CONTEXT
sql中關鍵字的執(zhí)行順序
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY
常接觸的對象
表、視圖嫡秕、索引渴语；物化視圖、觸發(fā)器昆咽、存儲過程驾凶、函數(shù)屠升、DBLink；分區(qū)表狭郑；
table和index
-- 在要統(tǒng)計的時候來一次同步腹暖，調producer
begin dbms_stats.gather_table_stats(OWNNAME =>'CSID', TABNAME => 'dba_extents',METHOD_OPT => 'FOR ALL');
end;
--查看所有的對象
select t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.status
from dba_objects t
where t.OWNER = 'GTJAKH'
and t.OBJECT_TYPE = 'TABLE'
--table
select t.TABLE_NAME,
t.NUM_ROWS,--表記錄數(shù)
t.BLOCKS, --高水平線下的數(shù)據(jù)塊個數(shù)
t.EMPTY_BLOCKS,
t.AVG_SPACE,
t.CHAIN_CNT,
t.AVG_ROW_LEN--行平均長度
from user_tables t
where t.table_name = 'NRNG_NEWMATCH';
--查看表中index
select t.index_name,
t.num_rows,-- 索引行
t.leaf_blocks,--索引葉塊數(shù)
t.distinct_keys,--索引不同鍵數(shù)
t.blevel,--btree深度
t.avg_leaf_blocks_per_key,--
t.avg_data_blocks_per_key,
t.clustering_factor--索引的聚簇因子，標識表中存儲數(shù)據(jù)順序和索引字段順序的符合程度
from user_indexes t
where t.table_name = 'LOG_KHYW_BUSI_HISTORY'
and t.index_name = 'INDEX_KHYWHIST3_CREATTIME';
--或者查詢整個dba的dba_tables翰萨、dba_indexes
高水位線（HWM） 隨著表記錄增多脏答，存儲塊也增多，HWM也會升高亩鬼；但刪除數(shù)據(jù)（delete殖告，truncate不會）不會導致HWM降低，這樣全包掃描時雳锋，從頭一直讀到HWM線黄绩，會很耗時；所以需要table move操作玷过，以回收空間提高查詢效率爽丹；（以此衡量table是否要重構）
索引的聚簇因子clustering_factor，標識表中存儲數(shù)據(jù)順序和索引字段順序的符合程度辛蚊；值越接近表塊BLOCKS個數(shù)粤蝎，性能越好，越接近表行數(shù)NUM_ROWS袋马，性能越差（以此衡量索引的有效程度）
索引類型：
1. B樹索引初澎，normal
創(chuàng)建B－tree索引，即構建一個相同深度的二叉樹虑凛，索引值存儲在葉子節(jié)點--雙向鏈表碑宴；
適用于非稀疏值的列；精確查找桑谍、范圍查找延柠、模糊查找等
多級索引/級聯(lián)索引，慎選第一列（第1列會被解析器作為索引使用）
2. 函數(shù)索引
針對函數(shù)的霉囚，只能是單行返回的捕仔；具體值使用normal或者bitmap則由系統(tǒng)確定
3. 位圖索引匕积，bitmap
適用于稀疏值的索引盈罐，即對每個值創(chuàng)建一個所有記錄的位數(shù)組，0和1代表是否存在闪唆；命中時盅粪，根據(jù)begin rowid和end rowid和偏移量計算出記錄的rowid
位圖索引不適合并發(fā)環(huán)境，在并發(fā)環(huán)境下可能會造成大量事務的阻塞
不能在分區(qū)表上創(chuàng)建global位圖索引悄蕾，因為對分區(qū)表而言票顾，每個分區(qū)的物理存儲分開
5. 唯一索引础浮，unique
主鍵時默認創(chuàng)建的索引，使用B樹結構
oracle中index的unusable奠骄，usable 豆同，disable，enable
--如果出現(xiàn)unusable的話含鳞，需要進行重建
alter index index_name rebuild;
drop index index_name;
create index index_name on xxxxx;
--enable和disable僅僅只針對函數(shù)索引
alter index index_name enable;
alter index index_name disable;
Procedure存儲過程
封裝連續(xù)的動作影锈，手動控制事務，且處理異常
會預編譯蝉绷，效率較高
plsql提供組件進行調試/測試
function函數(shù)
函數(shù)相比存儲過程可以return值鸭廷，并在select中直接調用
解決嵌套查詢兩級以上不能使用最外層值的問題=》直接傳遞外部值為參數(shù)
materialized view 物化視圖
與一般視圖的區(qū)別：查詢已經執(zhí)行并將結果集存入了一張表中，好處是預先計算了查詢結果并且在特定查詢執(zhí)行的時候可以直接調取該結果熔吗。
適用于查詢匯總的辆床，考慮到速度的
占用存儲空間，并且可以設置索引桅狠，物理存儲為segment
``sql
--查看快照/物化視圖的刷新時間
select * from ALL_SNAPSHOT_REFRESH_TIMES;
--物化視圖
select * from dba_snapshots;
--日志物化視圖
select * from dba_snapshot_logs;
--刪除
drop snapshot log on cust_info;
create snapshot log on cust_info;
#### 觸發(fā)器trigger
> 觸發(fā)器類似過程和函數(shù)讼载，都有聲明、執(zhí)行和異常處理過程的PL/SQL塊中跌；
> 觸發(fā)器是由一個事件來啟動運行维雇，觸發(fā)器不能接收參數(shù)
### 常用的sql操作
> rownum和rowid；merge晒他；窗口函數(shù)吱型、行列互換、操作父子數(shù)據(jù)陨仅；
### 執(zhí)行計劃
> oracle生成執(zhí)行計劃基于兩種方式：
> 1津滞、RBO基于規(guī)則（根據(jù)oracle確定的具有優(yōu)先級的規(guī)則來計算，可以手動指定）灼伤；
> 2触徐、CBO基于成本（oracle目前采用，默認where語句之間獨立）
> 數(shù)據(jù)庫優(yōu)化器的目標：產生讓SQL執(zhí)行總成本最低的執(zhí)行計劃(CBO, cost based optimizer,基于成本)
> 成本：（根據(jù)對象(表狐赡、索引撞鹉、列)的統(tǒng)計信息計算出訪問成本）時間成本+從磁盤訪問1個數(shù)據(jù)塊的成本
#### 常見執(zhí)行計劃：
1\. TABLE ACCESS FULL 對于表小時有優(yōu)勢
2\. INDEX UNIQUE SCAN 等值查詢index（分層定位）
3\. INDEX RANGE SCAN 范圍查詢，針對葉子節(jié)點（有序颖侄、雙向鏈表）
4\. INDEX FAST FULL SCAN
1鸟雏、index比原table小览祖；2孝鹊、index是按塊訪問=》若要查詢非index的字段，則按rowid進行**回表操作**展蒂；
cost=索引高度（0~4又活，索引的特點決定大數(shù)據(jù)量表的查找不是問題）+回表（0~1）+聚集系數(shù)
回表操作苔咪，考慮index表和table的聚集情況，按照table的特點柳骄，如時間团赏，可對index進行排序后再回表操作
5\. INDEX FULL SCAN
絕大多數(shù)情況下，index fast full scan性能優(yōu)于index full scan耐薯，但前者在有order by時馆里，一定會存在對讀取的塊重新排序的過程
rowid掃描 oracle定位單行數(shù)據(jù)最快的方式
- TABLE ACCESS BY USER ROWID 直接根據(jù)rowid值獲取
- TABLE ACCESS BY INDEX ROWID 先根據(jù)index獲取rowid，再根據(jù)rowid獲取
6\. NESTED LOOPS
COST ≈驅動表訪問成本+驅動表返回記錄數(shù)*內部表訪問成本
7\. HASH JOIN
探測成本受驅動表返回結果集影響可柿，有3種情行：
1鸠踪、結果集可放到內存，探測成本可忽略
2复斥、One pass营密，不能一次性放到內存，探測成本≈驅動表返回結果集寫入與讀取成本+探測表返回結果集寫入與讀取成本
3目锭、Multi pass评汰，`探測成本≈驅動表返回結果集寫入與讀取成本+探測表返回結果集（寫入成本+讀取成本*次數(shù)）`
8\. MEGER JOIN
#### 執(zhí)行計劃結果分析
> 順序：從上往下，從左往右痢虹，直到葉子節(jié)點被去；執(zhí)行結果不斷上移到父節(jié)點
```sql
SELECT T.ID activityId,
T.ACTIVITY_NAME ACTIVITYNAME,
T.ACTIVITY_NO ACTIVITYNO,
T.BONUS_RULE bonusRule,
(SELECT T2.KEY_DESC
FROM CSSWEB_DICTIONARY T2
WHERE T2.PARENT_ID = 90
AND T1.PROVINCE_ID = T2.KEY_CODE) PROVINCENAME,
(SELECT TO_CHAR(WM_CONCAT(C.CHANNEL_CODE))
FROM JF_ACTIVITY_CHANNEL C
WHERE C.JF_ACTIVITY_ID = T.ID) CHANNELCODE,
T1.PROVINCE_ID PROVINCEID,
T.ACTIVITY_STATUS ACTIVITYSTATUS,
TO_CHAR(T.BEGIN_TIME, 'yyyy-MM-dd') BEGINTIME,
TO_CHAR(T.END_TIME, 'yyyy-MM-dd') ENDTIME,
T.ACTIVITY_TOTAL_AMOUNT TOTALAMOUNT,
T.ACTIVITY_TYPE ACTIVITYTYPE,
TO_CHAR(T.JF_OVERDUE_DATE, 'yyyy-MM-dd') JFOVERDUEDATE,
NVL(T.JF_TOTAL_FLAG,1) JFTOTALFLAG,
T.JF_TOTAL JFTOTAL,
NVL(O.HLWCT, 0) HLWCT,
NVL(O.ZYTG, 0) ZYTG,
O.TGDZ,
NVL(O.TGGX, 0) TGGX,
NVL(O.CZHF, 0) CZHF,
T.SYN_BUSI_FLAG AS synBusiFlag ,
case when t.parent_id=0 then to_char(t.parent_id) else (select tt.activity_name from JF_MONEY_ACTIVITY tt where tt.id=t.parent_id) end as parentName
FROM JF_MONEY_ACTIVITY T
LEFT JOIN JF_ACTIVITY_AREA T1
ON T.ID = T1.JF_ACTIVITY_ID
LEFT JOIN JF_ACTIVITY_OTHER O
ON O.JF_ACTIVITY_ID = T.ID
WHERE T.ID ='894'
索引及優(yōu)化
索引效率不高
原因：建立的字段如果經常增刪改，或者按需求清楚歷史數(shù)據(jù)奖唯，但刪除記錄對應的表和索引里占用的數(shù)據(jù)塊空間并沒有釋放
解決：
-
alter table tbl move;可以釋放已刪除記錄表占用的數(shù)據(jù)塊空間惨缆，整理碎片
-
-
alter index idx_tbl_col rebuild;重建索引可以釋放已刪除記錄索引占用的數(shù)據(jù)塊空間。重建索引不僅能增加索引表空間空閑空間大小丰捷，還能夠提高查詢性能
-
-
alter index idx_tbl_col rebuild online;加online坯墨，可以防止rebuild會阻塞一切DML操作
-
強制指定索引：
一張表上創(chuàng)建了非常多的索引（不推薦），每一個索引都是針對特定業(yè)務查詢而增加的病往。這極易導致SQL由于個別索引的引入出現(xiàn)性能問題捣染。自己的sql可能命中其中的索引，但不是自己想要的停巷，則使用Hint的方法實現(xiàn)強制SQL不走特定索引或強制使用
--使用hint強制使用或者不使用
select /*+ NO_INDEX(t t_idx1) */ object_name from t where object_name = 'T'
索引失效：
1耍攘、<>
2、單獨的>,<
3畔勤、like "%_" 百分號在前
4蕾各、單獨引用復合索引里非第一位置的索引列
5、字符型字段為數(shù)字時在where條件里不添加引號硼被，或者merge中的on條件=》主要問題示损，數(shù)據(jù)庫之間或者表之間的字段類型不一致，常用是number和char之間的比較
6嚷硫、對索引列進行運算.需要建立函數(shù)索引
7检访、not in ,not exist
8、當變量采用的是times變量仔掸，而表的字段采用的是date變量時.或相反情況
9脆贵、索引失效
10、基于cost成本分析(oracle因為走全表成本會更小)：查詢小表,或者返回值大概在10%以上起暮；會優(yōu)化走table access full
11卖氨、有時都考慮到了 但就是不走索引；可能index效率不高负懦，需要重建
12筒捺、B-tree索引 is null不會走，is not null會走纸厉；位圖索引 is null,is not null 都會走
13系吭、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會走；is null時颗品，其它索引列都要涉及
