Oracle-请问Oracle SQL排序查询慢如何解决

WordPress 开发 WordPress 开发 主题:1098 回复:2322

Oracle-请问Oracle SQL排序查询慢如何解决

虐人心 发布于 2017-02-09 字数 175 浏览 1035 回复 2

26万条数据,使用:

SELECT * FROM(SELECT T.*,ROWNUM RN FROM(
SELECT * FROM INFO
) T WHERE ROWNUM<2001) WHERE RN>0

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

支持 Markdown 语法,需要帮助?

评论(2

瑾兮 2017-07-20 2 楼

这个原因很简单:

 SELECT FROM(SELECT T.,ROWNUM RN FROM(
SELECT * FROM INFO ORDER BY PDATE DESC
) T WHERE ROWNUM<2001) WHERE RN>0

算一下,如果使用定义在PDATE上的索引,那么拿到这2000个rowid后,还需要做2000次random io去读取这2000条记录。 -- 实在不如直接读出所有记录来做排序,成本还小的多。

改为:

 SELECT B.id, B.created, T.RN FROM
big_table B
join
(SELECT created,ROWNUM RN FROM (SELECT created FROM big_table ORDER BY created DESC) WHERE ROWNUM<2000) T
on B.created=T.created
WHERE RN>0;

则会使用定义在PDATE上的索引来取最大的2000个PDATE,无需 排序。

下面的实验是 用了我自己建的表,10w条数据。没看出效果。。。,不过第一个select需要排序,第二个不需要。

 SQL> SELECT FROM
2 (SELECT T.
,ROWNUM RN FROM (SELECT id,created FROM big_table ORDER BY created DESC) T WHERE ROWNUM<2000)
3 WHERE RN>0 and rownum>1;

Plan hash value: 2610743403

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1999 | 69965 | | 881 (2)| 00:00:11 |

| 1 | COUNT | | | | | | |

|* 2 | FILTER | | | | | | |

|* 3 | VIEW | | 1999 | 69965 | | 881 (2)| 00:00:11 |

|* 4 | COUNT STOPKEY | | | | | | |

| 5 | VIEW | | 98883 | 2124K| | 881 (2)| 00:00:11 |

|* 6 | SORT ORDER BY STOPKEY| | 98883 | 1255K| 2336K| 881 (2)| 00:00:11 |

| 7 | TABLE ACCESS FULL | BIG_TABLE | 98883 | 1255K| | 406 (1)| 00:00:05 |

Predicate Information (identified by operation id):

2 - filter(ROWNUM>1)
3 - filter("RN">0)
4 - filter(ROWNUM<2000)
6 - filter(ROWNUM<2000)

      1  recursive calls
      0  db block gets
   1488  consistent gets
      0  physical reads
      0  redo size
    471  bytes sent via SQL*Net to client
    512  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

 SQL> SELECT B.*, T.RN FROM
2 big_table B
3 join
4 (SELECT created,ROWNUM RN FROM (SELECT created FROM big_table ORDER BY created DESC) WHERE ROWNUM<2000) T
5 on B.created=T.created
6 WHERE RN>0 and rownum>1;

Plan hash value: 1806601054

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 364K| 42M| 678 (2)| 00:00:09 |

| 1 | COUNT | | | | | |

|* 2 | FILTER | | | | | |

|* 3 | HASH JOIN | | 364K| 42M| 678 (2)| 00:00:09 |

|* 4 | VIEW | | 1999 | 41979 | 268 (1)| 00:00:04 |

|* 5 | COUNT STOPKEY | | | | | |

| 6 | VIEW | | 98883 | 772K| 268 (1)| 00:00:04 |

| 7 | INDEX FULL SCAN DESCENDING| INX | 98883 | 772K| 268 (1)| 00:00:04 |

| 8 | TABLE ACCESS FULL | BIG_TABLE | 98883 | 9849K| 407 (1)| 00:00:05 |

Predicate Information (identified by operation id):

2 - filter(ROWNUM>1)
3 - access("B"."CREATED"="T"."CREATED")
4 - filter("T"."RN">0)
5 - filter(ROWNUM<2000)

      0  recursive calls
      0  db block gets
   1495  consistent gets
      0  physical reads
      0  redo size
   1479  bytes sent via SQL*Net to client
    512  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed
清晨说ぺ晚安 2017-06-23 1 楼

我怀疑是你的temp空间不足引起的,你看看有没有用到磁盘排序。
先把temp空间清理下,清理的方法如下:
1、 使用如下语句a查看一下认谁在用临时段
SELECT username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr

2、 那些正在使用临时段的进程
SQL>;Alter system kill session 'sid,serial#';
3、把TEMP表空间回收一下
SQL>;Alter tablespace TEMP coalesce;
然后再试试,如果还不行,慎用下set autotrace only
再不行那也就这个速度了,基本上order by引起的就是sort_area_size问题或temp空间不足