oracle排序操作
[*] 查询排序最多的SQL语句：
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> seconds,
operation,
last_exec,
olm "O/1/M",
sql_text
FROM sql_workarea
WHERE ranking
Minimum input run>
Average input run> ---- Direct Write Statistics -----
Write slot> 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
使用索引来规避排序
如果在order by字句中的部分或者全部列上存在索引，oracle有可能使用索引来按照要求的顺序获取记录，因此也避免了排序操作。
假如索引是出现在与orde by字句里的列相同的列上，oracle可以直接从索引中按照索引排序的顺序读取记录，然而，按键的顺序读取记录需要一块接一块地全扫描索引叶子块。虽然快速全扫描比全索引扫描高校得多，但是快速全扫描无法按索引顺序返回记录，因此也不能用来避免排序操作。
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
----------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------
| 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
----------------------------------------------------------
12recursive calls
15db block gets
1456consistent gets
2903physical reads
0redo> 6366362bytes sent via SQL*Net to client
41213bytes received via SQL*Net from client
3701SQL*Net roundtrips to/from client
0sorts (memory)
1sorts (disk)
55500rows 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
----------------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------
1recursive calls
0db block gets
26557consistent gets
1708physical reads
0redo> 6366312bytes sent via SQL*Net to client
41213bytes received via SQL*Net from client
3701SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
55500rows processed
虽然使用索引可能就不再需要排序了，但是同时读取索引和表块，以及按块顺次读取这种并不高效的扫描方式所带来的开销，比使用全表扫描读取表块的方式要欠佳很多，通常，这意味为了避免排序而使用索引，实际上会导致更差的性能。然而使用索引在检索第一行记录时速度更快，因为一旦需要的记录被检索到，它会立即返回。相比之下排序的方法要求在任一记录返回之前，全部记录都必须被检索出来并完成排序。因此，在优化器目标为FIRST_ROWS_N时，优化器倾向于使用索引，而在目标是ALL_ROWS时，则会使用全表扫描。
另一个基于索引的获取比先扫描再获取要更优异的场景是当内存极其有限时。如果可用于排序的内存是受限的，读写临时段所需要IO将超过索引和和表扫描所包含的额外的IO开销。当然如果能够分配更多的内存，它的表现会好很多的，但是如果这是不可能的，你或许应该使用INDEX提示来避免排序。
聚合操作
聚合炒作（如SUM和AVG）必须处理输入的数据每一行记录，因此，它们通常和全表扫描联系在一起。
SQL> select sum(quantity_sold) from sales;
Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612
----------------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------
2429recursive calls
2db block gets
5371consistent gets
1714physical reads
0redo> 538bytes sent via SQL*Net to client
524bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
183sorts (memory)
0sorts (disk)
1rows processed
SQL> select /*+ index(sales,index_sl) */ sum(quantity_sold) from sales;
SUM(QUANTITY_SOLD)
------------------
918843
Execution Plan
----------------------------------------------------------
Plan hash value: 3788238680
-----------------------------------------------------------------------------
|> -----------------------------------------------------------------------------
| 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
----------------------------------------------------------
1recursive calls
0db block gets
2311consistent gets
2314physical reads
0redo> 538bytes sent via SQL*Net to client
524bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
最大值和最小值，
与大多数其他的聚合操作不同，如果在相关列存在索引，MAX和MIN操作并不需要读取每一行记录。如果存在B树索引，我们可以通过检查第一个或最后一个索引项来确定最大值或最小值，这仅需要3-5个逻辑读的开销：
SQL> select max(amount_sold) from sales;
MAX(AMOUNT_SOLD)
----------------
1782.72
Execution Plan
----------------------------------------------------------
Plan hash value: 781264156
----------------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------
1recursive calls
0db block gets
3consistent gets
8physical reads
0redo> 536bytes sent via SQL*Net to client
524bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
同时找出最大值和最小值，
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
----------------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------
1recursive calls
0db block gets
1635consistent gets
1619physical reads
0redo> 618bytes sent via SQL*Net to client
524bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
实际上分别提交MAX和MIN查询然后将结果合并到一起是一种更好的方法：
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
------------------------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------
1recursive calls
0db block gets
6consistent gets
5physical reads
0redo> 602bytes sent via SQL*Net to client
524bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
前N 查询
如何获取一个表的前10行记录，
错误写法：
SQL> SELECT * FROM sales WHERE rownum
页:
[1]