這裡蒐索程式師資訊,查找有用的技術資料
當前位置:首頁 » 編程語言 » 寫一個你認為最高效sql語句
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

寫一個你認為最高效sql語句

發布時間: 2022-04-21 17:57:11

A. sql語句問題

這是個sql語句的基本學習手冊
你可以自己查一下

SQL語句學習手冊實例版

表操作

例 1 對於表的教學管理資料庫中的表 STUDENTS ,可以定義如下:
CREATE TABLE STUDENTS
(SNO NUMERIC (6, 0) NOT NULL
SNAME CHAR (8) NOT NULL
AGE NUMERIC(3,0)
SEX CHAR(2)
BPLACE CHAR(20)
PRIMARY KEY(SNO))
例 2 對於表的教學管理資料庫中的表 ENROLLS ,可以定義如下:
CREATE TABLE ENROLLS
(SNO NUMERIC(6,0) NOT NULL
CNO CHAR(4) NOT NULL
GRADE INT
PRIMARY KEY(SNO,CNO)
FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)
FOREIGN KEY(CNO) REFERENCES COURSES(CNO)
CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)))
例 3 根據表的 STUDENTS 表,建立一個只包含學號、姓名、年齡的女學生表。
CREATE TABLE GIRL
AS SELECT SNO, SNAME, AGE
FROM STUDENTS
WHERE SEX=' 女 ';
例 4 刪除教師表 TEACHER 。
DROP TABLE TEACHER
例 5 在教師表中增加住址列。
ALTER TABLE TEACHERS
ADD (ADDR CHAR(50))
例 6 把 STUDENTS 表中的 BPLACE 列刪除,並且把引用 BPLACE 列的所有視圖和約束也一起刪除。
ALTER TABLE STUDENTS
DROP BPLACE CASCADE
例 7 補充定義 ENROLLS 表的主關鍵字。
ALTER TABLE ENROLLS
ADD PRIMARY KEY (SNO,CNO) ;

視圖操作(虛表)

例 9 建立一個只包括教師號、姓名和年齡的視圖 FACULTY 。 ( 在視圖定義中不能包含 ORDER BY 子句 )
CREATE VIEW FACULTY
AS SELECT TNO, TNAME, AGE
FROM TEACHERS
例 10 從學生表、課程表和選課表中產生一個視圖 GRADE_TABLE , 它包括學生姓名、課程名和成績。
CREATE VIEW GRADE_TABLE
AS SELECT SNAME,CNAME,GRADE
FROM STUDENTS,COURSES,ENROLLS
WHERE STUDENTS.SNO = ENROLLS.SNO AND
COURSES.CNO=ENROLLS.CNO
例 11 刪除視圖 GRADE_TABLE
DROP VIEW GRADE_TABLE RESTRICT

索引操作

例 12 在學生表中按學號建立索引。
CREATE UNIQUE INDEX ST
ON STUDENTS (SNO,ASC)
例 13 刪除按學號所建立的索引。
DROP INDEX ST

資料庫模式操作

例 14 創建一個簡易教學資料庫的資料庫模式 TEACHING_DB ,屬主為 ZHANG 。
CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG
例 15 刪除簡易教學資料庫模式 TEACHING_DB 。(( 1 )選用 CASCADE ,即當刪除資料庫模式時,則本資料庫模式和其下屬的基本表、視圖、索引等全部被刪除。( 2 )選用 RESTRICT ,即本資料庫模式下屬的基本表、視圖、索引等事先已清除,才能刪除本資料庫模式,否則拒絕刪除。)
DROP SCHEMA TEACHING_DB CASCADE
單表操作

例 16 找出 3 個學分的課程號和課程名。
SELECT CNO, CNAME
FROM COURSES
WHERE CREDIT = 3
例 17 查詢年齡大於 22 歲的學生情況。
SELECT *
FROM STUDENTS
WHERE AGE > 22
例 18 找出籍貫為河北的男生的姓名和年齡。
SELECT SNAME, AGE
FROM STUDENTS
WHERE BPLACE = ' 河北 ' AND SEX = ' 男 '
例 19 找出年齡在 20 ~ 23 歲之間的學生的學號、姓名和年齡,並按年齡升序排序。 (ASC (升序)或 DESC (降序)聲明排序的方式,預設為升序。 )
SELECT SNO, SNAME, AGE
FROM STUDENTS
WHERE AGE BETWEEN 20 AND 23
ORDER BY AGE
例 20 找出年齡小於 23 歲、籍貫是湖南或湖北的學生的姓名和性別。(條件比較運算符=、< 和邏輯運算符 AND (與),此外還可以使用的運算符有:>(大於)、>=(大於等於)、<=(小於等於)、<>(不等於)、 NOT (非)、 OR (或)等。
謂詞 LIKE 只能與字元串聯用,常常是 「 <列名> LIKE pattern」 的格式。特殊字元 「_」 和 「%」 作為通配符。
謂詞 IN 表示指定的屬性應與後面的集合(括弧中的值集或某個查詢子句的結果)中的某個值相匹配,實際上是一系列的 OR (或)的縮寫。謂詞 NOT IN 表示指定的屬性不與後面的集合中的某個值相匹配。
謂詞 BETWEEN 是 「 包含於 … 之中 」 的意思。)
SELECT SNAME, SEX
FROM STUDENTS
WHERE AGE < 23 AND BPLACE LIKE' 湖% '

SELECT SNAME, SEX
FROM STUDENTS
WHERE AGE < 23 AND BPLACE IN ( ' 湖南 ' , ' 湖北 ' )
例 22 找出學生表中籍貫是空值的學生的姓名和性別。(在 SQL 中不能使用條件:<列名>= NULL 。在 SQL 中只有一個特殊的查詢條件允許查詢 NULL 值:)
SELECT SNAME, SEX
FROM STUDENTS
WHERE BPLACE IS NULL
多表操作

例 23 找出成績為 95 分的學生的姓名。(子查詢)
SELECT SNAME
FROM STUDENTS
WHERE SNO =
(SELECT SNO
FROM ENROLLS
WHERE GRADE = 95)
例 24 找出成績在 90 分以上的學生的姓名。
SELECT SNAME
FROM STUDENTS
WHERE SNO IN
(SELECT SNO
FROM ENROLLS
WHERE GRADE > 90)

SELECT SNAME
FROM STUDENTS
WHERE SNO = ANY
(SELECT SNO
FROM ENROLLS
WHERE GRADE > 90)
例 25 查詢全部學生的學生名和所學課程號及成績。(連接查詢)
SELECT SNAME, CNO, GRADE
FROM STUDENTS, ENROLLS
WHERE STUDENTS.SNO = ENROLLS.SNO
例 26 找出籍貫為山西或河北,成績為 90 分以上的學生的姓名、籍貫和成績。(當構造多表連接查詢命令時,必須遵循兩條規則。第一,連接條件數正好比表數少 1 (若有三個表,就有兩個連接條件 ) ;第二,若一個表中的主關鍵字是由多個列組成,則對此主關鍵字中的每一個列都要有一個連接條件(也有少數例外情況))
SELECT SNAME, BPLACE, GRADE
FROM STUDENTS, ENROLLS
WHERE BPLACE IN (『 山西 ' , 『 河北 ') AND GRADE >= 90 AND STUDENTS.SNO=ENROLLS.SNO
例 28 查出課程成績在 80 分以上的女學生的姓名、課程名和成績。( FROM 子句中的子查詢)
SELECT SNAME,CNAME, GRADE
FROM (SELECT SNAME, CNAME , GRADE
FROM STUDENTS, ENROLLS,COURSES
WHERE SEX = ' 女 ')
AS TEMP (SNAME, CNAME,GRADE)
WHERE GRADE > 80
表達式與函數的使用

例 29 查詢各課程的學時數。(算術表達式由算術運算符+、-、 * 、/與列名或數值常量所組成。)
SELECT CNAME,COURSE_TIME = CREDIT*16
FROM COURSES
例 30 找出教師的最小年齡。(內部函數: SQL 標准中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函數,稱之為聚集函數( Set Function )。 COUNT 函數的結果是該列統計值的總數目, SUM 函數求該列統計值之和, AVG 函數求該列統計值之平均值, MAX 函數求該列最大值, MIN 函數求該列最小值。)
SELECT MIN(AGE)
FROM TEACHERS
例 31 統計年齡小於等於 22 歲的學生人數。(統計)
SELECT COUNT(*)
FROM STUDENTS
WHERE AGE < = 22
例 32 找出學生的平均成績和所學課程門數。
SELECT SNO, AVG(GRADE), COURSES = COUNT(*)
FROM ENROLLS
GROUP BY SNO

例 34 找出年齡超過平均年齡的學生姓名。
SELECT SNAME
FROM STUDENTS
WHERE AGE >
(SELECT AVG(AGE)
FROM STUDENTS)
例 35 找出各課程的平均成績,按課程號分組,且只選擇學生超過 3 人的課程的成績。( GROUP BY 與 HAVING
GROUP BY 子句把一個表按某一指定列(或一些列)上的值相等的原則分組,然後再對每組數據進行規定的操作。
GROUP BY 子句總是跟在 WHERE 子句後面,當 WHERE 子句預設時,它跟在 FROM 子句後面。
HAVING 子句常用於在計算出聚集之後對行的查詢進行控制。)
SELECT CNO, AVG(GRADE), STUDENTS = COUNT(*)
FROM ENROLLS
GROUP BY CNO
HAVING COUNT(*) >= 3

相關子查詢

例 37 查詢沒有選任何課程的學生的學號和姓名。(當一個子查詢涉及到一個來自外部查詢的列時,稱為相關子查詢( Correlated Subquery) 。相關子查詢要用到存在測試謂詞 EXISTS 和 NOT EXISTS ,以及 ALL 、 ANY ( SOME )等。)
SELECT SNO, SNAME
FROM STUDENTS
WHERE NOT EXISTS
(SELECT *
FROM ENROLLS
WHERE ENROLLS.SNO=STUDENTS.SNO)
例 38 查詢哪些課程只有男生選讀。
SELECT DISTINCT CNAME
FROM COURSES C
WHERE ' 男 ' = ALL
(SELECT SEX
FROM ENROLLS , STUDENTS
WHERE ENROLLS.SNO=STUDENTS.SNO AND
ENROLLS.CNO=C.CNO)
例 39 要求給出一張學生、籍貫列表,該表中的學生的籍貫省份,也是其他一些學生的籍貫省份。
SELECT SNAME, BPLACE
FROM STUDENTS A
WHERE EXISTS
(SELECT *
FROM STUDENTS B
WHERE A.BPLACE=B.BPLACE AND
A.SNO < > B.SNO)
例 40 找出選修了全部課程的學生的姓名。
本查詢可以改為:查詢這樣一些學生,沒有一門課程是他不選修的。
SELECT SNAME
FROM STUDENTS
WHERE NOT EXISTS
(SELECT *
FROM COURSES
WHERE NOT EXISTS
(SELECT *
FROM ENROLLS
WHERE ENROLLS.SNO = STUDENTS.SNO
AND ENROLLS.CNO = COURSES.CNO))
關系代數運算

例 41 設有某商場工作人員的兩張表:營業員表 SP_SUBORD 和營銷經理表 SP_MGR ,其關系數據模式如下:
SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
其中,屬性 SALPERS_ID 為工作人員的編號 , SALPERS_NAME 為工作人員的姓名 , MANAGER_ID 為所在部門經理的編號 , OFFICE 為工作地點。
若查詢全部商場工作人員,可以用下面的 SQL 語句:
(SELECT * FROM SP_SUBORD)
UNION
(SELECT * FROM SP_MGR)
或等價地用下面的 SQL 語句:
SELECT *
FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)
( 2 ) INTERSECT
(SELECT * FROM SP_SUBORD)
INTERSECT
(SELECT * FROM SP_MGR)
或等價地用下面的 SQL 語句:
SELECT *
FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)
或用帶 ALL 的 SQL 語句:
(SELECT * FROM SP_SUBORD)
INTERSECT ALL
(SELECT * FROM SP_MGR)

SELECT *
FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)
( 3 ) EXCEPT
(SELECT * FROM SP_MGR)
EXCEPT
(SELECT * FROM SP_SUBORD)
或等價地用下面的 SQL 語句:
SELECT *
FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)
或用帶 ALL 的 SQL 語句:
(SELECT * FROM SP_MGR)
EXCEPT ALL
(SELECT * FROM SP_SUBORD)
例 42 查詢籍貫為四川、課程成績在 80 分以上的學生信息及其成績。(自然連接)
(SELECT * FROM STUDENTS
WHERE BPLACE=『 四川 ')
NATURAL JOIN
(SELECT * FROM ENROLLS
WHERE GRADE >=80)
例3.43 列出全部教師的姓名及其任課的課程號、班級。
(外連接與外部並外連接允許在結果表中保留非匹配元組,空缺部分填以 NULL 。外連接的作用是在做連接操作時避免丟失信息。
外連接有 3 類:
( 1 )左外連接( Left Outer Join )。連接運算謂詞為 LEFT [OUTER] JOIN ,其結果表中保留左關系的所有元組。
( 2 )右外連接( Right Outer Join )。連接運算謂詞為 RIGHT [OUTER] JOIN ,其結果表中保留右關系的所有元組。
( 3 )全外連接( Full Outer Join )。連接運算謂詞為 FULL [OUTER] JOIN ,其結果表中保留左右兩關系的所有元組。)
SELECT TNAME, CNO, CLASS
FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)

SQL 的數據操縱

例 44 把教師李映雪的記錄加入到教師表 TEACHERS 中。(插入)
INSERT INTO TEACHERS
VALUES(1476 , ' 李映雪 ' , 44 , ' 副教授 ')
例 45 成績優秀的學生將留下當教師。
INSERT INTO TEACHERS (TNO , TNAME)
SELECT DISTINCT SNO , SNAME
FROM STUDENTS , ENROLLS
WHERE STUDENTS.SNO = ENROLLS.SNO AND GRADE >= 90
例 47 把所有學生的年齡增加一歲。(修改)
UPDATE STUDENTS
SET AGE = AGE+1
例 48 學生張春明在資料庫課考試中作弊,該課成績應作零分計。
UPDATE ENROLLS
SET GRADE = 0
WHERE CNO = 'C1' AND
' 張春明 ' =
(SELECT SNAME
FROM STUDENTS
WHERE STUDENTS.SNO=ENROLLS.SNO)
例 49 從教師表中刪除年齡已到 60 歲的退休教師的數據。(刪除)
DELETE FROM TEACHERS
WHERE AGE >= 60

SQL 的數據控制

例 50 授予 LILI 有對表 STUDENTS 的查詢權。(表/視圖特權的授予
一個 SQL 特權允許一個被授權者在給定的資料庫對象上進行特定的操作。授權操作的資料庫對象包括:表 / 視圖、列、域等。授權的操作包括: INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 、 UNDER 、 USAGE 、 EXECUTE 等。其中 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 有對表做相應操作的許可權,故稱為表特權。)
GRANT SELECT ON STUDENTS
TO LILI
WITH GRANT OPTION
例 51 取消 LILI 的存取 STUDENTS 表的特權。
REVOKE ALL
ON STUDENTS
FROM LILI CASCADE

B. 高效地進行sql語句設計遵循哪些方面

1、盡量少用IN操作符,基本上所有的IN操作符都可以用EXISTS代替。不用NOT IN操作符,可以用NOT EXISTS或者外連接+替代。
2、不用「<>」或者「!=」操作符。對不等於操作符的處理會造成全表掃描,可以用「<」 or 「>」代替。
3、Where子句中出現IS NULL或者IS NOT NULL時,Oracle會停止使用索引而執行全表掃描。可以考慮在設計表時,對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。
4、當通配符「%」或者「_」作為查詢字元串的第一個字元時,索引不會被使用。
5、對於有連接的列「||」,最後一個連接列索引會無效。盡量避免連接,可以分開連接或者使用不作用在列上的函數替代。
6、如果索引不是基於函數的,那麼當在Where子句中對索引列使用函數時,索引不再起作用。Where子句中避免在索引列上使用計算,否則將導致索引失效而進行全表掃描。
7、對數據類型不同的列進行比較時,會使索引失效。
8、用「>=」替代「>」。
9、UNION操作符會對結果進行篩選,消除重復,數據量大的情況下可能會引起磁碟排序。如果不需要刪除重復記錄,應該使用UNION ALL。
10、Oracle從下到上處理Where子句中多個查詢條件,所以表連接語句應寫在其他Where條件前,可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾。
11、Oracle從右到左處理From子句中的表名,所以在From子句中包含多個表的情況下,將記錄最少的表放在最後。(只在採用RBO優化時有效)
12、Order By語句中的非索引列會降低性能,可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。
13、不同區域出現的相同的Sql語句,要保證查詢字元完全相同,以利用SGA共享池,防止相同的Sql語句被多次分析。
14、多利用內部函數提高Sql效率。例如DECODE
15、當在Sql語句中連接多個表時,使用表的別名,並將之作為每列的前綴。這樣可以減少解析時間。
需要注意的是,隨著Oracle的升級,查詢優化器會自動對Sql語句進行優化,某些限制可能在新版本的Oracle下不再是問題。尤其是採用CBO(Cost-Based Optimization,基於代價的優化方式)時。

C. 如何寫出高性能sql語句

盡量不要在where中包含子查詢;
關於時間的查詢,盡量不要寫成:where to_char(dif_date,'yyyy-mm-dd')=to_char('2007-07-01','yyyy-mm-dd');
2
在過濾條件中,可以過濾掉最大數量記錄的條件必須放在where子句的末尾;
FROM子句中寫在最後的表(基礎表,driving table)將被最先處理,在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有三個以上的連接查詢,那就需要選擇交叉表(intersection table)作為基礎表,交叉表是指那個被其他表所引用的表;
3
採用綁定變數
4
在WHERE中盡量不要使用OR
5
用EXISTS替代IN、用NOT EXISTS替代NOT IN;
6
避免在索引列上使用計算:WHERE SAL*12>25000;
7
用IN來替代OR: WHERE LOC_ID=10 OR LOC_ID=15 OR LOC_ID=20
8
避免在索引列上使用IS NULL和IS NOT NULL;
9
總是使用索引的第一個列;
10
用UNION-ALL替代UNION;
11
避免改變索引列的類型:SELECT...FROM EMP WHERE EMPNO='123',由於隱式數據類型轉換,to_char(EMPNO)='123',因此,將不採用索引,一般在採用字元串拼湊動態SQL語句出現;
12
'!=' 將不使用索引;
13
優化GROUP BY;
14
避免帶有LIKE參數的通配符,LIKE '4YE%'使用索引,但LIKE '%YE'不使用索引
15
避免使用困難的正規表達式,例如select * from customer where zipcode like "98___",即便在zipcode上建立了索引,在這種情況下也還是採用順序掃描的方式。如果把語句改成select * from customer where zipcode>"98000",在執行查詢時就會利用索引來查詢,顯然會大大提高速度;
16
盡量明確的完成SQL語句,盡量少讓資料庫工作。比如寫SELECT語句時,需要把查詢的欄位明確指出表名。盡量不要使用SELECT *語句。組織SQL語句的時候,盡量按照資料庫的習慣進行組織。

D. 如何才可能寫出高效的sql語句呢哪位達人能推薦一些相關書籍資料

舉個最簡單的例子,以查詢來說
一張表裡有5個欄位,如果你只想知道其中2個欄位的信息,那麼你的select語句中就應該只查詢這兩個欄位,而不應該寫上*
比如:select
name,age
from
table;
而不應該寫成:select
*
from
table;

E. 怎麼寫sql語句

不知道理解的對不對,有問題再問我吧
建立測試數據

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE TABLE button(button_id varchar(10))
go
CREATE TABLE [user](USER_ID varchar(10))
go
create TABLE user_button(USER_ID varchar(10),button_id varchar(10))
go

INSERT INTO dbo.button
( button_id )
SELECT
1001
UNION ALL
SELECT
1002
UNION ALL
SELECT
1003
UNION ALL
SELECT
1004

F. 玩轉SQL:如何寫出好的SQL語句

1.選擇最有效率的表名順序(只在基於規則的優化器中有效):
ORALCE的解析器按照從右到左的順序處理FROM子句中的表名,FROM子句中寫在最後的表(基礎表 driving table)將被最先處理,在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
2.WHERE子句中的連接順序:
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
3.SELECT子句中避免使用 * :
ORACLE在解析的過程中, 會將』*』 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.
4.使用DECODE函數來減少處理時間:
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
5.用Where子句替換HAVING子句:
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. on、where、having這三個都可以加條件的子句中,on是最先執行,where次之,having最後,因為on是先把不符合條件的記錄過濾後才進行統計,它就可以減少中間運算要處理的數據,按理說應該速度是最快的,where也應該比having快點的,因為它過濾數據後才進行sum,在兩個表聯接時才用on的.在多表聯接查詢時,on比where更早起作用。系統首先根據各個表之間的聯接條件,把多個表合成一個臨時表後,再由where進行過濾,然後再計算,計算完後再由having進行過濾。
6.減少對表的查詢:
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.例子:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
7.使用表的別名(Alias):
當在SQL語句中連接多個表時, 請使用表的別名並把別名前綴於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
8.用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』)
9. 識別』低效執行』的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;
10.用索引提高效率:
索引是表的一個概念部分,用來提高檢索數據的效率,ORACLE使用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
11.用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);
12.sql語句用大寫的:
因為oracle總是先解析sql語句,把小寫的字母轉換成大寫的再執行
13.避免在索引列上使用NOT
我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE」遇到」NOT,他就會停止使用索引轉而執行全表掃描.
14.避免在索引列上使用計算.
WHERE子句中,如果索引列是函數的一部分.優化器將不使用索引而使用全表掃描.
舉例:
低效:
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
15.用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄
16.用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, 那就需要返回記錄最少的索引列寫在最前面.
17.用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);
18.避免在索引列上使用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;
19.總是使用索引的第一個列:
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引. 這也是一條簡單而重要的規則,當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
20.用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』
21.ORDER BY:
ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列.
22.需要當心的WHERE子句:
某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子.
在下面的例子里, (1)『!=』 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中. (2) 『||』是字元連接函數. 就象其他函數那樣, 停用了索引. (3) 『+』是數學函數. 就象其他數學函數那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.
23.優化GROUP BY:
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = 『PRESIDENT』
OR JOB = 『MANAGER』
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 『PRESIDENT』
OR JOB = 『MANAGER』
GROUP by JOB
24.視圖中不要有ORDER BY
視圖裡面有 order by 會干擾執行計劃

G. 求一高效率sql語句,高手進,菜鳥就來學習吧

寫思路吧:億級的肯定是生產用的伺服器,個人電腦跑不動。那就這樣:看你好像是任意前三條
insert into temp(新建一個臨時表,不然全靠內存排序)
select name,row_number() over(partition by name order by name) as rank from tb_1;
insert into tb_2
select * from temp where rank<=3

H. 如何提高sql語句的執行效率

1、使用ordered提示

Oracle必須花費大量的時間來剖析多表的合並,用以確定表合並的最佳順序。SQL表達式涉及七個乃至更多的表合並,那麼有時就會需要超過30分鍾的時間來剖析,Ordered這個提示(hint)和其他的提示一起使用能夠產生合適的合並順序。

2、使用ordered_predicates

ordered_predicates提示在查詢的WHERE子句里指定的,並被用來指定布爾判斷(Booleanpredicate)被評估的順序。在沒有ordered_predicates的情況下,Oracle會使用下面這些步驟來評估SQL判斷的順序:子查詢的評估先於外層WHERE子句里的Boolean條件。

所有沒有內置函數或者子查詢的布爾條件都按照其在WHERE子句里相反的順序進行評估,即最後一條判斷最先被評估。每個判斷都帶有內置函數的布爾判斷都依據其預計的評估值按遞增排列。

3、限製表格合並評估的數量

提高SQL剖析性能的最後一種方法是強製取代Oracle的一個參數,這個參數控制著在評估一個查詢的時候,基於消耗的優化器所評估的可能合並數量。

(8)寫一個你認為最高效sql語句擴展閱讀:

1、表設計的優化,數據行的長度不要超過8020位元組,如果超過這個長度的話在物理頁中這條數據會佔用兩行從而造成存儲碎片,降低查詢效率。

2、語句的查詢優化,保證在實現功能的基礎上,盡量減少對資料庫的訪問次數;

3、建立高效的索引創建索引一般有以下兩個目的:維護被索引列的唯一性和提供快速訪問表中數據的策略。

大型資料庫有兩種索引即簇索引和非簇索引,一個沒有簇索引的表是按堆結構存儲數據,所有的數據均添加在表的尾部,而建立了簇索引的表,其數據在物理上會按照簇索引鍵的順序存儲。個表只允許有一個簇索引。

4、強制查詢轉換,有時候oracle 的優化器未必能走正確的查詢路線,這個時候就需要添加一些hint 之類的來規定他的執行路線。當然了,這個未必是最好的處理方案。因為雖然現在走這個路線是對的,以為因為數據的變化到這這個HINT 變得不可取。

I. 如何書寫高效的SQL語句

優化SQL查詢:如何寫出高性能SQL語句
1、首先要搞明白什麼叫執行計劃?
執行計劃是資料庫根據SQL語句和相關表的統計信息作出的一個查詢方案,這個方案是由查詢優化器自動分析產生欀如一條SQL語句如果用來從一個10萬條記錄的表中查1條記錄,那查詢優化器會選擇「索引查找」方式,如果該表進行了歸檔,當前只剩下5000條記錄了,那查詢優化器就會改變方案,採用 「全表掃描」方式。
可見,執行計劃並不是固定的,它是「個性化的」。產生一個正確的「執行計劃」有兩點很重要:
(1) SQL語句是否清晰地告訴查詢優化器它想干什麼?
(2) 查詢優化器得到的資料庫統計信息是否是最新的、正確的?
2、統一SQL語句的寫法
對於以下兩句SQL語句,程序員認為是相同的,資料庫查詢優化器認為是不同的。
select * from al
select * From al
其實就是大小寫不同,查詢分析器就認為是兩句不同的SQL語句,必須進行兩次解析。生成2個執行計劃。
所以作為程序員,應該保證相同的查詢語句在任何地方都一致,多一個空格都不行!
3、不要把SQL語句寫得太復雜
我經常看到,從資料庫中捕捉到的一條SQL語句列印出來有2張A4紙這么長。一般來說這么復雜的語句通常都是有問題的。我拿著這2頁長的SQL語句去請教原作者,結果他說時間太長,他一時也看不懂了。可想而知,連原作者都有可能看糊塗的SQL語句,資料庫也一樣會看糊塗。
一般,將一個Select語句的結果作為子集,然後從該子集中再進行查詢,這種一層嵌套語句還是比較常見的,但是根據經驗,超過3層嵌套,查詢優化器就很容易給出錯誤的執行計劃。因為它被繞暈了。像這種類似人工智慧的東西,終究比人的分辨力要差些,如果人都看暈了,我可以保證資料庫也會暈的。
另外,執行計劃是可以被重用的,越簡單的SQL語句被重用的可能性越高。而復雜的SQL語句只要有一個字元發生變化就必須重新解析,然後再把這一大堆垃圾塞在內存里。可想而知,資料庫的效率會何等低下。
4、使用「臨時表」暫存中間結果
簡化SQL語句的重要方法就是採用臨時表暫存中間結果,但是,臨時表的好處遠遠不止這些,將臨時結果暫存在臨時表,後面的查詢就在tempdb中了,這可以避免程序中多次掃描主表,也大大減少了程序執行中「共享鎖」阻塞「更新鎖」,減少了阻塞,提高了並發性能。

J. 如何編寫一個高效的sql語句

少用一些敏感的函數,少連接表,連接表的時候使用exists代替in,not exists代替not in。sql要大寫。在條件中能夠處理掉較多數據的放最後面。等等。