oraclesql調優

㈠ oracle sql性能優化需注意哪些

1，sql的寫法，有很多資料，不一一列舉
比如 >= , <= 在一起的時候，直接用between and 等等。。。
2，加index

㈡ oracle資料庫調優從哪幾個方面入手

簡單點的：
1, 修改sql查詢條件、如果使用函數，盡量不要把函數載入欄位上。
比如：to_char(aaa,'yyyy/mm/dd') = '2018/03/19' X
aaa = to_date('2018/03/19','yyyy/mm/dd')
2,參照sql查詢條件，給欄位增加索引。
3,看sql的執行計劃，時間cost。
4,表分區管理（DBA級別，個人不會）

㈢ oracle怎麼根據執行計劃調優sql

rdb和aof兩種方法都用上，多重保險。 appendfsync設為always。// 單節點測性能，連續100000次算平均時間，和per second比對，性能損失不大。 性能會有些許損失，但任務執行為非同步操作，無需用戶同步等待，為了保證數據安全，這樣是值得的。

㈣ Oracle等資料庫數據量特別大的時候怎樣從程序和SQL語句方面優化使查詢速度加快

一般最常用的大數據量優化：
1、創建分區表，使查詢時的大表盡量分割成小表。Oracle提供范圍分區、列表分區、Hash分區以及復合分區，具體選擇哪種分區最優，需要根據你的業務數據來確定。
2、創建索引，創建合適的索引可以大大提高查詢速度。但是你的這張大表如果會頻繁的進行update、insert等操作，索引會導致這些操作變慢。就有可能需要進行動態索引的使用。
3、優化復雜SQL；對復雜的SQL進行合理的優化，這個有時候也需要根據你的數據情況來優化，可以參考一些SQL語句優化方面的文檔。

㈤ 如何設置使oracle10g性能最優 性能調優 步驟

一、 磁碟方面調優
1. 規范磁碟陣列
RAID 10比RAID5更適用於OLTP系統，RAID10先鏡像磁碟，再對其進行分段，由於對數據的小規模訪問會比較頻繁，所以對OLTP適用。而RAID5,優勢在於能夠充分利用磁碟空間，並且減少陣列的總成本。但是由於陣列發出一個寫入請求時，必須改變磁碟上已修改的塊，需要從磁碟上讀取「奇偶校驗」塊，並且使用已修改的塊計算新的奇偶校驗塊，然後把數據寫入磁碟，且會限制吞吐量。對性能有所影響，RAID5適用於OLAP系統。

2. 數據文件分布
分離下面的東西,避免磁碟競爭
Ø SYSTEM表空間
Ø TEMPORARY表空間
Ø UNDO表空間
Ø 聯機重做日誌（放在最快的磁碟上）
Ø 操作系統磁碟
Ø ORACLE安裝目錄
Ø 經常被訪問的數據文件
Ø 索引表空間
Ø 歸檔區域（應該總是與將要恢復的數據分離）
例：
² /: System
² /u01: Oracle Software
² /u02: Temporary tablespace, Control file1
² /u03: Undo Segments, Control file2
² /u04: Redo logs, Archive logs, Control file4
² /u05: System, SYSAUX tablespaces
² /u06: Data1 ,control file3
² /u07: Index tablespace
² /u08: Data2
通過下列語句查詢確定IO問題
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;

3. 增大日誌文件

u 增大日誌文件的大小，從而增加處理大型INSERT,DELETE,UPDATE操作的比例
查詢日誌文件狀態
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查詢日誌切換時間
select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes
from v$log_history a ,v$log_history b
where a.RECID=b.RECID+1
order by a.FIRST_TIME desc

增大日誌文件大小，以及對每組增加日誌文件（一個主文件、一個多路利用文件）
u 增大LOG_CHECKPOINT_INTERVAL參數，現已不提倡使用它
如果低於每半小時切換一次日誌，就增大聯機重做日誌大小。如果處理大型批處理任務時頻繁進行切換，就增大聯機重做日誌數目。
alter database add logfile member 『/log.ora』 to group 1;
alter database drop logfile member 『/log.ora』;

4. UNDO表空間
修改三個初始參數：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=<#of minutes>

5. 不要在系統表空間中執行排序

二、 初始化參數調優
32位的定址最大支持應該是2的32次方，就是4G大小。但實際中32位系統（XP，windows2003等MS32位系統, ubuntu等linux32 位系統）要能利用4G內存，都是採用內存重映射技術。需要主板及系統的支持。如果關閉主板BIOS的重映射功能，系統將不能利用4G內存，可能只達3.5G.而在windows下看到的一般為3.25G。所以SGA設置為內存的40%，但不能超過3.25G
1. 重要初始化參數
l SGA_MAX_SIZE
l SGA_TARGET
l PGA_AGGREGATE_TARGET
l DB_CACHE_SIZE
l SHARED_POOL_SIZE

2. 調整DB_CACHE_SIZE來提高性能
它設定了用來存儲和處理內存中數據的SGA區域大小，從內存中取數據比磁碟快10000倍以上
根據以下查詢出數據緩存命中率
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio
from v$sysstat;
一個事務處理程序應該保證得到95%以上的命中率，命中率從90%提高到98%可能會提高500%的性能，ORACLE正在通過CPU或服務時間與等待時間來分析系統性能，不太重視命中率，不過現在的庫緩存和字典緩存仍將命中率作為基本的調整方法。

在調整DB_CACHE_SIZE時使用V$DB_CACHE_ADVICE
select size_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT';

如果查詢的命中率過低，說明缺少索引或者索引受到限制，通過V$SQLAREA視圖查詢執行緩慢的SQL

3. 設定DB_BLOCK_SIZE來反映數據讀取量大小
OLTP一般8K
OLAP一般16K或者32K

4. 調整SHARED_POOL_SIZE以優化性能

正確地調整此參數可以同等可能地共享SQL語句，使得在內存中便能找到使用過的SQL語句。為了減少硬解析次數，優化對共享SQL區域的使用，需盡量使用存儲過程、使用綁定變數

保證數據字典緩存命中率在95%以上
select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio
from v$rowcache
where gets+getmisses <>0;

如果命中率小於 99％，就可以考慮增加shared pool 以提高library cache 的命中率

SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;

通常規則是把它定為DB_CACHE_SIZE大小的50%－150%，在使用了大量存儲過程或程序包，但只有有限內存的系統里，最後分配為150%。在沒有使用存儲過程但大量分配內存給DB_CACHE_SIZE的系統里，這個參數應該為10%－20%

5. 調整PGA_AGGREGATE_TARGET以優化對內存的應用
u OLTP ：totalmemory*80%*20%
u DSS: totalmemory*80%*50%

6. 25個重要初始化參數
² DB_CACHE_SIZE：分配給數據緩存的初始化內存
² SGA_TARGET：使用了自動內存管理，則設置此參數。設置為0可禁用它
² PGA_AGGREGATE_TARGET：所有用戶PGA軟內存最大值
² SHARED_POOL_SIZE：分配給數據字典、SQL和PL／SQL的內存
² SGA_MAX_SIZE：SGA可動態增長的最大內存
² OPTIMIZER_MODE：
² CURSOR_SHARING：把字面SQL轉換成帶綁定變更的SQL，可減少硬解析開銷
² OPTIMIZER_INDEX_COST_ADJ：索引掃描成本和全表掃描成本進行調整，設定在1－10間會強制頻繁地使用索引，保證索引可用性
² QUERY_REWRITE_ENABLED：用於啟用具體化視圖和基於函數的索引功能
² DB_FILE_MULTIBLOCK_READ_COUNT：對於全表掃描，為了更有效執行IO，此參數可在一次IO中讀取多個塊
² LOG_BUFFER：為內存中沒有提交的事務分配緩沖區（非動態參數）
² DB_KEEP_CACHE_SIZE：分配給KEEP池或者額外數據緩存的內存
² DB_RECYCLE_CACHE_SIZE：
² DBWR_IO_SLAVES：如果沒有非同步IO，參數等同於DB_WRITER_PROCESSES模擬非同步IO而分配的從SGA到磁碟的寫入器數。如果有非同步IO，則使用DB_WRITER_PROCESSES設置多個寫程序，在DBWR期間更快地寫出臟塊
² LARGE_POOL_SIZE：分配給大型PLSQL或其他一些很少使用的ORACLE選項LARGET池的總塊數
² STATISTICS_LEVEL：啟用顧問信息，並可選擇提供更多OS統計信息來改進優化器決策。默認：TYPICAL
² java_POOL_SIZE：為JVM使用的JAVA存儲過程所分配的內存
² JAVA_MAX_SESSIONSPACE_SIZE：跟蹤JAVA類的用戶會話狀態所用內存上限
² MAX_SHARED_SERVERS：當使用共享伺服器時的共享伺服器上限
² WORKAREA_SIZE_POLICY：啟用PGA大小自動管理
² FAST_START_MTTR_TARGET：完成一次崩潰恢復的大概時間／S
² LOG_CHECKPOINT_INTERVAL：檢查點頻率
² OPEN_CURSORS：指定了保存用戶語句的專用區域大小，如此設置過高會導致ORA-4031
² DB_BLOCK_SIZE：資料庫默認塊大小
² OPTIMIZER_DYNAMIC_SAMPLING：控制動態抽樣查詢讀取的塊數量，對正在使用全局臨時表的系統非常有用

三、 SQL調優1. 使用提示
1.1 改變執行路徑
通過OPTIMIZER_MODE參數指定優化器使用方法，默認ALL_ROWS
Ø ALL_ROWS 可得最佳吞吐量執行查詢所有行
Ø FIRST_ROWS(n) 可使優化器最快檢索出第一行:
select /*+ FIRST_ROWS(1) */ store_id,… from tbl_store

1.2 使用訪問方法提示
允許開發人員改變訪問的實際查詢方式，經常使用INDEX提示
Ø CLUSTER 強制使用集群
Ø FULL
Ø HASH
Ø INDEX 語法：/*+ INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….
當不指定任何INDEX時，優化器會選擇最佳的索引
SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE
Ø INDEX_ASC 8I開始默認是升序，所以與INDEX同效
Ø INDEX_DESC
Ø INDEX_COMBINE 用來指定多個點陣圖索引，而不是選擇其中最好的索引
Ø INDEX_JOIN 只需訪問這些索引，節省了重新檢索表的時間
Ø INDEX_FFS 執行一次索引的快速全局掃描，只處理索引，不訪問具體表
Ø INDEX_SS
Ø INDEX_SSX_ASC
Ø INDEX_SS_DESC
Ø NO_INDEX
Ø NO_INDEX_FFS
Ø NO_INDEX_SS
1.3 使用查詢轉換提示
對於數據倉庫非常有幫助
Ø FACT
Ø MERGE
Ø NO_EXPAND 語法：/*+ NO_EXPAND */ column1,…
保證OR組合起的IN列表不會陷入困境，/*+ FIRST_ROWS NO_EXPAND */
Ø NO_FACT
Ø NO_MERGE
Ø NO_QUERY_TRANSFORMATION
Ø NO_REWRITE
Ø NO_STAR_TRANSFORMATION
Ø NO_UNSET
Ø REWRITE
Ø STAR_TRANSFORMATION
Ø UNSET
Ø USE_CONCAT

1.4 使用連接操作提示
顯示如何將連接表中的數據合並在一起，可用兩提示直接影響連接順序。LEADING指定連接順序首先使用的表，ORDERED告訴優化器基於FROM子句中的表順序連接這些表，並使用第一個表作為驅動表（最行訪問的表）
ORDERED語法：/*+ ORDERED */ column 1,….
訪問表順序根據FROM後的表順序來
LEADING語法：/*+ LEADING(TABLE1) */ column 1,….
類似於ORDER，指定驅動表
Ø NO_USE_HASH
Ø NO_USE_MERGE
Ø NO_USE_NL
Ø USE_HASH前提足夠的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET
通常可以為較大的結果集提供最佳的響應時間
Ø USE_MERGE
Ø USE_NL 通常可以以最快速度返回一個行
Ø USE_NL_WITH_INDEX

1.5 使用並行執行
Ø NO_PARALLEL
Ø NO_PARALLEL_INDEX
Ø PARALLEL
Ø PARALLEL_INDEX
Ø PQ_DISTRIBUTE

1.6 其他提示
Ø APPEND 不會檢查當前所用塊中是否有剩餘空間，而直接插入到表中，會直接將數據添加到新的塊中。
Ø CACHE 會將全表掃描全部緩存到內存中，這樣可直接在內存中找到數據，不用在磁碟上查詢
Ø CURSOR_SHARING_EXACT
Ø DRIVING_SITE
Ø DYNAMIC_SAMPLING
Ø MODEL_MIN_ANALYSIS
Ø NOAPPEND
Ø NOCACHE
Ø NO_PUSH_PRED
Ø NO_PUSH_SUBQ
Ø NO_PX_JOIN_FILTER
Ø PUSH_PRED
Ø PUSH_SUBQ 強制先執行子查詢，當子查詢很快返回少量行時，這些行可以用於限制外部查詢返回行數，可極大地提高性能
例：select /*+PUSH_SUBQ */ emp.empno,emp.ename
From emp,orders
where emp.deptno=(select deptno from dept where loc=』1』)
Ø PX_JOIN_FILTER
Ø QB_NAME

2. 調整查詢

2.1 在V$SQLAREA中選出最佔用資源的查詢

HASH_VALUE：SQL語句的Hash值。
ADDRESS：SQL語句在SGA中的地址。
PARSING_USER_ID：為語句解析第一條CURSOR的用戶
VERSION_COUNT：語句cursor的數量
KEPT_VERSIONS：
SHARABLE_MEMORY：cursor使用的共享內存總數
PERSISTENT_MEMORY：cursor使用的常駐內存總數
RUNTIME_MEMORY：cursor使用的運行時內存總數。
SQL_TEXT：SQL語句的文本（最大隻能保存該語句的前1000個字元）。
MODULE,ACTION：用了DBMS_APPLICATION_INFO時session解析第一條cursor時信息
SORTS: 語句的排序數
CPU_TIME: 語句被解析和執行的CPU時間
ELAPSED_TIME: 語句被解析和執行的共用時間
PARSE_CALLS: 語句的解析調用(軟、硬)次數
EXECUTIONS: 語句的執行次數
INVALIDATIONS: 語句的cursor失效次數
LOADS: 語句載入(載出)數量
ROWS_PROCESSED: 語句返回的列總數
select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT
from v$sqlarea a ,dba_users b
where a.PARSING_USER_ID=b.user_id and a.DISK_READS>100 order by a.DISK_READS desc;

2.2 在V$SQL中選出最佔用資源的查詢
與V$SQLAREA類似
select * from
(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)
where rank_buffers <11

2.3 確定何時使用索引
² 當查詢條件只需要返回很少的行（受限列）時，則需要建立索引，不同的版本中這個返回要求不同
V5:20% V7:7% V8i,V9i:4% V10g: 5%
查看錶上的索引
select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner
from dba_ind_columns a
where a.table_owner='CLOUDSEA'

² 修正差的索引，可使用提示來限制很差的索引，如INDEX，FULL提示
² 在SELECT 和WHERE中的列使用索引
如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)
對於系統中很關鍵的查詢，可以考慮建立此類連接索引

² 在一個表中有多個索引時可能出現麻煩，使用提示INDEX指定使用索引
² 使用索引合並，使用提示INDEX_JOIN
² 基於函數索引，由於使用了函數造成查詢很慢.必須基於成本的優化模式,參數：
QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);

2.4 在內存中緩存表
將常用的相對小的表緩存到內存中，但注意會影響到嵌套循環連接上的驅動表
alter table tablename cache;

2.5 使用EXISTS 與嵌套子查詢 代替IN
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=』A』);
(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = 『A』
(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT 『X』 FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = 『A』);

四、 使用STATSPACK和AWR報表調整等待和閂鎖

1. 10GR2里的腳本
在$ORACLE_HOME/RDBMS/ADMIN下

Spcreate.sql 通過調用spcusr.sql spctab.sql 和spcpkg.sql創建STATSPACK環境，使用SYSDBA運行它
Spdrop.sql 調用sptab.sql和spsr.sql刪除整個STATSPACK環境，使用SYSDBA運行它
Spreport.sql 這是生成報表的主要腳本，由PERFSTAT用戶運行
Sprepins.sql 為指定的資料庫和實例生成實例報表
Sprepsql.sql 為指定的SQL散列值生成SQL報表
Sprsqins.sql 為指定的資料庫和實例生成SQL報表
Spauto.sql 使用DBMS_JOB自動進行統計數據收集（照相）
Sprepcon.sql 配置SQLPLUS變數來設置像閾值這樣的內容的配置文件
Spurge.sql 刪除給定資料庫實例一定范圍內的快照ID，不刪除基線快照
Sptrunc.sql 截短STATSPACK表裡所有性能數據

五、 執行快速系統檢查1. 緩沖區命中率
查詢緩沖區命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;

㈥ 執行一下Oracle SQL時，慢如蝸牛，如何優化

至少可以改成這個樣子：
--------------------------------------------------------
select b.reach_item_no as 小區編號,
c.reach_item_name as 小區名稱,
substr(b.floor_no, 1, 2) as 樓棟,
count(1) as 應抄數,
sum(decode(d.mp_item_no, null, 0, 1)) as 實抄數,
to_char(round((sum(decode(d.mp_item_no, null, 0, 1)) / count(1)) * 100)) as 抄收率,
'20090701' as 抄收日期
from meter_running a,
customer_files b,
reach_item_location c,
raw_day_e d
where a.customer_no = b.customer_no
and b.reach_item_no = c.reach_item_no
and d.mp_item_no(+) = a.mp_item_no
and d.data_dt(+) = '20090701'
group by substr(b.floor_no, 1, 2), b.reach_item_no, c.reach_item_name,
order by 1, 3
--------------------------------------------------------
如果執行還慢，那麼就要看索引是否建了，統計信息是否收集，分配的pga是否夠大。這種關聯使用hash join應該效率更高，如果分配的pga太小，走nl，估計會比較慢。

㈦ Oracle資料庫對SQL查詢做了哪些優化

oracle內部有個優化器，以前是RBO，現在都改用CBO了。CBO有三個組件：

查詢轉換器（QueryTransformer）

評估器（Estimator）

計劃生成器（PlanGenerator）

這概念就比較多了，簡單描述一下吧：

查詢轉換器--查詢語句的形式會影響所產生的執行計劃，查詢轉換器的作用就是改變查詢語句的形式以產生較好的執行計劃。有以下幾種轉換技術：視圖合並（ViewMerging）、謂詞推入（PredicatePushing）、子查詢反嵌套（SubqueryUnnesting）、物化視圖的查詢重寫（）和OR擴展（OR-expansion）。

評估器--評估器通過計算三個值來評估計劃的總體成本：選擇性（Selectivity）、基數（Cardinality）、成本（Cost）。

計劃生成器--計劃生成器的作用就是生成不同的執行計劃，然後選擇其中總體成本最低的一個。

這就是sql語句被解析生成執行執行計劃的流程。優化器參考統計信息，決定sql的執行計劃。比如兩張表關聯，哪張表作為驅動表，是否使用索引等。

不知道你所說的復合查詢是個什麼概念，如果是指包含子查詢的查詢，那麼建議你最好不要這么干，oracle會改寫子查詢或者視圖，但改寫的效果可能很差。能不用就不要用，或者減少子查詢和視圖的嵌套。

㈧ oracle sql優化涉及哪些原理

B 樹索引（B-Tree索引）
B樹索引是我們在oracle資料庫中最常用的索引，在詳細介紹訪問方法之前，我們看一下B-TREE索引的結構（圖片來源網路）
oracle的B樹索引就好像一顆長到的樹，他包含兩種類型，一種是索引分支塊（根節點塊，分支節點塊）一種是索引葉子塊（葉子節點塊）。分節點用來搜索，葉子節點用來存儲數據。根節點存儲索引的低層分支節點的數據。 由於所有的葉子節點均會自動的存儲成相同的深度，所以稱為「平 衡樹索引」， 故此，從任何葉子處檢索數據消耗的時間都是相同的。
對於分支節點塊（包括根節點塊）來說，其所包含的索引條目都是按照順序排列的（預設是升序排列，也可以在創建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）都具有兩個欄位。第一個欄位表示當前該分支節點塊下面所鏈接的索引塊中所包含的最小鍵值；第二個欄位為四個位元組，表示所鏈接的索引塊的地址，該地址指向下面一個索引塊。 比如從上圖一可以看到，對於根節點塊來說，包含三條記錄，分別為（0 B1）、（500 B2）、（1000 B3），它們指向三個分支節點塊。其中的0、500和1000分別表示這三個分支節點塊所鏈接的鍵值的最小值。而B1、B2和B3則表示所指向的三個分支節點塊的地址。在一個分支節點塊中 所能容納的記錄 行數由數據塊大小以及索引鍵值的長度決定。
對於葉子節點塊來說，其所包含的索引條目與分支節點一樣，都是按照順序排列的（預設是升序排列，也可以在創建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）也具有兩個欄位。第一個欄位表示索引的鍵值，對於單列索引來說是一個值；而對於多列索引來說則是多個值組合在一起的。第二個欄位表示鍵值所對應的記錄行的ROWID，該ROWID是記錄行在表裡的物理地址。
當用戶創建索引時，Oracle 取得所有被索引列的數據並進行排序，之後將排序後索引值和與此值相對應的 rowid 按照從下到上的順序載入到索引中。例如，以下語句：
[html] view plain print?在CODE上查看代碼片派生到我的代碼片
CREATE INDEX employees_last_name ON employees(last_name);
Oracle 先將 employees 表按 last_name 列排序，再將排序後的 列及相應的 rowid 按從下到上的順序載入到索引中。使用此索引時，Oracle 可以快速地搜索已排序的 last_name 值，並使用相應的 rowid 去定位包含用戶所查找的 last_name 值的數據行。
在一個平衡樹索引中，最底層的索引塊(葉塊)存儲了被索引的數據值，以及對應的
rowid。葉塊之間以雙向鏈表的形式相互連接。位於葉塊之上的分支塊中包含了
指向下層索引塊的指針。
接下來介紹一個索引查詢的流程，從上往下，第一層為根節點，第二層為分支節點，第三層為葉子節點（包含了列值和rowid）。比如我們的條件為where=29，(補充說明如果被索引的列存儲的是字元數據，那麼索引值為這些字元數據在當前資料庫字元集中的二進制值)就從跟節點開始查詢，29在0-500中，指向分支節點最左邊第一個分支節點塊（也就是B1），就B1中去找，發現29在0-200中，指向葉子節點的L1，於是在L1中找到29的值和響應的rowid。如果只查找索引列的值，就不用根據rowid去表中查找了，如果還要查找值29這行的其他列的值就得根據rowid去表裡查查詢（這個過程叫做回表查詢）。葉子節點還有個雙向鏈表（如圖）。在通過索引進行范圍掃描時會起作用，比如要查找值29-700，如果當查找到值29的時候，不就會再從跟節點開始查找其他的值，而是根據本葉子節點鏈表的指向去查找其他的值。

㈨ 怎麼使用 基於oracle的sql優化

（1） 選擇最有效率的表名順序(只在基於規則的優化器中有效)：
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
（2） WHERE子句中的連接順序．：
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
（3） SELECT子句中避免使用 『 * 『：
ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間
（4） 減少訪問資料庫的次數：
ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀數據塊等；
（5） 在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索數據量 ,建議值為200
（6） 使用DECODE函數來減少處理時間：
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
（7） 整合簡單,無關聯的資料庫訪問：
如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系)
（8） 刪除重復記錄：
最高效的刪除重復記錄方法 ( 因為使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
（9） 用TRUNCATE替代DELETE：
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢復.因此很少的資源被調用,執行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
（10） 盡量多使用COMMIT：
只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a. 回滾段上用於恢復數據的信息.
b. 被程序語句獲得的鎖
c. redo log buffer 中的空間
d. ORACLE為管理上述3種資源中的內部花費
（11） 用Where子句替換HAVING子句：
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. (非oracle中)on、where、having這三個都可以加條件的子句中，on是最先執行，where次之，having最後，因為on是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的，where也應該比having快點的，因為它過濾數據後才進行sum，在兩個表聯接時才用on的，所以在一個表的時候，就剩下where跟having比較了。在這單表查詢統計的情況下，如果要過濾的條件沒有涉及到要計算欄位，那它們的結果是一樣的，只是where可以使用rushmore技術，而having就不能，在速度上後者要慢如果要涉及到計算的欄位，就表示在沒計算之前，這個欄位的值是不確定的，根據上篇寫的工作流程，where的作用時間是在計算之前就完成的，而having就是在計算後才起作用的，所以在這種情況下，兩者的結果會不同。在多表聯接查詢時，on比where更早起作用。系統首先根據各個表之間的聯接條件，把多個表合成一個臨時表後，再由where進行過濾，然後再計算，計算完後再由having進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什麼時候起作用，然後再決定放在那裡
（12） 減少對表的查詢：
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13） 通過內部函數提高SQL效率.：
復雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
（14） 使用表的別名(Alias)：
當在SQL語句中連接多個表時, 請使用表的別名並把別名前綴於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
（15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND EXISTS (SELECT 『X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 『MELB')
(低效)SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 『MELB')
（16） 識別'低效執行'的SQL語句：
雖然目前各種關於SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

（17） 用索引提高效率：
索引是表的一個概念部分,用來提高檢索數據的效率，ORACLE使用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引是有必要的.：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
（18） 用EXISTS替換DISTINCT：
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果. 例子：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 『X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（19） sql語句用大寫的；因為oracle總是先解析sql語句，把小寫的字母轉換成大寫的再執行
（20） 在java代碼中盡量少用連接符「＋」連接字元串！
（21） 避免在索引列上使用NOT 通常，
我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE」遇到」NOT,他就會停止使用索引轉而執行全表掃描.
（22） 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
（23） 用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
（24） 用UNION替換OR (適用於索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = 「MELBOURNE」
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = 「MELBOURNE」
如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.
（25） 用IN來替換OR
這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
（26） 避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引．對於單列索引，如果列包含空值，索引中將不存在此記錄. 對於復合索引，如果每個列都為空，索引中同樣不存在此記錄.如果至少有一個列不為空，則記錄存在於索引中．舉例: 如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等於空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27） 總是使用索引的第一個列：
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引. 這也是一條簡單而重要的規則，當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
（28） 用UNION-ALL 替換UNION ( 如果有可能的話)：
當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合並, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是，UNION ALL 將重復輸出兩個結果集合中相同記錄. 因此各位還是要從業務需求分析使用UNION ALL的可行性. UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內存. 對於這塊內存的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29） 用WHERE替代ORDER BY：
ORDER BY 子句只在兩種嚴格的條件下使用索引.
ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使用)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效: (使用索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
（30） 避免改變索引列的類型.:
當比較不同數據類型的數據時, ORACLE自動對列進行簡單的類型轉換.
假設 EMPNO是一個數值類型的索引列.
SELECT … FROM EMP WHERE EMPNO = 『123'
實際上,經過ORACLE類型轉換, 語句轉化為:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(『123')
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.
現在,假設EMP_TYPE是一個字元類型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
這個語句被ORACLE轉換為:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因為內部發生的類型轉換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進行隱式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值類型到字元類型
（31） 需要當心的WHERE子句:
某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子.
在下面的例子里, (1)『!=' 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中. (2) 『||'是字元連接函數. 就象其他函數那樣, 停用了索引. (3) 『+'是數學函數. 就象其他數學函數那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.
（32） a. 如果檢索數據量超過30%的表中記錄數.使用索引將沒有顯著的效率提高.
b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
（33） 避免使用耗費資源的操作:
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎
執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強
（34） 優化GROUP BY:
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = 『PRESIDENT'
OR JOB = 『MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 『PRESIDENT'
OR JOB = 『MANAGER'
GROUP JOB

㈩ oracle中如何優化sql語句的，用什麼方法

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合並的方式連接它們.首先,掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並.
例如: 表 TAB1 16,384 條記錄
表 TAB2 1 條記錄
選擇TAB2作為基礎表 (最好的方法)
select count(*) from tab1,tab2 執行時間0.96秒
選擇TAB2作為基礎表 (不佳的方法)
select count(*) from tab2,tab1 執行時間26.09秒