① sql和Oracle的功能上有什麼區別
許多軟體公司都理解開發不依賴於特定資料庫類型(例如Oracle、SQL Server、DB2)的應用程序的重要性，它可以讓客戶們選擇自己習慣的平台。一般來說，軟體開發人員都能夠識別出他們的負責資料庫維護的客戶，和必須使用現有平台和個性化的客戶。
關於Oracle和SQL Server之間的區別，已經有很多篇文章從企業的角度和資料庫管理員的角度出發描寫了兩者之間的一般差別。在這篇文章裡面，我將會從應用程序的角度向你描述SQL erver 和oracle平台之間的區別，並且將會討論開發不依賴於資料庫環境的應用程序的幾種可能的方法。與此同時，我不會再論討對於應用程序來說，兩個平台之間顯而易見的區別，例如表的分割和索引。
定義通用介面和語言有很少的幾種通用語言和介面可以讓應用程序不依賴於資料庫，想來也可以以同樣的方式應用在關系型資料庫上面: ANSI是美國國家標准局定義的，這是一家志願者成員的組織(用私人基金運轉)，他們在有關設備和程序等廣泛領域內開發了國家承認的標准。在資料庫領域， ANSI定義了編寫SQL命令的標准，假設命令可以運行在任何的資料庫上，而不需要更改命令的語法。ODBC是開放資料庫連接(ODBC)介面，微軟定義的，它可以讓應用程序訪問資料庫管理系統(DBMS)中的數據，使用SQL作為訪問數據的標准。ODBC允許最大的互聯性，這意味著一個單個的應用程序可以訪問不同的資料庫管理系統。然後，應用程序終端用戶可以添加開放資料庫連接(ODBC)資料庫驅動來將應用程序與他們所選擇的資料庫管理系統鏈接上。 OLE DB是ODBC的繼承者，是一組允許例如基於VB、C++、Access等任何可以連接到類似SQL Server、Oracle、DB2、MySQL等後台的「前台」的軟體組件。在許多種情況下，OLE DB組件都比原有的ODBC提供了好得多的性能。 JDBC(Java資料庫連接)應用程序介面是Java編程語言和廣泛范圍的資料庫，SQL資料庫和其他表列數據源(例如電子表格或者普通文本文件)之間，進行不依賴於資料庫的連接的行業標准。JDBD應用程序介面為基於SQL的資料庫訪問提供了調用級別的應用程序介面。真實世界中的通用介面不幸的是，並不是所有資料庫級別的命令都是ANSI，每個資料庫平台都有自己的擴展功能。對於ANSI或者通用介面，一般來說都代表著幾本功能，因此也可能意味著喪失了性能方面的競爭力。對於小型資料庫和小型應用程序來說，要維護對資料庫的通用訪問是簡單的，但是當資料庫和/或應用程序變得越來越大，越來越復雜，你就不得不向代碼中添加功能。
SQL Server和Oracle的常用函數對比
---------數學函數
1.絕對值
S:select abs(-1) value
O:select abs(-1) value from al
2.取整(大)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from al
3.取整（小）
S:select floor(-1.001) value
O:select floor(-1.001) value from al
4.取整（截取）
S:select cast(-1.002 as int) value
O:select trunc(-1.002) value from al
5.四捨五入
S:select round(1.23456,4) value 1.23460
O:select round(1.23456,4) value from al 1.2346
6.e為底的冪
S:select Exp(1) value 2.7182818284590451
O:select Exp(1) value from al 2.71828182
7.取e為底的對數
S:select log(2.7182818284590451) value 1
O:select ln(2.7182818284590451) value from al; 1
8.取10為底對數
S:select log10(10) value 1
O:select log(10,10) value from al; 1
9.取平方
S:select SQUARE(4) value 16
O:select power(4,2) value from al 16
10.取平方根
S:select SQRT(4) value 2
O:select SQRT(4) value from al 2
11.求任意數為底的冪
S:select power(3,4) value 81
O:select power(3,4) value from al 81
12.取隨機數
S:select rand() value
O:select sys.dbms_random.value(0,1) value from al;
13.取符號
S:select sign(-8) value -1
O:select sign(-8) value from al -1
----------三角函數相關
14.圓周率
S:SELECT PI() value 3.1415926535897931
O:不知道
15.sin,cos,tan 參數都以弧度為單位
例如：select sin(PI()/2) value 得到1（SQLServer）
16.Asin,Acos,Atan,Atan2 返回弧度
17.弧度角度互換(SQLServer，Oracle不知道)
DEGREES：弧度-〉角度
RADIANS：角度-〉弧度
---------數值間比較
18. 求集合最大值
S:select max(value) value from
(select 1 value
union
select -2 value
union
select 4 value
union
select 3 value)a
O:select greatest(1,-2,4,3) value from al
19. 求集合最小值
S:select min(value) value from
(select 1 value
union
select -2 value
union
select 4 value
union
select 3 value)a
O:select least(1,-2,4,3) value from al
20.如何處理null值(F2中的null以10代替)
S:select F1,IsNull(F2,10) value from Tbl
O:select F1,nvl(F2,10) value from Tbl
--------字元串函數
21.求字元序號
S:select ascii('a') value
O:select ascii('a') value from al
22.從序號求字元
S:select char(97) value
O:select chr(97) value from al
23.連接
S:select '11'+'22'+'33' value
O:select CONCAT('11','22')||33 value from al
23.子串位置 --返回3
S:select CHARINDEX('s','sdsq',2) value
O:select INSTR('sdsq','s',2) value from al
23.模糊子串的位置 --返回2,參數去掉中間%則返回7
// 本文轉自 C++Builder 研究 - http://www.ccrun.com/article.asp?i=996&d=dwn1rn
S:select patindex('%d%q%','sdsfasdqe') value
O:oracle沒發現，但是instr可以通過第四個參數控制出現次數
select INSTR('sdsfasdqe','sd',1,2) value from al 返回6
24.求子串
S:select substring('abcd',2,2) value
O:select substr('abcd',2,2) value from al
25.子串代替 返回aijklmnef
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from al
26.子串全部替換
S:Replace
O:select Translate('fasdbfasegas','fa','我' ) value from al
27.長度
S:len,datalength
O:length
28.大小寫轉換 lower,upper
29.單詞首字母大寫
S:沒發現
O:select INITCAP('abcd dsaf df') value from al
30.左補空格（LPAD的第一個參數為空格則同space函數）
S:select space(10)+'abcd' value
O:select LPAD('abcd',14) value from al
31.右補空格（RPAD的第一個參數為空格則同space函數）
S:select 'abcd'+space(10) value
O:select RPAD('abcd',14) value from al
32.刪除空格
S:ltrim,rtrim
O:ltrim,rtrim,trim
33. 重復字元串
S:select REPLICATE('abcd',2) value
O:沒發現
34.發音相似性比較(這兩個單詞返回值一樣，發音相同)
S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from al
SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比較soundex的差
返回0-4，4為同音，1最高
--------------日期函數
35.系統時間
S:select getdate() value
O:select sysdate value from al
36.前後幾日
直接與整數相加減
37.求日期
S:select convert(char(10),getdate(),20) value
O:select trunc(sysdate) value from al
select to_char(sysdate,'yyyy-mm-dd') value from al
38.求時間
S:select convert(char(8),getdate(),108) value
O:select to_char(sysdate,'hh24:mm:ss') value from al
39.取日期時間的其他部分
S:DATEPART 和 DATENAME 函數 （第一個參數決定）
O:to_char函數 第二個參數決定
參數---------------------------------下表需要補充
year yy, yyyy
quarter qq, q (季度)
month mm, m (m O無效)
dayofyear dy, y (O表星期)
day dd, d (d O無效)
week wk, ww (wk O無效)
weekday dw (O不清楚)
Hour hh,hh12,hh24 (hh12,hh24 S無效)
minute mi, n (n O無效)
second ss, s (s O無效)
millisecond ms (O無效)
----------------------------------------------
40.當月最後一天
S:不知道
O:select LAST_DAY(sysdate) value from al
41.本星期的某一天（比如星期日）
S:不知道
O:SELECT Next_day(sysdate,7) vaule FROM DUAL;
42.字元串轉時間
S:可以直接轉或者select cast('2004-09-08'as datetime) value
O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;
43.求兩日期某一部分的差（比如秒）
S:select datediff(ss,getdate(),getdate()+12.3) value
O:直接用兩個日期相減（比如d1-d2=12.3）
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44.根據差值求新的日期（比如分鍾）
S:select dateadd(mi,8,getdate()) value
O:SELECT sysdate+8/60/24 vaule FROM DUAL;
45.求不同時區時間
S:不知道
O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;
-----時區參數,北京在東8區應該是Ydt-------
AST ADT 大西洋標准時間
BST BDT 白令海標准時間
CST CDT 中部標准時間
EST EDT 東部標准時間
GMT 格林尼治標准時間
HST HDT 阿拉斯加—夏威夷標准時間
MST MDT 山區標准時間
NST 紐芬蘭標准時間
PST PDT 太平洋標准時間
YST YDT YUKON標准時間
Oracle支持的字元函數和它們的Microsoft SQL Server等價函數:
函數 Oracle Microsoft SQL Server
把字元轉換為ASCII ASCII ASCII
字串連接 CONCAT (expression + expression)
把ASCII轉換為字元 CHR CHAR
返回字元串中的開始字元（左起） INSTR CHARINDEX
把字元轉換為小寫 LOWER LOWER
把字元轉換為大寫 UPPER UPPER
填充字元串的左邊 LPAD N/A
清除開始的空白 LTRIM LTRIM
清除尾部的空白 RTRIM RTRIM
字元串中的起始模式（pattern） INSTR PATINDEX
多次重復字元串 RPAD REPLICATE
字元串的語音表示 SOUNDEX SOUNDEX
重復空格的字串 RPAD SPACE
從數字數據轉換為字元數據 TO_CHAR STR
子串 SUBSTR SUBSTRING
替換字元 REPLACE STUFF
將字串中的每個詞首字母大寫 INITCAP N/A
翻譯字元串 TRANSLATE N/A
字元串長度 LENGTH DATELENGTH or LEN
列表中最大的字元串 GREATEST N/A
列表中最小的字元串 LEAST N/A
如果為NULL則轉換字串 NVL ISNULL
日期函數
函數 Oracle Microsoft SQL Server
日期相加 (date column +/- value) or
ADD_MONTHS DATEADD
兩個日期的差 (date column +/- value) or
MONTHS_BETWEEN DATEDIFF
當前日期和時間 SYSDATE GETDATE()
一個月的最後一天 LAST_DAY N/A
時區轉換 NEW_TIME N/A
日期後的第一個周日 NEXT_DAY N/A
代表日期的字元串 TO_CHAR DATENAME
代表日期的整數 TO_NUMBER
(TO_CHAR)) DATEPART
日期舍入 ROUND CONVERT
日期截斷 TRUNC CONVERT
字元串轉換為日期 TO_DATE CONVERT
如果為NULL則轉換日期 NVL ISNULL
轉換函數
函數 Oracle Microsoft SQL Server
數字轉換為字元 TO_CHAR CONVERT
字元轉換為數字 TO_NUMBER CONVERT
日期轉換為字元 TO_CHAR CONVERT
字元轉換為日期 TO_DATE CONVERT
16進制轉換為2進制 HEX_TO_RAW CONVERT
2進制轉換為16進制 RAW_TO_HEX CONVERT
其它行級別的函數
函數 Oracle Microsoft SQL Server
返回第一個非空表達式 DECODE COALESCE
當前序列值 CURRVAL N/A
下一個序列值 NEXTVAL N/A
如果exp1 = exp2, 返回null DECODE NULLIF
用戶登錄賬號ID數字 UID SUSER_ID
用戶登錄名 USER SUSER_NAME
用戶資料庫ID數字 UID USER_ID
用戶資料庫名 USER USER_NAME
當前用戶 CURRENT_USER CURRENT_USER
用戶環境(audit trail) USERENV N/A
在CONNECT BY子句中的級別 LEVEL N/A
合計函數
函數 Oracle Microsoft SQL Server
Average AVG AVG
Count COUNT COUNT
Maximum MAX MAX
Minimum MIN MIN
Standard deviation STDDEV STDEV or STDEVP
Summation SUM SUM
Variance VARIANCE VAR or VARP
Oracle還有一個有用的函數EXTRACT,提取並且返回日期時間或時間間隔表達式中特定的時間域:
EXTRACT(YEAR FROM 日期)
② oracle中如何執行sql語句
如果用sqlplus，那麼在你行的結尾要加分號(;)，然後回車就行了。
例如:
select * from cat;
③ 如何利用BAT文件登陸OracleSql並執行多個sql腳本PLSQL語句
首先，做一個簡單的sql文件（log.sql）。
在這個SQL文件中我們創建了一個表。
接下來，我們來建一個bat批處理文件（log.bat）。
在這個bat文件裡面我們調用絕對路徑的sql文件，並將執行過程及結果輸出到log.txt文件中。
最後，我們運行bat文件，執行多個sql腳本plsql語句。
④ 如何使用oracle提供的SQL
Sql性能非常差的時候,oracle提供了SQL_TRACE來跟蹤sql的執行情況。
註：分析sql的方式比較多，還有根據優化器、sql執行計劃來分析。
SQL_TRACE能夠將sql執行的過程輸出到一個trace文件裡面。
首先設置自己定義的trace文件的標識方便查找。
alter session set tracefile_identifier='mytest';
然後對當前會話啟動SQL_TRACE，最好不要一直打開該開關，代價比較大。
alter session set sql_trace=true;
然後我們執行一條sql語句。
最後關閉該開關的狀態。
alter session set sql_trace=false;
我們可以從目錄%ORACLE_BASE%/diag/rdbms/orcl/orcl/trace(11g版本的路徑，如果是10g的應該不一樣)中
找到自己定義的trace文件。
原始的trace文件的可讀性不高，我們一般使用oracle自帶的工具，tkprof來處理這個trace文件。我們可以查看tkprof的幫助。
tkprof orcl_ora_3820_mytest.trc out.txt
我們來看剛才生成的trace文件，頭部信息描述了tkprof 的版本以及報告中一些列的含義，對於任何一條sql語句，都應該包含Parse—sql分析階段，Execute—sql執行階段，Fetch—數據提取階段，橫向的列如圖所示，包含消耗cpu時間0.00秒，操作總耗時0.04秒，物理讀取了0個數據塊，沒有發生current方式的讀取(一般在update會發生)，一共提取記錄1條。
Misses in library cache ring parse: 0表示這是一次軟分析(關於硬分析和軟分析下面會接著談到)
Optimizer mode: ALL_ROWS表示oracle的優化器模式為ALL_ROWS。這也就是前面提到的另外的分析方式優化器。
下面是sql執行的具體計劃，可以看到執行計劃選擇的是全表掃描。
經過處理以後的trace文件的確比較容易看明白，它有助於我們分析sql的性能問題。
下面我通過一個trace實例來解釋一下，為什麼OLTP系統中需要變數綁定機制。
當用戶和資料庫建立連接，並發送一條sql語句以後，oracle會對該sql進行hash函數運算(hash演算法提供了一種快速存取數據的方法，它用一種演算法建立鍵值與真實值之間的對應關系,每一個真實值只能有一個鍵值，但是一個鍵值可以對應多個真實值，以方便存取)，得到一個hash值，然後到共享池中尋找是否有匹配的hash值的sql存在，如果有，就直接使用該sql的執行計劃去執行sql。如果沒有，oracle就會認為這是一條新的sql語句，然後按照語法分析，語義分析，生成執行計劃，執行sql這些步驟來執行最終把結果返回給用戶。這些步驟也被成為硬分析，可以想像，如果減少硬分析，能夠大大降低資料庫花費在sql解析上的資源開銷。
我們先執行一條sql 1000次，比較綁定變數和不綁定變數的差異。得到結果以後，要計算實際的消耗，我們需要把OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS以及OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS的時間累計起來，前者表示數據字典表的相關的信息，包含許可權控制等，後者表示sql所衍生出的遞歸sql語句的信息。可以看到綁定變數的，整條語句執行時間為0.22+0.02=0.24秒，CPU時間0.18+0.03=0.21秒，分析次數3次，執行次數1003次。而不綁定變數的時候，整條語句執行時間為0.28+1.29=1.57秒，CPU時間0.31+1.26=1.57秒，分析次數1002次，執行次數1003次。可見綁定變數的確能夠帶來更低的開銷。(如何設計資料庫中使用綁定變數也是和系統息息相關的，很多資料庫問題都是在設計以後就已經存在的)
應用級調優分析:
就通常所說的三層架構來說，中間件這一層能夠起到一個緩沖池的作用，如果並發用戶數到3000這個數量級的時候，中間件能夠控制不是所有的用戶都能直接連接到資料庫，當然這里的程序會快速響應用戶請求，保證緩沖池的隊列等待不會很久。
對應用這一級別的調優，主要集中在app程序，中間件的監控，集群配置等方面。如果是發現應用級別的問題，首先要分析是配置問題，還是程序本身的問題。如果並發用戶數很大，中間件的線程池最大值配置過小，會導致在請求隊列堆積，表現就是線程監控視圖中，請求的隊列堆積比較多，一般可以調整線程池最大值來解決。我們來看看weblogic的監控視圖。
考慮到如果為每一個請求都創建一個新線程來處理的話，那麼我們難以在系統中實現足夠數量的線程。不受限制的創建線程可能耗盡系統資源，因此引入了線程池。線程池的思想是在進程開始時創建一定數量的線程並將它們置入一個池(pool)中，線程在這個池中等待工作。當伺服器接收到一個請求時，它就從池中喚醒一個線程(如果有可用的線程)，由它來處理請求。一旦線程服務完畢，它就返回線程池等待後面的工作。
線程池利用已存在的線程服務請求要比等待創建一個線程要快，並且線程池限制了線程的數量。
如果懷疑是程序的問題，我們一般可以通過java自帶的工具來幫助分析，工具很多。這里我主要提到一個jdk1.6以後附帶的jvisualvm。
我們打開jdk1.6，找到並運行jvisualvm.exe。
我們發現應用程序分為本地，遠程兩部分。本地包含本地運行的java進程，遠程能夠通過配置連接到遠程伺服器上的java進程。我們先啟動一個tomcat。可以看到本地應用程序已經打開了一個帶有tomcat以及進程標識id的菜單。雙擊打開。這里我們一般關心2個視圖。監視、線程。
其中監視視圖比較關心垃圾回收活動(顧名思義，回收那些在程序裡面不再使用到的內存空間)，堆內存變化。如果在壓力測試過程中，堆內存變化是一個逐漸上漲的趨勢，並且經過多次手動gc回收，還是保持這個趨勢，說明內存泄漏的可能性很大。如果猜測有內存泄漏，可以通過分析java的heap mp。JVM (java虛擬機)記錄下問題發生時系統的運行狀態並將其存儲在轉儲(mp)文件中。Heap mp就是這樣一種文件形式。
線程視圖比較關心線程的當前執行狀態，這里可以生成另一種轉儲文件 Java mp。Java mp，也叫做 Thread mp，是 JVM 故障診斷中最重要的轉儲文件之一。JVM 的許多問題都可以使用這個文件進行診斷，其中比較典型的包括線程阻塞，CPU 使用率過高，JVM Crash，堆內存不足，和類裝載等問題。其中線程阻塞更加常見。
原文轉自：http://blog.csdn.net/xuyubotest/article/details/8158241
⑤ oracle中如何優化sql語句的，用什麼方法
2. 選擇最有效率的表名順序(只在基於規則的優化器中有效)
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秒
如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
例如: EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
將比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
3. WHERE子句中的連接順序．
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
例如:
(低效,執行時間156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = 『MANAGER』
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,執行時間10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = 『MANAGER』;
4. SELECT子句中避免使用 『 * 『
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 『*』 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將』*』 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.
5. 減少訪問資料庫的次數
當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀數據塊等等. 由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量.
例如,
以下有三種方法可以檢索出雇員號等於0342或0291的職員.
方法1 (最低效)
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;
方法3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
注意:
在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索數據量 ,建議值為200.
6. 使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE 『SMITH%』;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE 『SMITH%』;
你可以用DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO,0020,』X』,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,』X』,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE 『SMITH%』;
類似的,DECODE函數也可以運用於GROUP BY 和ORDER BY子句中.
7. 用TRUNCATE替代DELETE
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)
而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢復.因此很少的資源被調用,執行時間也會很短.
TRUNCATE只在刪除全表或分區適用,TRUNCATE是DDL不是DML
8. 用Where子句替換HAVING子句
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != 『SYDNEY』
AND REGION != 『PERTH』
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != 『SYDNEY』
AND REGION != 『PERTH』
GROUP BY REGION
HAVING 中的條件一般用於對一些集合函數的比較,如COUNT() 等等. 除此而外,一般的條件應該寫在WHERE子句中
9. 減少對表的查詢
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update 多個Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
10. 通過內部函數提高SQL效率.
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調用下面的函數可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,』?』));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,』?』));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
經常在論壇中看到如 』能不能用一個SQL寫出….』 的貼子, 殊不知復雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
11. 使用表的別名(Alias)
當在SQL語句中連接多個表時, 請使用表的別名並把別名前綴於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬
12. 用EXISTS替代IN
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
低效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = 『MELB』)
高效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND EXISTS (SELECT 『X』
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 『MELB』)
相對來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節中將指出
13. 用NOT EXISTS替代NOT IN
在子查詢中,NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例如:
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』);
14. 用表連接替換EXISTS
通常來說 , 採用表連接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 『X』
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 『A』);
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = 『A』 ;
在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP
15. 用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換
例如:
低效:
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);
EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果.
16. 基礎表的選擇
基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據優化器的不同, SQL語句中基礎表的選擇是不一樣的.
如果你使用的是CBO (COST BASED OPTIMIZER),優化器會檢查SQL語句中的每個表的物理大小,索引的狀態,然後選用花費最低的執行路徑.
如果你用RBO (RULE BASED OPTIMIZER) , 並且所有的連接條件都有索引對應, 在這種情況下, 基礎表就是FROM 子句中列在最後的那個表.
舉例:
SELECT A.NAME , B.MANAGER
FROM WORKER A,
LODGING B
WHERE A.LODGING = B.LODING;
由於LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORKER表將被作為查詢中的基礎表.
17. 多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時, ORACLE會同時使用多個索引並在運行時對它們的記錄進行合並, 檢索出僅對全部索引有效的記錄.
在ORACLE選擇執行路徑時,唯一性索引的等級高於非唯一性索引. 然而這個規則只有
當WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的.
如果不同表中兩個想同等級的索引將被引用, FROM子句中表的順序將決定哪個會被率先使用. FROM子句中最後的表的索引將有最高的優先順序.
如果相同表中兩個想同等級的索引將被引用, WHERE子句中最先被引用的索引將有最高的優先順序.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = 『A』;
這里,DEPTNO索引將被最先檢索,然後同EMP_CAT索引檢索出的記錄進行合並. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
18. 等式比較和范圍比較
當WHERE子句中有索引列, ORACLE不能合並它們,ORACLE將用范圍比較.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = 『A』;
這里只有EMP_CAT索引被用到,然後所有的記錄將逐條與DEPTNO條件進行比較. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
19. 不明確的索引等級
當ORACLE無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在WHERE子句中被列在最前面的.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > 『A』;
這里, ORACLE只用到了DEPT_NO索引. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
我們來試一下以下這種情況:
SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE
SQL> select * from emp where empno >= 2 and emp_type = 'A' ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)
雖然EMPNO是唯一性索引,但是由於它所做的是范圍比較, 等級要比非唯一性索引的等式比較低!
20. 強制索引失效
如果兩個或以上索引具有相同的等級,你可以強制命令ORACLE優化器使用其中的一個(通過它,檢索出的記錄數量少) .
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || 『』 = 『A』 /*EMP_TYPE上的索引將失效*/
這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你希望單獨優化幾個SQL時才能採用它.
這里有一個例子關於何時採用這種策略,
假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = 『A』
AND EMP_CLASS = 『X』;
優化器會注意到EMP_TYPE上的索引並使用它. 這是目前唯一的選擇. 如果,一段時間以後, 另一個非唯一性建立在EMP_CLASS上,優化器必須對兩個索引進行選擇,在通常情況下,優化器將使用兩個索引並在他們的結果集合上執行排序及合並. 然而,如果其中一個索引（EMP_TYPE）接近於唯一性而另一個索引（EMP_CLASS）上有幾千個重復的值. 排序及合並就會成為一種不必要的負擔. 在這種情況下,你希望使優化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = 『A』
AND EMP_CLASS||』』 = 『X』;
21. 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
這是一個非常實用的規則，請務必牢記
22. 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．
在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這里，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
23. 避免在索引列上使用NOT
通常， 我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的
影響. 當ORACLE」遇到」NOT ,!=,他就會停止使用索引轉而執行全表掃描.
舉例:
低效: (這里,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE != 0;
高效: (這里,使用了索引)
SELECT …
FROM DEPT
知道的基本就這么多了，要加分啊~~O(∩_∩)O~
⑥ SQL和Oracle的區別
至於SqlServer 和Oracle 的區別在於：
一、開放性 1. SQL Server 只能在windows上運行，沒有絲毫的開放性，操作系統的系統的穩定對資料庫是十分重要的。Windows9X系列產品是偏重於桌面應用，NT server只適合中小型企業。而且windows平台的可靠性，安全性和伸縮性是非常有限的。它不象unix那樣久經考驗，尤其是在處理大資料庫。 2. Oracle 能在所有主流平台上運行（包括 windows）。完全支持所有的工業標准。採用完全開放策略。可以使客戶選擇最適合的解決方案。對開發商全力支持。
二、可伸縮性，並行性 1. SQL server 並行實施和共存模型並不成熟，很難處理日益增多的用戶數和數據卷，伸縮性有限。 2. Oracle 並行伺服器通過使一組結點共享同一簇中的工作來擴展windownt的能力，提供高可用性和高伸縮性的簇的解決方案。如果windowsNT不能滿足需要，用戶可以把資料庫移到UNIX中。Oracle的並行伺服器對各種UNIX平台的集群機制都有著相當高的集成度。
三、性能 1. SQL Server 多用戶時性能不佳 2. Oracle 性能最高， 保持開放平台下的TPC-D和TPC-C的世界記錄。
四、客戶端支持及應用模式 1. SQL Server C/S結構，只支持windows客戶，可以用ADO、DAO、OLEDB、ODBC連接。 2. Oracle 多層次網路計算，支持多種工業標准，可以用ODBC、JDBC、OCI等網路客戶連接。
五、操作簡便 1. SQL Server 操作簡單，但只有圖形界面。 2. Oracle 較復雜，同時提供GUI和命令行，在windowsNT和unix下操作相同。
六、使用風險 1. SQL server 完全重寫的代碼，經歷了長期的測試，不斷延遲，許多功能需要時間來證明。並不十分兼容。 2. Oracle 長時間的開發經驗，完全向下兼容。得到廣泛的應用。完全沒有風險。 最後價格上 ORACLE貴過SQLSRVER
⑦ SQL和ORACLE是什麼
SQLStructured
Quevy
Language(結構化查詢語言)縮寫SQL專資料庫建立操作命令集種功能齊全資料庫語言使用需要發做命令做用使用者考慮SQL功能強、簡單易、使用便已經資料庫操作基礎並且現幾乎所資料庫均支持SQL
Oracle現用資料庫發軟體
所要資料庫要SQL,關資料庫面軟體Oracle
⑧ oracle中對一張表的某個欄位進行查詢並且去重sql怎麼寫請大神示範
selectdistinct欄位from表名
或者
select欄位from表名groupby欄位
這個意思？
⑨ ORACLE 和 SQL區別
這兩種資料庫在不同方面都有著很大的區別，以下是具體的區別：
1.開放性： 1. SQL Server 只能在windows上運行，沒有絲毫的開放性，操作系統的系統的穩定對資料庫是十分重要的。Windows9X系列產品是偏重於桌面應用，NT server只適合中小型企業。而且windows平台的可靠性，安全性和伸縮性是非常有限的。它不象unix那樣久經考驗，尤其是在處理大資料庫。 2. Oracle 能在所有主流平台上運行（包括 windows）。完全支持所有的工業標准。採用完全開放策略。可以使客戶選擇最適合的解決方案。對開發商全力支持。
2.可伸縮性：並行性 1. SQL server 並行實施和共存模型並不成熟，很難處理日益增多的用戶數和數據卷，伸縮性有限。 2. Oracle 並行伺服器通過使一組結點共享同一簇中的工作來擴展windownt的能力，提供高可用性和高伸縮性的簇的解決方案。如果windowsNT不能滿足需要，用戶可以把資料庫移到UNIX中。Oracle的並行伺服器對各種UNIX平台的集群機制都有著相當高的集成度。
3.性能： 1. SQL Server 多用戶時性能不佳 2. Oracle 性能最高， 保持開放平台下的TPC-D和TPC-C的世界記錄。
4.客戶端支持及應用模式 ：1. SQL Server C/S結構，只支持windows客戶，可以用ADO、DAO、OLEDB、ODBC連接。 2. Oracle 多層次網路計算，支持多種工業標准，可以用ODBC、JDBC、OCI等網路客戶連接。
5.操作簡便性： 1. SQL Server 操作簡單，但只有圖形界面。 2. Oracle 較復雜，同時提供GUI和命令行，在windowsNT和unix下操作相同。
6.使用風險性 ：1. SQL server 完全重寫的代碼，經歷了長期的測試，不斷延遲，許多功能需要時間來證明。並不十分兼容。 2. Oracle 長時間的開發經驗，完全向下兼容。得到廣泛的應用。完全沒有風險。
7.價格：ORACLE貴過SQLSRVER
⑩ 一個簡單的oracle sql 語句
列印出空值的原因很簡單，oracle中
rownum永遠按照默認的順序生成，並且rownum只能使用<
<=，不能使用>
>=。
可以這樣寫：select
*
2
from
(select
rownum
r,e.*
3
from
(select
*
from
emp
order
by
sal)
e
4
where
rownum
<=8
5
)
6
where
r
>=5;
經過子查詢後，此時的別名r只是一個普通的欄位，就不再是按照默認生成的了