广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >oracle排序操作
  • 853

0
分享到

oracle排序操作

2024-04-02 19:04:59 853人浏览 薄情痞子
摘要

查询排序最多的sql语句：WITH sql_workarea AS (SELECT sql_id || '_' || child_number sql_id_child, &nb

  1. 查询排序最多的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 ID-CHILD",
           seconds,
           operation,
           last_exec,
           olm          "O/1/M",
           sql_text
      FROM sql_workarea
     WHERE ranking <= 10
     ORDER BY ranking;

  2. 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

使用索引来规避排序

如果在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

----------------------------------------------------------------------------------------
| 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 * 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

虽然使用索引可能就不再需要排序了，但是同时读取索引和表块，以及按块顺次读取这种并不高效的扫描方式所带来的开销，比使用全表扫描读取表块的方式要欠佳很多，通常，这意味为了避免排序而使用索引，实际上会导致更差的性能。然而使用索引在检索第一行记录时速度更快，因为一旦需要的记录被检索到，它会立即返回。相比之下排序的方法要求在任一记录返回之前，全部记录都必须被检索出来并完成排序。因此，在优化器目标为FIRST_ROWS_N时，优化器倾向于使用索引，而在目标是ALL_ROWS时，则会使用全表扫描。

  另一个基于索引的获取比先扫描再获取要更优异的场景是当内存极其有限时。如果可用于排序的内存是受限的，读写临时段所需要IO将超过索引和和表扫描所包含的额外的IO开销。当然如果能够分配更多的内存，它的表现会好很多的，但是如果这是不可能的，你或许应该使用INDEX提示来避免排序。

聚合操作

聚合炒作（如SUM和AVG）必须处理输入的数据每一行记录，因此，它们通常和全表扫描联系在一起。

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 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

最大值和最小值，

与大多数其他的聚合操作不同，如果在相关列存在索引，MAX和MIN操作并不需要读取每一行记录。如果存在B树索引，我们可以通过检查第一个或最后一个索引项来确定最大值或最小值，这仅需要3-5个逻辑读的开销：

 

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

同时找出最大值和最小值，

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

实际上分别提交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

------------------------------------------------------------------------------------------------
| 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 查询

如何获取一个表的前10行记录，

错误写法：

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.

这是因为对where的处理会先于order by。因此这个查询将获取它最先发现的10条记录，然后对它们进行排序。这样的结果不是真正的前10.

下面的查询更好：

SELECT
 *
  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.

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: oracle排序操作

本文链接: https://www.lsjlt.com/news/44402.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • oracle排序操作
    查询排序最多的SQL语句：WITH sql_workarea AS (SELECT sql_id || '_' || child_number sql_id_child, &nb...
    99+
    2022-10-18
  • Oracle的where语句和排序操作
    where特点：1、用于对数据的筛选2、可以比较，逻辑操作3、where 需要放到from后面==============================================...
    99+
    2022-10-18
  • LINQ排序操作符用法
    Linq中的排序操作符包括OrderBy、OrderByDescending、ThenBy、ThenByDescending和Reverse，提供了升序或者降序排序。 一、Orde...
    99+
    2022-11-13
  • mysql操作入门（四）-----数据排序（升序、降序、多字段排序）
    已知emp表的内容为 1.升序排序 语法：select 字段名1，字段名2，字段名3 from 表名 （where 条件）order by （字段）； 举例：将工资进行升序排序 也可以升序排列字符串，顺序是按照第一...
    99+
    2023-09-17
    mysql 数据库 sql
  • 排序，分组和集合操作
    排序操作排序是计算机承担的最基本操作之一，尤其是在数据库处理领域，oracle也不例外。可能需要oracle排序数据的操作包括以下几种；（1）创建一个索引（2）通过group by，unique或disti...
    99+
    2022-10-18
  • 【Java】数组操作 之 数组排序
    对数组进行排序是程序中非常基本的需求。常用的排序算法有冒泡排序、插入排序和快速排序等。 一、冒泡排序 我们来看一下如何使用冒泡排序算法对一个整型数组从小到大进行排序： // 冒泡排序import ja...
    99+
    2023-10-04
    java 排序算法 算法
  • mysql如何in查询操作排序
    本文将为大家详细介绍“mysql如何in查询操作排序”，内容步骤清晰详细，细节处理妥当，而小编每天都会更新不同的知识点，希望这篇“mysql如何in查询操作排序”能够给你意想不...
    99+
    2022-10-19
  • R语言:排序的应用操作
    工作中遇到过许多看起来挺复杂的数据筛选，本质上都可以用排序解决，这里以R自带的mtcar数据集为例做一个记录。 首先简单介绍一下mtcar数据集，mtcar（Motor Trend ...
    99+
    2022-11-12
  • LINQ排序操作符怎么使用
    这篇文章主要介绍了LINQ排序操作符怎么使用的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇LINQ排序操作符怎么使用文章都会有所收获，下面我们一起来看看吧。Linq中的排序操作符包括OrderBy、OrderB...
    99+
    2023-06-29
  • PHP 排序函数使用方法，按照字母排序等操作
    详解PHP排序方法使用 一、sort() 函数 用于对数组单元从低到高进行排序。 //数组$data = array('D','F','A','C','B');//排序sort($data);//输出排版标签echo "";//打印数据p...
    99+
    2023-10-20
    php 开发语言
  • Oracle中文排序 NLSSORT
    今天发现了个挺有意思的函数:NLSSORT，发现能给中文按拼音、笔画、部首排序，遂查了下相关资料，结果如下:Oracle 9i开始，新增了按照拼音、部首、笔画排序功能。   &...
    99+
    2022-10-18
  • ORACLE Postgresql中文排序
    当我们order排序不能够实现我们想要的内容时候，尝试一下NLSSORT这个函数吧他不仅仅按照姓氏排序，名也会排序： nls_param用于指定语言特征，格式为nls_sort   &n...
    99+
    2022-10-18
  • Python数据分析Pandas Dataframe排序操作
    目录1.索引的排序2.值的排序前言： 数据的排序是比较常用的操作，DataFrame 的排序分为两种，一种是对索引进行排序，另一种是对值进行排序，接下来就分别介绍一下。 1.索引的排...
    99+
    2022-11-13
  • excel排序依次类推怎么操作
    今天小编给大家分享一下excel排序依次类推怎么操作的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。excel排序1234依次...
    99+
    2023-07-02
  • Elasticsearch聚合查询及排序操作示例
    目录1 es排序2 match和match的区别3 分页查询4 es 组合查询5 结果过滤展示字端6 结果高亮展示7 聚合查询avg、max、min、sum、分组8 mapping和...
    99+
    2022-11-13
  • MySQL排序检索数据操作方法梳理
    目录前言1. 排序数据2. 按多个列排序3. 按列位置排序4. 指定排序方式（升序或者降序）5. 扩展—文本性数据如何排序前言 本实验中所用数据库创建SQL语句以及插入数据到数据库中的SQL语句链接： 链接:...
    99+
    2022-10-21
  • 使用numpy实现topk函数操作(并排序)
    np.argpartition 难以解决topK topK是常用的一个功能，在python中，numpy等计算库使用了丰富的底层优化，对于矩阵计算的效率远高于python的for-l...
    99+
    2022-11-12
  • oracle排序要注意的点
    排序算法分稳定排序和非稳定排序。通常非稳定排序的时间复杂度更低。而在oracle中查询也要注意排序的结果集可能不是目标的结果集。1， 未指定排序规则select * from&...
    99+
    2022-10-18
  • Python数据分析Pandas Dataframe排序操作的方法
    本文小编为大家详细介绍“Python数据分析Pandas Dataframe排序操作的方法”，内容详细，步骤清晰，细节处理妥当，希望这篇“Python数据分析Pandas Dataframe排序操作的方法”文章能帮助大家...
    99+
    2023-06-30
  • Python入门基本操作列表排序用法详解
    目录列表的举例1.访问python列表中的元素2.python列表的切片3.python列表的排序4.Python列表元素的添加5.Python列表元素的删除列表是最常用的Pytho...
    99+
    2022-11-12
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作