建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
查詢(xún)排序最多的SQL語(yǔ)句：
目前創(chuàng)新互聯(lián)已為超過(guò)千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)站空間、網(wǎng)站托管、服務(wù)器托管、企業(yè)網(wǎng)站設(shè)計(jì)、龍華網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶(hù)導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶(hù)和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。
WITH sql_workarea AS
(SELECT sql_id || '_' || child_number sql_id_child,
operation_type operation,
last_execution last_exec,
round(active_time / 1000000, 2) seconds,
optimal_executions || '/' || multipasses_executions olm,
'' || substr(sql_text, 1, 155) sql_text,
rank() over(ORDER BY active_time DESC) ranking
FROM v$sql_workarea
JOIN v$sql
USING (sql_id, child_number))
SELECT sql_id_child "SQL ID-CHILD",
seconds,
operation,
last_exec,
olm "O/1/M",
sql_text
FROM sql_workarea
WHERE ranking <= 10
ORDER BY ranking;10033跟蹤排序：
alter session set tracefile_identifier=e10033;
alter session set events '10033 trace name context forever,level 1';
---- Sort Statistics ------------------------------
Initial runs 14
Number of merges 1
Input records 55500
Output records 55500
Disk blocks 1st pass 1467
Total disk blocks used 1451
Total number of comparisons performed 699074
Comparisons performed by in-memory sort 485849
Comparisons performed during merge 213212
Comparisons while searching for key in-memory 13
Number of seeks in final run 55500
Temp segments allocated 1
Extents allocated 12
Uses version 2 sort
Uses asynchronous IO
---- Run Directory Statistics ----
Run directory block reads (buffer cache) 15
Block pins (for run directory) 1
Block repins (for run directory) 14
Maximum input run size (in blocks) 109
Minimum input run size (in blocks) 32
Average input run size (in blocks) 104
---- Direct Write Statistics -----
Write slot size 49152
Write slots used during in-memory sort 2
Number of direct writes 247
Num blocks written (with direct write) 1449
Block pins (for sort records) 1449
Waits for async writes 199
---- Direct Read Statistics ------
Size of read slots for output 32768
Number of read slots for output 32
Number of direct sync reads 30
Number of blocks read synchronously 95
Number of direct async reads 343
Number of blocks read asynchronously 1354
使用索引來(lái)規(guī)避排序
如果在order by字句中的部分或者全部列上存在索引，oracle有可能使用索引來(lái)按照要求的順序獲取記錄，因此也避免了排序操作。
假如索引是出現(xiàn)在與orde by字句里的列相同的列上，oracle可以直接從索引中按照索引排序的順序讀取記錄，然而，按鍵的順序讀取記錄需要一塊接一塊地全掃描索引葉子塊。雖然快速全掃描比全索引掃描高校得多，但是快速全掃描無(wú)法按索引順序返回記錄，因此也不能用來(lái)避免排序操作。
SQL> select * from customers order by cust_last_name,cust_first_name,cust_year_of_birth;
55500 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2792773903
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 9810K| | 2609 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 55500 | 9810K| 12M| 2609 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 9810K| | 405 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
12 recursive calls
15 db block gets
1456 consistent gets
2903 physical reads
0 redo size
6366362 bytes sent via SQL*Net to client
41213 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
55500 rows processed
建索引后：
SQL> create index cust_namedob_i on customers(cust_last_name,cust_first_name,cust_year_of_birth);
Index created.
SQL> select /*+ index(customers,cust_namedob_i) */ * from customers order by cust_last_name,cust_first_name,cust_year_of_birth;
55500 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1819843466
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 9810K| 20550 (1)| 00:00:15 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 55500 | 9810K| 20550 (1)| 00:00:15 |
| 2 | INDEX FULL SCAN | CUST_NAMEDOB_I | 55500 | | 225 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
26557 consistent gets
1708 physical reads
0 redo size
6366312 bytes sent via SQL*Net to client
41213 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55500 rows processed
雖然使用索引可能就不再需要排序了，但是同時(shí)讀取索引和表塊，以及按塊順次讀取這種并不高效的掃描方式所帶來(lái)的開(kāi)銷(xiāo)，比使用全表掃描讀取表塊的方式要欠佳很多，通常，這意味為了避免排序而使用索引，實(shí)際上會(huì)導(dǎo)致更差的性能。然而使用索引在檢索第一行記錄時(shí)速度更快，因?yàn)橐坏┬枰挠涗洷粰z索到，它會(huì)立即返回。相比之下排序的方法要求在任一記錄返回之前，全部記錄都必須被檢索出來(lái)并完成排序。因此，在優(yōu)化器目標(biāo)為FIRST_ROWS_N時(shí)，優(yōu)化器傾向于使用索引，而在目標(biāo)是ALL_ROWS時(shí)，則會(huì)使用全表掃描。
另一個(gè)基于索引的獲取比先掃描再獲取要更優(yōu)異的場(chǎng)景是當(dāng)內(nèi)存極其有限時(shí)。如果可用于排序的內(nèi)存是受限的，讀寫(xiě)臨時(shí)段所需要IO將超過(guò)索引和和表掃描所包含的額外的IO開(kāi)銷(xiāo)。當(dāng)然如果能夠分配更多的內(nèi)存，它的表現(xiàn)會(huì)好很多的，但是如果這是不可能的，你或許應(yīng)該使用INDEX提示來(lái)避免排序。
聚合操作
聚合炒作（如SUM和AVG）必須處理輸入的數(shù)據(jù)每一行記錄，因此，它們通常和全表掃描聯(lián)系在一起。
SQL> select sum(quantity_sold) from sales;
Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 525 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION RANGE ALL| | 918K| 2691K| 525 (2)| 00:00:01 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 2691K| 525 (2)| 00:00:01 | 1 | 28 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
2429 recursive calls
2 db block gets
5371 consistent gets
1714 physical reads
0 redo size
538 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
183 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ index(sales,index_sl) */ sum(quantity_sold) from sales;
SUM(QUANTITY_SOLD)
------------------
918843
Execution Plan
----------------------------------------------------------
Plan hash value: 3788238680
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2316 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN| INDEX_SL | 918K| 2691K| 2316 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2311 consistent gets
2314 physical reads
0 redo size
538 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
最大值和最小值，
與大多數(shù)其他的聚合操作不同，如果在相關(guān)列存在索引，MAX和MIN操作并不需要讀取每一行記錄。如果存在B樹(shù)索引，我們可以通過(guò)檢查第一個(gè)或最后一個(gè)索引項(xiàng)來(lái)確定最大值或最小值，這僅需要3-5個(gè)邏輯讀的開(kāi)銷(xiāo)：
SQL> select max(amount_sold) from sales;
MAX(AMOUNT_SOLD)
----------------
1782.72
Execution Plan
----------------------------------------------------------
Plan hash value: 781264156
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| AMOUNT_SOLD_IDX | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
8 physical reads
0 redo size
536 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
同時(shí)找出最大值和最小值，
SQL> select max(amount_sold),min(amount_sold) from sales;
MAX(AMOUNT_SOLD) MIN(AMOUNT_SOLD)
---------------- ----------------
1782.72 6.4
Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 525 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | |
| 2 | PARTITION RANGE ALL| | 918K| 4486K| 525 (2)| 00:00:01 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 4486K| 525 (2)| 00:00:01 | 1 | 28 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1635 consistent gets
1619 physical reads
0 redo size
618 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
實(shí)際上分別提交MAX和MIN查詢(xún)?nèi)缓髮⒔Y(jié)果合并到一起是一種更好的方法：
SELECT max_sold, min_sold
FROM (SELECT MAX(amount_sold) max_sold FROM sales) maxt,
2 3 (SELECT MIN(amount_sold) min_sold FROM sales) mint;
MAX_SOLD MIN_SOLD
---------- ----------
1782.72 6.4
Execution Plan
----------------------------------------------------------
Plan hash value: 3650580342
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 6 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| AMOUNT_SOLD_IDX | 1 | 5 | 3 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| AMOUNT_SOLD_IDX | 1 | 5 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
5 physical reads
0 redo size
602 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
前N 查詢(xún)
如何獲取一個(gè)表的前10行記錄，
錯(cuò)誤寫(xiě)法：
SQL> SELECT * FROM sales WHERE rownum <= 10 ORDER BY amount_sold DESC;
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- ---------- ---------- ------------- -----------
13 987 1998-01-10 00:00:00 3 999 1 1232.16
13 1660 1998-01-10 00:00:00 3 999 1 1232.16
13 1762 1998-01-10 00:00:00 3 999 1 1232.16
13 1843 1998-01-10 00:00:00 3 999 1 1232.16
13 4663 1998-01-10 00:00:00 3 999 1 1232.16
13 2273 1998-01-10 00:00:00 3 999 1 1232.16
13 2380 1998-01-10 00:00:00 3 999 1 1232.16
13 2683 1998-01-10 00:00:00 3 999 1 1232.16
13 2865 1998-01-10 00:00:00 3 999 1 1232.16
13 1948 1998-01-10 00:00:00 3 999 1 1232.16
10 rows selected.
這是因?yàn)閷?duì)where的處理會(huì)先于order by。因此這個(gè)查詢(xún)將獲取它最先發(fā)現(xiàn)的10條記錄，然后對(duì)它們進(jìn)行排序。這樣的結(jié)果不是真正的前10.
下面的查詢(xún)更好：
SELECT /* top10_subquery */
*
FROM (SELECT cust_id, prod_id, time_id, amount_sold
FROM sales
ORDER BY amount_sold DESC)
WHERE rownum <= 10;
4 5 6
CUST_ID PROD_ID TIME_ID AMOUNT_SOLD
---------- ---------- ------------------- -----------
3948 18 1999-04-26 00:00:00 1782.72
4150 18 1999-06-26 00:00:00 1782.72
40 18 1999-06-26 00:00:00 1782.72
33724 18 1999-06-21 00:00:00 1782.72
32863 18 1999-06-21 00:00:00 1782.72
31364 18 1999-06-21 00:00:00 1782.72
10864 18 1999-06-21 00:00:00 1782.72
10620 18 1999-06-21 00:00:00 1782.72
6490 18 1999-06-21 00:00:00 1782.72
4788 18 1999-06-21 00:00:00 1782.72
10 rows selected.
網(wǎng)站標(biāo)題：oracle排序操作
文章URL：http://www.dlmjj.cn/article/gcoess.html
