講解ORACLE的執(zhí)行計劃的幾種方法

Oracle

10年積累的成都網站建設、成都網站制作經驗，可以快速應對客戶對網站的新想法和需求。提供各種問題對應的解決方案。讓選擇我們的客戶得到更好、更有力的網絡服務。我雖然不認識你，你也不認識我。但先網站制作后付款的網站建設流程，更有瑞安免費網站建設讓你可以放心的選擇與我們合作。

RDBMS執(zhí)行每一條SQL語句，都必須經過Oracle優(yōu)化器的評估。所以，了解優(yōu)化器是如何選擇(搜索)路徑以及索引是如何被使用的，對優(yōu)化SQL語句有很大的幫助。Explain可以用來迅速方便地查出對于給定SQL語句中的查詢數據是如何得到的即搜索路徑(我們通常稱為Access

Path)。從而使我們選擇最優(yōu)的查詢方式達到最大的優(yōu)化效果。

1.1

、安

裝

要使用EXPLAIN首先要執(zhí)行相應的腳本，創(chuàng)建出Explain_plan表。

具體腳本執(zhí)行如下：

$ORACLE_HOME/rdbms/admin/utlxplan.sql

(UNIX)

該腳本后會生成一個表這個程序會創(chuàng)建一個名為plan_table的表。

1.2

、使用

常規(guī)使用語法：

explainPLAN[SETSTATEMENT_ID[=]

評論

加載更多

誰能把Oracle的體系結構用通俗的語言講解一下 我要自己理解的.用通俗的話說出來.

1、 ORACLE 實例――包括內存結構與后臺進程 2、 ORACLE 數據庫――物理操作系統(tǒng)文件的集合 3、 了解內存結構的組成 4、 了解后臺進程的作用

1、 Oracle 實例――包括內存結構與后臺進程

2、 Oracle 數據庫――物理操作系統(tǒng)文件的集合

3、 了解內存結構的組成

4、 了解后臺進程的作用

5、 了解數據庫的物理文件

6、 解釋各種邏輯結構

一、Oracle實例

1、Oracle 實例

System Global Area(SGA) 和 Background Process 稱為數據庫的實例。

2、Oracle 數據庫

一系列物理文件的集合（數據文件，控制文件，聯(lián)機日志，參數文件等）

3、系統(tǒng)全局共享區(qū)System Global Area(SGA)

System Global Area 是一塊巨大的共享內存區(qū)域，他被看做是Oracle 數據庫的一個大緩沖池，這里的數據可以被Oracle的各個進程共用。其大小可以通過如下語句查看：

SQL select * from v$sga;

NAME VALUE

-------------------- ---------

Fixed Size 39816

Variable Size 259812784

Database Buffers 1.049E+09

Redo Buffers 327680

更詳細的信息可以參考V$sgastat、V$buffer_pool

主要包括以下幾個部分：

a、 共享池(Shared pool)

共享池是SGA中最關鍵的內存片段，特別是在性能和可伸縮性上。一個太小的共享池會扼殺性能，使系統(tǒng)停止，太大的共享池也會有同樣的效果，將會消耗大量的CPU來管理這個共享池。不正確的使用共享池只會帶來災難。共享池主要又可以分為以下兩個部分：

SQL語句緩沖(Library Cache)

當一個用戶提交一個SQL語句，Oracle會將這句SQL進行分析(parse)，這個過程類似于編譯，會耗費相對較多的時間。在分析完這個SQL，Oracle會把他的分析結果給保存在Shared pool的Library Cache中，當數據庫第二次執(zhí)行該SQL時，Oracle自動跳過這個分析過程，從而減少了系統(tǒng)運行的時間。這也是為什么第一次運行的SQL 比第二次運行的SQL要慢一點的原因。

下面舉例說明parse的時間

SQL select count(*) fromscpass ;

COUNT(*)

----------

243

Elapsed: 00:00:00.08

這是在Share_pool 和Data buffer 都沒有數據緩沖區(qū)的情況下所用的時間

SQL alter system flush SHARED_POOL;

System altered.

清空Share_pool，保留Data buffer

SQL select count(*) from scpass ;

COUNT(*)

----------

243

Elapsed: 00:00:00.02

SQL select count(*) from scpass ;

COUNT(*)

----------

243

Elapsed: 00:00:00.00

從兩句SQL 的時間差上可以看出該SQL 的Parse 時間約為00:00:00.02

對于保存在共享池中的SQL語句，可以從V$Sqltext、v$Sqlarea中查詢到，對于編程者來說，要盡量提高語句的重用率，減少語句的分析時間。一個設計的差的應用程序可以毀掉整個數據庫的Share pool，提高SQL語句的重用率必須先養(yǎng)成良好的變成習慣，盡量使用Bind變量。

數據字典緩沖區(qū)(Data Dictionary Cache)

顯而易見，數據字典緩沖區(qū)是Oracle特地為數據字典準備的一塊緩沖池，供Oracle內部使用，沒有什么可以說的。

b、塊緩沖區(qū)高速緩存(Database Buffer Cache)

這些緩沖是對應所有數據文件中的一些被使用到的數據塊。讓他們能夠在內存中進行操作。在這個級別里沒有系統(tǒng)文件,，戶數據文件，臨時數據文件，回滾段文件之分。也就是任何文件的數據塊都有可能被緩沖。數據庫的任何修改都在該緩沖里完成，并由DBWR進程將修改后的數據寫入磁盤。

這個緩沖區(qū)的塊基本上在兩個不同的列表中管理。一個是塊的“臟”表(Dirty List)，需要用數據庫塊的

書寫器(DBWR)來寫入，另外一個是不臟的塊的列表(Free List)，一般的情況下，是使用最近最少使用 (Least Recently Used,LRU)算法來管理。塊緩沖區(qū)高速緩存又可以細分為以下三個部分（Default pool,Keep pool,Recycle pool）。如果不是人為設置初始化參數(Init.ora)，Oracle將默認為Default pool。由于操作系統(tǒng)尋址能力的限制，不通過特殊設置，在32位的系統(tǒng)上，塊緩沖區(qū)高速緩存最大可以達到1.7G，在64位系統(tǒng)上，塊緩沖區(qū)高速緩存最大可以達到10G。

c、重做日志緩沖區(qū)(Redo log buffer)

重做日志文件的緩沖區(qū)，對數據庫的任何修改都按順序被記錄在該緩沖，然后由LGWR進程將它寫入磁盤。這些修改信息可能是DML語句，如(Insert,Update,Delete)，或DDL語句，如(Create,Alter,Drop等)。 重做日志緩沖區(qū)的存在是因為內存到內存的操作比較內存到硬盤的速度快很多，所以重作日志緩沖區(qū)可以加快數據庫的操作速度，但是考慮的數據庫的一致性與可恢復性，數據在重做日志緩沖區(qū)中的滯留時間不會很長。所以重作日志緩沖區(qū)一般都很小，大于3M之后的重作日志緩沖區(qū)已經沒有太大的實際意義。

d、Java程序緩沖區(qū)(Java Pool)

Java 的程序區(qū)，Oracle 8I 以后，Oracle 在內核中加入了對Java的支持。該程序緩沖區(qū)就是為Java 程序保留的。如果不用Java程序沒有必要改變該緩沖區(qū)的默認大小。

e、大池(Large Pool)

大池的得名不是因為大，而是因為它用來分配大塊的內存，處理比共享池更大的內存，在8.0開始引入。

下面對象使用大池：

MTS――在SGA的Large Pool中分配UGA

語句的并行查詢(Parallel Executeion of Statements)――允許進程間消息緩沖區(qū)的分配，用來協(xié)調 并行查詢服務器

備份(Backup)――用于RMAN磁盤I/O緩存

4、后臺進程(Background process)

后臺進程是Oracle的程序，用來管理數據庫的讀寫，恢復和監(jiān)視等工作。Server Process主要是通過他和user process進行聯(lián)系和溝通，并由他和user process進行數據的交換。在Unix機器上，Oracle后臺進程相對于操作系統(tǒng)進程，也就是說，一個Oracle后臺進程將啟動一個操作系統(tǒng)進程；在Windows機器上， Oracle后臺進程相對于操作系統(tǒng)線程，打開任務管理器，我們只能看到一個Oracle.EXE的進程，但是通過另外的工具，就可以看到包含在這里進程中的線程。

在Unix上可以通過如下方法查看后臺進程：

ps ?ef | grep ora_

# ps -ef | grep ora_ | grep XCLUAT

Oracle 29431 1 0 Sep 02 2:02 ora_dbwr_SID

Oracle 29444 1 0 Sep 02 0:03 ora_ckpt_SID

Oracle 29448 1 0 Sep 02 2:42 ora_smon_SID

Oracle 29442 1 0 Sep 02 3:25 ora_lgwr_SID

Oracle 29427 1 0 Sep 02 0:01 ora_pmon_SID

a、Oracle系統(tǒng)有5 個基本進程他們是

DBWR(數據文件寫入進程)

LGWR(日志文件寫入進程)

SMON(系統(tǒng)監(jiān)護進程)

PMON(用戶進程監(jiān)護進程)

CKPT(檢查點進程,同步數據文件, 日志文件,控制文件)

b、DBWR

將修改過的數據緩沖區(qū)的數據寫入對應數據文件

維護系統(tǒng)內的空緩沖區(qū)

這里指出幾個容易錯誤的概念:

當一個更新提交后,DBWR把數據寫到磁盤并返回給用戶提交完成.

DBWR會觸發(fā)CKPT 后臺進程

DBWR不會觸發(fā)LGWR 進程

上面的概念都是錯誤的.

DBWR是一個很底層的工作進程，他批量的把緩沖區(qū)的數據寫入磁盤。和任何前臺用戶的進程幾乎沒有什么關系，也不受他們的控制。至于DBWR會不會觸發(fā)LGWR和CKPT進程，我們將在下面幾節(jié)里討論。

DBWR工作的主要條件如下

DBWR 超時

系統(tǒng)中沒有多的空緩沖區(qū)用來存放數據

CKPT 進程觸發(fā)DBWR 等

c、LGWR

將重做日志緩沖區(qū)的數據寫入重做日志文件，LGWR是一個必須和前臺用戶進程通信的進程。當數據被修改的時候，系統(tǒng)會產生一個重做日志并記錄在重做日志緩沖區(qū)內。這個重做日志可以類似的認為是以下的一個結構:

SCN=000000001000

數據塊ID

對象ID=0801

數據行=02

修改后的數據=0011

提交的時候，LGWR必須將被修改的數據的重做日志緩沖區(qū)內數據寫入日志數據文件，然后再通知前臺進程提交成功，并由前臺進程通知用戶。從這點可以看出LGWR承擔了維護系統(tǒng)數據完整性的任務。

LGWR 工作的主要條件如下

用戶提交

有1/3 重做日志緩沖區(qū)未被寫入磁盤

有大于1M 重做日志緩沖區(qū)未被寫入磁盤

超時

DBWR需要寫入的數據的SCN號大于LGWR 記錄的SCN號，DBWR 觸發(fā)LGWR寫入

d、SMON

工作主要包含

清除臨時空間

在系統(tǒng)啟動時，完成系統(tǒng)實例恢復

聚結空閑空間

從不可用的文件中恢復事務的活動

OPS中失敗節(jié)點的實例恢復

清除OBJ$表

縮減回滾段

使回滾段脫機

e、PMON

主要用于清除失效的用戶進程，釋放用戶進程所用的資源。如PMON將回滾未提交的工作，釋放鎖，釋放分配給失敗進程的SGA資源。

f、CKPT

同步數據文件，日志文件和控制文件，由于DBWR/LGWR的工作原理，造成了數據文件，日志文件，控制文件的不一至，這就需要CKPT進程來同步。CKPT會更新數據文件/控制文件的頭信息。

CKPT工作的主要條件如下

在日志切換的時候

數據庫用immediate ,transaction , normal 選項shutdown 數據庫的時候

根據初始話文件LOG_CHECKPOINT_INTERVAL、LOG_CHECKPOINT_TIMEOUT、FAST_START_IO_TARGET 的設置的數值來確定

用戶觸發(fā)

以下進程的啟動需要手工配置

g、ARCH

當數據庫以歸檔方式運行的時候，Oracle會啟動ARCH進程，當重做日志文件被寫滿時，日志文件進行切換，舊的重做日志文件就被ARCH進程復制到一個/多個特定的目錄/遠程機器。這些被復制的重做日志文件被叫做歸檔日志文件。

h、RECO

負責解決分布事物中的故障。Oracle可以連接遠程的多個數據庫，當由于網絡問題，有些事物處于懸而未決的狀態(tài)。RECO進程試圖建立與遠程服務器的通信，當故障消除后，RECO進程自動解決所有懸而未決的會話。

i、服務進程Server Process

服務進程的分類

專用服務進程(Dedicated Server Process)

一個服務進程對應一個用戶進程

共享服務進程(MultiTreaded Server Process)

一個服務進程對應多個用戶進程，輪流為用戶進程服務。

PGA UGA

PGA = Process Global Area

UGA = User Global Area

他保存了用戶的變量、權限、堆棧、排序空間等用戶信息，對于專用服務器進程，UGA在PGA中分配。對于多線程進程，UGA在Large pool中分配。

j、用戶進程User Process

在客戶端，將用戶的SQL 語句傳遞給服務進程

5、一個貫穿數據庫全局的概念----系統(tǒng)改變號SCN(System Change Number)

系統(tǒng)改變號，一個由系統(tǒng)內部維護的序列號。當系統(tǒng)需要更新的時候自動增加，他是系統(tǒng)中維持數據的一致性和順序恢復的重要標志。

a. 查詢語句不會使SCN增加，就算是同時發(fā)生的更新，數據庫內部對應的SCN也是不同的。這樣一來就保證了數據恢復時候的順序。

b. 維持數據的一致性，當一

二、Oracle 數據庫

Oracle數據庫的組成――物理操作系統(tǒng)文件的集合。主要包括以下幾種。

1、控制文件（參數文件init.ora記錄了控制文件的位置）

控制文件包括如下主要信息

數據庫的名字，檢查點信息，數據庫創(chuàng)建的時間戳

所有的數據文件，聯(lián)機日志文件，歸檔日志文件信息

備份信息等

有了這些信息，Oracle就知道那些文件是數據文件，現在的重做日志文件是哪些，這些都是系統(tǒng)啟動和運行的基本條件，所以他是Oracle運行的根本。如果沒有控制文件系統(tǒng)是不可能啟動的。控制文件是非常重要的，一般采用多個鏡相復制來保護控制文件，或采用RAID來保護控制文件?？刂莆募膩G失，將使數據庫的恢復變的很復雜。

控制文件信息可以從V$Controlfile中查詢獲得

2、數據文件（數據文件的詳細信息記載在控制文件中）

可以通過如下方式查看數據文件

SQL select name from v$datafile;

NAME

---------------------------------------------

/u05/dbf/PROD/system_01.dbf

/u06/dbf/PROD/temp_01.dbf

/u04/dbf/PROD/users_01.dbf

/u09/dbf/PROD/rbs_01.dbf

/u06/dbf/PROD/applsys_indx_01.dbf

/u05/dbf/PROD/applsys_data_01.dbf

從以上可以看出，數據文件大致可以分為以下幾類：

i. 系統(tǒng)數據文件(system_01.dbf)

存放系統(tǒng)表和數據字典，一般不放用戶的數據，但是用戶腳本，如過程，函數，包等卻是保存在數據字典中的。

名詞解釋：數據字典 數據字典是一些系統(tǒng)表或視圖，他存放系統(tǒng)的信息，他包括數據庫版本，數據文件信息，表與索引等段信息，系統(tǒng)的運行狀態(tài)等各種和系統(tǒng)有關的信息和用戶腳本信息。數據庫管理員可以通過對數據字典的查詢，就可以了解到Oracle的運行狀態(tài)。

ii. 回滾段文件(rbs_01.dbf)

如果數據庫進行對數據的修改，那么就必須使用回滾段，回滾段是用來臨時存放修改前的數據(Before Image)。回滾段通常都放在一個單獨的表空間上（回滾表空間），避免表空間碎片化，這個表空間包含的數據文件就是回滾數據文件。

iii. 臨時數據文件(temp_01.dbf)

主要存放用戶的排序等臨時數據，與回滾段相似，臨時段也容易引起表空間碎片化，而且沒有辦法在一個永久表空間上開辟臨時段，所以就必須有一個臨時表空間，它所包含的數據文件就是臨時數據文件，主要用于不能在內存上進行的排序操作。我們必須為用戶指定一個臨時表空間。

iv. 用戶數據文件(/applsys_data_01.dbf ,applsys_indx_01.dbf)

存放用戶數據，這里列舉了兩類常見的用戶型數據，一般數據和索引數據，一般來說，如果條件許可的話，可以考慮放在不同的磁盤上。

3、重做日志文件（聯(lián)機重做日志）

用戶對數據庫進行的任何操作都會記錄在重做日志文件。在了解重做日志之前必須了解重做日志的兩個概念，重做日志組和重做日志組成員(Member)，一個數據庫中至少要有兩個日志組文件，一組寫完后再寫另一組，即輪流寫。每個日志組中至少有一個日志成員，一個日志組中的多個日志成員是鏡相關系，有利于日志文件的保護，因為日志文件的損壞，特別是當前聯(lián)機日志的損壞，對數據庫的影響是巨大的。

聯(lián)機日志組的交換過程叫做切換，需要特別注意的是，日志切換在一個優(yōu)化效果不好的數據庫中會引起臨時的“掛起”。掛起大致有兩種情況：

在歸檔情況下，需要歸檔的日志來不及歸檔，而聯(lián)機日志又需要被重新利用

檢查點事件還沒有完成（日志切換引起檢查點），而聯(lián)機日志需要被重新利用

解決這種問題的常用手段是：

i.增加日志組

ii.增大日志文件成員大小

通過v$log可以查看日志組，v$logfile可以查看具體的成員文件。

4、歸檔日志文件

Oracle可以運行在兩種模式之中，歸檔模式和不歸檔模式。如果不用歸檔模式，當然，你就不會有歸檔日志，但是，你的系統(tǒng)將不會是一個實用系統(tǒng)，特別是不能用于生產系統(tǒng)，因為你可能會丟失數據。但是在歸檔模式中，為了保存用戶的所有修改，在重做日志文件切換后和被覆蓋之間系統(tǒng)將他們另外保存成一組連續(xù)的文件系列，該文件系列就是歸檔日志文件。

有人或許會說，歸檔日志文件占領我大量的硬盤空間，其實，具體想一想，你是愿意浪費一點磁盤空間來保護你的數據，還是愿意丟失你的數據呢？顯而義見，我們需要保證我們的數據的安全性。其實，歸檔并不是一直占領你的磁盤空間，你可以把她備份到磁帶上，或則刪除上一次完整備份前的所有日志文件。

5、初始化參數文件

initSID.ora或init.ora文件，因為版本的不一樣，其位置也可能會不一樣。在8i中，通常位于$Oracle_HOME/admin//Pfile下，初始化文件記載了許多數據庫的啟動參數，如內存，控制文件，進程數等，在數據庫啟動的時候加載（Nomount時加載），初始化文件記錄了很多重要參數，對數據庫的性能影響很大，如果不是很了解，不要輕易亂改寫，否則會引起數據庫性能下降。

6、其他文件

i . 密碼文件

用于Oracle 的具有sysdba權限用戶的認證.

ii. 日志文件

報警日志文件（alert.log或alrt.ora）

記錄數據庫啟動，關閉和一些重要的出錯信息。數據庫管理員應該經常檢查這個文件，并對出現的問題作出即使的反應。你可以通過以下SQL 找到他的路徑select value from v$PARAMETER where name ="background_dump_dest";

后臺或用戶跟蹤文件

系統(tǒng)進程或用戶進程出錯前寫入的信息，一般不可能讀懂，可以通過Oracle的TKPROF工具轉化為可以讀懂的格式。對于系統(tǒng)進程產生的跟蹤文件與報警日志文件的路徑一樣，用戶跟蹤文件的路徑，你可以通過以下SQL找到他的路徑select value from v$PARAMETER where name ="user_dump_dest";

三、Oracle邏輯結構

1、 表空間(tablespace)

表空間是數據庫中的基本邏輯結構，一系列數據文件的集合。一個表空間可以包含多個數據文件，但是一個數據文件只能屬于一個表空間。

2、 段(Segment)

段是對象在數據庫中占用的空間，雖然段和數據庫對象是一一對應的，但段是從數據庫存儲的角度來看的。一個段只能屬于一個表空間，當然一個表空間可以有多個段。

表空間和數據文件是物理存儲上的一對多的關系，表空間和段是邏輯存儲上的一對多的關系，段不直接和數據文件發(fā)生關系。一個段可以屬于多個數據文件，關于段可以指定擴展到哪個數據文件上面。

段基本可以分為以下四種

數據段(Data Segment)

索引段(Index Segment)

回滾段(Rollback Segment)

臨時段(Temporary Segment)

3、區(qū)間(Extent)

關于Extent的翻譯有多種解釋，有的譯作擴展，有的譯作盤區(qū)，我這里通常譯為區(qū)間。在一個段中可以存在多個區(qū)間，區(qū)間是為數據一次性預留的一個較大的存儲空間，直到那個區(qū)間被用滿，數據庫會繼續(xù)申請一個新的預留存儲空間，即新的區(qū)間，一直到段的最大區(qū)間數(Max Extent)或沒有可用的磁盤空間可以申請。 在Oracle8i以上版本，理論上一個段可以無窮個區(qū)間，但是多個區(qū)間對Oracle卻是有性能影響的，Oracle建議把數據分布在盡量少的區(qū)間上，以減少Oracle的管理與磁頭的移動。

4、Oracle數據塊(Block)

Oracle最基本的存儲單位，他是OS數據塊的整數倍。Oracle的操作都是以塊為基本單位，一個區(qū)間可以包含多個塊（如果區(qū)間大小不是塊大小的整數倍，Oracle實際也擴展到塊的整數倍）。

5、基本表空間介紹

a. 系統(tǒng)表空間

主要存放數據字典和內部系統(tǒng)表基表

查看數據數據字典的SQL

select * from dict

查看內部系統(tǒng)表的SQL

select * from v$fixed_view_definition

DBA對系統(tǒng)的系統(tǒng)表中的數據字典必須有一個很深刻的了解，他們必須準備一些基礎的SQL語句，通過這些SQL可以立即了解系統(tǒng)的狀況和數據庫的狀態(tài)，這些基本的SQL包括

系統(tǒng)的剩余空間

系統(tǒng)的SGA

狀態(tài)系統(tǒng)的等待

用戶的權限

當前的用戶鎖

緩沖區(qū)的使用狀況等

在成為DBA 的道路上我們不建議你過分的依賴于OEM/Quest 等優(yōu)秀的數據庫管理工具，因為他們不利于你對數據數據字典的理解，SQL語句可以完成幾乎全部的數據庫管理工作。

大量的讀少量的寫是該表空間的一個顯著的特點。

b. 臨時表空間.

臨時表空間顧名思義是用來存放臨時數據的，例如排序操作的臨時空間，他的空間會在下次系統(tǒng)啟動的時候全部被釋放。

c. 回滾段表空間

i. 回滾段在系統(tǒng)中的作用

當數據庫進行更新插入刪除等操作的時候，新的數據被更新到原來的數據文件，而舊的數據(Before Image)就被放到回滾段中，如果數據需要回滾，那么可以從回滾段將數據再復制到數據文件中。來完成數據的回滾。在系統(tǒng)恢復的時候， 回滾段可以用來回滾沒有被commit 的數據，解決系統(tǒng)的一至性。

回滾段在什么情況下都是大量的寫，一般是少量讀，因此建議把回滾段單獨出來放在一個單獨的設備（如單獨的磁盤或RAID），以減少磁盤的IO爭用。

ii. 回滾段的工作方式

一個回滾表空間可以被劃分成多個回滾段.

一個回滾段可以保存多個會話的數據.

回滾段是一個圓形的數據模型

假設回滾段由4 個區(qū)間組成，他們的使用順序就是區(qū)間1à區(qū)間2à區(qū)間3à區(qū)間4à區(qū)間1。也就是說，區(qū)間是可以循環(huán)使用的，當區(qū)間4到區(qū)間1的時候，區(qū)間1里面的會話還沒有結束, 區(qū)間4用完后就不能再用區(qū)間1,這時系統(tǒng)必須分配區(qū)間5，來繼續(xù)為其他會話服務服務。

我們分析一個Update 語句的完成

①. 用戶提交一個Update 語句

②. Server Process 檢查內存緩沖.

如果沒有該數據塊的緩沖，則從磁盤讀入

i. 如果沒有內存的有效空間，DBWR被啟動將未寫入磁盤的臟緩沖寫入磁盤

ii. 如果有有效空間，則讀入

③. 在緩沖內更新數據

i. 申請一個回滾段入口，將舊數據寫如回滾段

ii. 加鎖并更新數據

iii. 并在同時將修改記錄在Redo log buffer中

Oracle之查詢詳解

查詢是數據的一個重要操作。用戶發(fā)送查詢請求，經編譯軟件變異成二進制文件供服務器查詢，后返回查詢結果集給用戶，查詢會產生一個虛擬表，看到的是表形式顯示的結果，但結果并不真正的存儲，每次執(zhí)行查詢只是從數據表中提取數據，并按照表的形式顯示出來。

SELECT 列名

FGROM 表名

[WHERE 查詢條件表達式]

[GROUP BY 分組表達式]

[HAVING 分組查詢表達式]

[ORDER BY 排序的列名 [ASC或DESC]]

group by 用于對查詢的結果分組統(tǒng)計，通過對group by后面的名字進行分組后輸出結果。

group by后面還可以跟多列表示 多列分組 ，在多列分組時放前面的優(yōu)先分組。

group by 列名,列名

having 子句用于限制分組顯示結果，其只能和group by一起連用。在where中沒有辦法直接使用聚合函數，即sum avg等無法使用，所以引用了having，在having中可以使用這些函數。

order by 表示排序，后跟列名和排序方式。如果什么都不加默認為升序。ASC表示升序，DESC表示降序。

在Oracle中還可以設置多列排序

order by 列名1 升降,列名2 升降;

前面的為主要排序，后面的為次一級排序。

注：碰到自己與自己比較的情況下，不能用having，可以創(chuàng)建一個新列。

注：如果select語句同時包含group by,having,order by,按group by,having,order by排序

分組和聚合一起使用，目的是為了統(tǒng)計信息。

where是為了from服務的，只能跟 真實的字段 ，用來篩選from子句中指定的操作所產生的行

group by 用來分組where子句的輸出

having 用來從分組的結果中篩選行

order by用來對篩選的結果進行排序

（1）分組函數：max min?avg sum count

max表示該列的最大值，min表示該列的最小值，avg表示該列的平均值，sum表示該列的和，count表示該列的行數。

注：分組函數(max、min、avg、count、sum)只能出現在選擇列表中having子句、order by子句、不能出現在where子句和group by子句中。

（2）多表查詢

多表查詢是指兩個和兩個以上的表或者是視圖的查詢，在實際應用中，當查詢單個表不能滿足需求時，一般使用多表查詢。如：顯示sales部門位置和其員工的姓名，這種情況下需要使用到(dept表和emp表)。

多表查詢的連接一般可以分為：內連接、左外連接、右外連接、全連接。

注：在使用多表查詢的時候每個表可以設置別名，如果表指定了別名，那么語句中所有語句必須使用別名，而不能再使用實際表名。且在寫屬性的時候如果屬性為其中一個表特有的屬性則不需要寫別名，如果是兩個表都有則必須指定是哪一個表的哪個屬性格式為：表名.屬性名。

select 列名 from 表1 別名,表2 別名...

注：e是emp的別名，d是dept的別名。

但如果對表進行了操作則需要設置別名，如：查詢每個部門中工資高于該部門平均工資的員工人數。在其中有一個avg表，這個表必須設置別名（提醒：如果僅有一個被修改的表，則可以不設置別名，但如果有多個表則必須設置別名）。

內連接

內連接通過使用比較運算符來使每個表的通用列中的值匹配來組成一個新表，即：把兩個表中間共有的那些行拿出來進行連接，如果某些行不是兩個表共有的，則不進行連接。

select

from 表1

inner join 表2

on 匹配條件

或

select

from 表1 表2

where匹配條件

左外連接

左外連接與內連接的區(qū)別是：設置左外連接的時候設置了主表和附表，主表在前，附表在后。內連接是將兩個表匹配的地方輸出出來，而左外連接則是主表全寫，附表一一對應，附表有則加上，沒有不寫。

select

from 表1

left join 表2

on 匹配條件

右外連接

右外連接和左外連接基本相同只是右外連接的主表寫在后邊。

select

from 表1

right join 表2

on 匹配條件

全連接

全連接是在等值連接的基礎上將左表和右表的未匹配數據都加上，使用的關鍵字為full outer join或者full join。

select

from 表1

full join 表2

on 匹配條件

自連接

還有一種特殊情況即自連接，在Oracle中一個表無法與自己進行比較，所以當需要自己表的兩個信息做比較的時候也需要使用連接來連接，即同一張表的連結查詢。

（3）子查詢

子查詢是指嵌套在其他sql語句中的select語句，也叫嵌套查詢。sql語句執(zhí)行順序為從右到左執(zhí)行，所以在執(zhí)行查詢時會先執(zhí)行左側的子查詢后進行主查詢。

子查詢分為單行子查詢和多行子查詢，單行子查詢是指返回一行數據的子查詢語句，多行子查詢是指返回多行數據的查詢語句。子查詢還可以分為多列子查詢、多行子查詢、多列多行子查詢。

在進行子查詢時如果內部查詢不返回任何記錄，則外部條件中字段DEPTNO與NULL比較永遠為假，也就是說外部查詢不返回任何結果。

總結為：

單行子查詢是指子查詢只返回單列、單行數據

多行子查詢是指返回單列多行數據，都是針對單列而言的

多列子查詢則是指查詢返回多個列數據的子查詢語句

單行子查詢

where deptno = (單行數值)

多行子查詢

where deptno in ( 多行數值 )

多列子查詢：

where (job,deptno)=(select job,deptno from emp where ename='KING')

多列多行子查詢

where (job,deptno) in (select job,deptno from emp where ename='KING')

單行子查詢

在單行子查詢的外部查詢中可以使用=、、、=、=、等比較運算符。

內部查詢返回的結果必須與外部查詢條件中字段(DEPTNO)相匹配。

多行子查詢

在WHERE子句中使用多行子查詢時，可以使用多行比較運算符（IN,ALL,ANY）。

IN：等于任何一個。

ALL：和子查詢返回的所有值比較。例如：salALL(1,2,3)等價于sal3，即大于所有。

ANY：和子查詢返回的任意一個值比較。例如：salANY(1,2,3)等價于sal1，即大于任意一個就可以。

注：ANY運算符必須與單行比較運算符結合使用,并且返回行只要匹配子查詢的任何一個結果即可。

多列子查詢

多列子查詢和多行子查詢相同，只是使用多列子查詢的時候會有多列進行匹配。

（4）集合運算

為了合并多個select語句的結果，可以使用集合操作符號union，union all，intersect，minus。

union：該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行

union all：該操作與union相似，但是它不會取消重復行，而且不會排序

intersect：使用該操作符用于取得兩個結果集的交集

minus：使用該操作符用于取得兩個結果集的差集，它只會顯示存在第一個集合中，而不存在第二個集合中的數據

總結為集合運算就是將兩個或者多個結果集組合成一個結果集。

intersect ?交集 返回兩個查詢共有的記錄

union all ?并集 返回各個查詢的所有記錄，包括重復的記錄

union ? ? ?交集 返回各個查詢的所有記錄，不包括重復的記錄

MINUS ? 補集 返回第一個查詢檢查出的記錄減去第二個查詢檢索出來的記錄之后剩余的記錄

注意：當使用集合操作的時候，查詢所返回的列數以及列的類型必須匹配，列名可以不同。

（1）Distinct關鍵字

在Oracle中，可能出現若干相同的情況，那么可以用Distinct消除重復行

（2）多表查詢與單行子查詢可以實現相同的功能

查詢出銷售部（sales）下面的員工姓名，工作，工資

（3）顯示高于自己部門平均工資的員工信息

分析：

1.找到所有部門的平均工資

select deptno,avg(sal) from emp group by deptno;

2.找到所有人的工資信息

select ename,sal,deptno from emp;

3.把兩個結果集使用多表連接組合組合起來

select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno;

4.去掉低于平均工資的那些數據即可：

select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno and salavgsal;

（4）emp表介紹

字段? ? ? ? ? ? ? 類型? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 描述

empno ? ? ? ? ?NUMBER(4) ? ? ? ? ? ? ? ? 表示雇員編號，是唯一編號

ename ? ? ? ? ?VAECHAR2(10) ? ? ? ? ? 表示雇員姓名

job ? ? ? ? ? ? ? ?VARCHAR2(9) ? ? ? ? ? ? 表示工作職位

mgr ? ? ? ? ? ? ?NUMBER(4) ? ? ? ? ? ? ? ? 表示一個雇員的領導編號

hiredate ? ? ? DATE ? ? ? ? ? ? ? ? ? ? ? ? ? ?表示雇傭日期

sal ? ? ? ? ? ? ? NUMBER(7,2) ? ? ? ? ? ? ? 表示月薪，工資

comm ? ? ? ? ?NUMBER(7,2) ? ? ? ? ? ? ? 表示獎金，或者稱為傭金

deptno ? ? ? ? NUMBER(2) ? ? ? ? ? ? ? ? 部門編號

標題名稱：如何講解oracle 如何講解公司價值遠景
本文地址：http://vcdvsql.cn/article32/hiocpc.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供響應式網站、品牌網站制作、電子商務、靜態(tài)網站、動態(tài)網站、網站改版

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)