高分求排序效率解决方法

Question

有ABC3个表进行关联查询，其中A表60万数据，B，C都是6万左右
sql文：SELECT A.ID1,B.NAME,C.NAME
FROM A
     LEFT JOIN B
          ON A.ID2=B.ID2
     LEFT JOIN C
          ON A.ID3=C.ID3
WHERE A.IsValid=1
ORDER BY A.ID4
发现时间主要用在了Order by 上，曾经试图在A.ID4,A.ID2,A.ID3建立联合索引，又试只在A.ID4上建立索引，但是几乎没有提升，整个查询还在15秒左右。
抽出的数据集在50万以上，外面其实有一层SELECT,取前1000条的记录。ps：不要让我在Where里面写取前1000条，我知道这样快，但结果是错的
上面的是一个基础的sql文，其实Where条件中同时还可以对B,C表的NAME字段进行限定，如
SELECT A.ID1,B.NAME,C.NAME
FROM A
     LEFT JOIN B
          ON A.ID2=B.ID2
     LEFT JOIN C
          ON A.ID3=C.ID3
WHERE A.IsValid=1 AND ((B.NAME='AAA' OR B.NAME='BBB') AND C.NAME='CCC')
ORDER BY A.ID4因为可能有OR的关系，所以不能在ON中写限定B.NAME,C.NAME的条件
关键还是以A.ID4的排序太慢要如何建立索引才能提高效率呢？

Answer 1

你可能产生一些概念上的混淆, order by排序慢和建立索引提高效率是两回事. order by排序的时候, 系统会使用排序区, 临时表空间的使用等有关系, 需要排序时, 会先使用系统的排序区, 如果数据量大到超过Sort_area_size设置的值的时候排序要磁盘进行数据交互, 这个应该是主要原因. 数据量大的时候排序号是要多很多时间, 试着把参数Sort_area_size调整大一些,以增加排序性能. 至于说建立索引提高效率, 是在SQL执行时起作用, 这个可以通过执行计划看出来的. 

Answer 2

order by 是会影响一些效率的，楼上说的好像有道理，但我没试过！

Answer 3

A(id2, id3, id4), 可以建个复合索引B(id2)
C(id3)
也要建索引－－－－－>其中A表60万数据，B，C都是6万左右 你这个sql查询出来会要得到多少条记录？
能把执行计划帖出来不？

Answer 4

To dinya2003:
索引不能提高order by的性能么，我做了一个小实验，比如在一列上加索引，如果只是Select 这个被索引的字段，然后order by，速度还是很快的；如果Select中加了其他未被索引的字段，性能下降很多；如果未加索引，速度最慢。不知道为什么，只是试验了一下，这方面我还真不太了解。To codearts:
大概最终会有45W的数据量，然后外层有一个Select，取前1000条
稍后贴执行计划

Answer 5

已选择349092行。
执行计划
------------------------------------------------------------------------------------------------------------------------------------------
----------| Id  | Operation               | Name              | Rows  | Bytes |TempSpc| Co
st (%CPU)|--------------------------------------------------------------------------------
----------|   0 | SELECT STATEMENT        |                   |   349K|    61M|       | 30
393   (1)||   1 |  SORT ORDER BY          |                   |   349K|    61M|   129M| 30
393   (1)||   2 |   HASH JOIN             |                   |   349K|    61M|       |  6
425   (1)||   3 |    TABLE ACCESS FULL    | TB_MEDIATYPESMALL |  6535 |   165K|       |
 14   (0)||   4 |    HASH JOIN            |                   |   349K|    52M|  1416K|  6
407   (1)||   5 |     INDEX FAST FULL SCAN| MYINDEX3          | 36150 |   988K|       |
 45   (0)||   6 |     TABLE ACCESS FULL   | MEDIADATAGENERAL  |   349K|    43M|       |  2
792   (1)|--------------------------------------------------------------------------------
----------
Note
-----
   - 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
         74  recursive calls
         55  db block gets
      12920  consistent gets
      26871  physical reads
          0  redo size
   30092767  bytes sent via SQL*Net to client
     256380  bytes received via SQL*Net from client
      23274  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     349092  rows processed

Answer 6

又测试了一下
codearts 建立索引的索引并没有被用到
因为Where条件的关系，如果去掉Where中的内容，索引才被用到

Answer 7

赞同dinya2003 的观点。
首先要确认慢是发生在select还是发生在order by。
如果是select，可以进行增加索引等操作；如果发生在order by，那要通过增大Sort_area_size来提高排序性能，或者查看一下temp空间。

Answer 8

谢谢dinya2003和jdsnhan如何调整Sort_area_size呢？我的Sort_area_size目前是65536
使用
alter session set sort_area_size
将其改成一个非常大的数值，然后提交，再查看数值已经变化
但排序速度仍然没有任何提高，虽然有4G内存，查询时硬盘灯确实在一直亮的
重启之后，又恢复成65536
怎样才能更改Sort_area_size值使其起作用呢？

Answer 9

除了调整Sort_area_size，
顺便再继续问高手一个索引的问题
还是上面的连接，其中90%以上是排序时间。
但如果我进行优化查询时间，如
SELECT   A.ID1,B.NAME,C.NAME 
FROM   A 
          LEFT   JOIN   B 
                    ON   A.ID2=B.ID2 
          LEFT   JOIN   C 
                    ON   A.ID3=C.ID3 
WHERE   A.IsValid=1   AND   ((B.NAME='AAA'   OR   B.NAME='BBB')   AND   C.NAME='CCC') 我试图在A表上建立索引，可是一碰到WHERE   A.IsValid=1 oralce就无视索引走full table scan
这种连接，怎样建立索引才能使查询速度最优化？

Answer 10

楼主的这个查询，表中有60W的数据，查询结果将近40W，这么大比例的查询结果，明显CBO会用全表扫描的(有索引也不会用)。
这个查询计划：
 1     sorts   (disk) 
这说明有通过硬盘排序，调整Sort_area_size可能会有一定的性能提高。
本质上，查询出这么多数据，我认为这个设计是有问题的。楼主的sql完整版是不是这样：
select * from (select ***** where a.isvalid = 1 .... order by ****) where rownum <= 1000?
这个语句的执行计划跟
select ***** where a.isvalid = 1 .... order by ****
是完全不同的，oracle会对前面的那个sql进行优化。
不知道楼主的几个字段是不是会经常更新，如果A表有很多的字段选出的那几个字段又不常更新，可以这样建索引试试：
A(id2, id3, IsValid，id4，ID1),
这样那个sql执行时只扫描这个索引时就可以了

Answer 11

其实你是从60万记录中提取34万记录，比例太高，Oracle通常会采用全表扫描的方式，这样成本反而小些。
如果全表扫描不可避免，那你就尽量减少全表扫描的成本。你可以尝试下把字段IsValid加入到复合索引中。也就是说你将语句中的所有涉及到A表的所有字段（包括SELECT和WHERE子句）建立一个复合索引，这样执行时就不查源表，只读取这个复合索引就可以了。而索引比源表要小不少，这样减少了I/O，性能会有所提高。

Answer 12

思路有问题，在这么大的数据集合上做ORDER by简直就是找死，不建议那么做。 

Answer 13

完整版确实如codearts 所说，时间确实要少一点，
事实上，select   *   from   (select   *****   where   a.isvalid   =   1   ....   order   by   ****)   where   rownum   <=   1000
也只是一部分，我只是把可能作为排序和条件的表放入里面最小的查询中，并加入数量限制，
这个1000条结果还要与其他表相连，不过在1000条的基础上操作，就快很多了。表不会频繁更新，一会儿试试看你的索引。不过现在查询并不是很费时间。另外如何修改Sort_area_size并使其生效呢？我修改SPFILEORACLE10.ORA文件也不行