SQL性能优化，高手请进

Question

本帖最后由 yz394777014 于 2010-05-21 13:34:17 编辑

Answer 1

只一个列条件，IS_TOSAP应该建立索引，但该字段的选择性如何？
如果选择性低，可能照样不能利用索引！

Answer 2

对只能这样 IS_TOSAP 建索引
SELECT   this_.TT_WHM_STOCK_INBOUND_ID AS TT1_254_0_,
                     this_.LAST_UPDATE_TIME AS LAST2_254_0_,
                     this_.LAST_UPDATE_USERNAME AS LAST3_254_0_,
                     this_.PID AS PID254_0_,
                     this_.PID_IS_CLOSED AS PID5_254_0_,
                     this_.CARRIER_PACKAGE_ID AS CARRIER6_254_0_,
                     this_.CARRIER_PACKAGE_NUM AS CARRIER7_254_0_,
                     this_.COMMON_SHEET_TYPE AS COMMON8_254_0_,
                     this_.CUR_PACKAGE_NUM AS CUR9_254_0_,
                     this_.DLOC_ID AS DLOC10_254_0_,
                     this_.DLOC_PACKAGE_SYSID AS DLOC11_254_0_,
                     this_.IN_BOUND_GROUP_ID AS IN12_254_0_,
                     this_.INBOUND_QTY AS INBOUND13_254_0_,
                     this_.INBOUND_TIME AS INBOUND14_254_0_,
                     this_.INBOUND_USERNAME AS INBOUND15_254_0_,
                     this_.IS_TOSAP AS IS16_254_0_,
                     this_.LOC_ID AS LOC17_254_0_,
                     this_.SHEET_ID AS SHEET18_254_0_,
                     this_.SHEET_NO AS SHEET19_254_0_,
                     this_.STOCK_PART_LOT_NO AS STOCK20_254_0_,
                     this_.TM_BAS_PART_ID AS TM21_254_0_,
                     this_.TM_BAS_PLANT_ID AS TM22_254_0_,
                     this_.TM_BAS_WORKSHOP_ID AS TM23_254_0_,
                     this_.WAREHOUSE_ID AS WAREHOUSE24_254_0_
              FROM   TT_WHM_STOCK_INBOUND this_
             WHERE   this_.IS_TOSAP = :1 
--只有一个条件  :1你这是绑定变量还是什么，不是就不要就是1,下面的也一样
          ORDER BY   this_.TT_WHM_STOCK_INBOUND_ID ASC

Answer 3

现在的情况是这样的。
我对IS_TOSAP,TT_WHM_STOCK_INBOUND_ID ASC建了索引，应该数据量太大了几百万条数据
而列IS_TOSAP的直只有两个直:0 or 1.this_.IS_TOSAP = :1  中的:1只是一个占位符。谢谢。

Answer 4

当数据量大的时候，是不是使用索引可能有数据丢失，所以Oracle优化器选择了全表扫描，这样数据是保持完整性。
原因是如下：
IS_TOSAP='0'的数据只有2000多条，
IS_TOSAP='1'的数据有几百万条。
SELECT   *
  FROM   (  SELECT   this_.TT_WHM_STOCK_INBOUND_ID AS TT1_254_0_,
                     this_.LAST_UPDATE_TIME AS LAST2_254_0_,
                     this_.LAST_UPDATE_USERNAME AS LAST3_254_0_,
                     this_.PID AS PID254_0_,
                     this_.PID_IS_CLOSED AS PID5_254_0_,
                     this_.CARRIER_PACKAGE_ID AS CARRIER6_254_0_,
                     this_.CARRIER_PACKAGE_NUM AS CARRIER7_254_0_,
                     this_.COMMON_SHEET_TYPE AS COMMON8_254_0_,
                     this_.CUR_PACKAGE_NUM AS CUR9_254_0_,
                     this_.DLOC_ID AS DLOC10_254_0_,
                     this_.DLOC_PACKAGE_SYSID AS DLOC11_254_0_,
                     this_.IN_BOUND_GROUP_ID AS IN12_254_0_,
                     this_.INBOUND_QTY AS INBOUND13_254_0_,
                     this_.INBOUND_TIME AS INBOUND14_254_0_,
                     this_.INBOUND_USERNAME AS INBOUND15_254_0_,
                     this_.IS_TOSAP AS IS16_254_0_,
                     this_.LOC_ID AS LOC17_254_0_,
                     this_.SHEET_ID AS SHEET18_254_0_,
                     this_.SHEET_NO AS SHEET19_254_0_,
                     this_.STOCK_PART_LOT_NO AS STOCK20_254_0_,
                     this_.TM_BAS_PART_ID AS TM21_254_0_,
                     this_.TM_BAS_PLANT_ID AS TM22_254_0_,
                     this_.TM_BAS_WORKSHOP_ID AS TM23_254_0_,
                     this_.WAREHOUSE_ID AS WAREHOUSE24_254_0_
              FROM   TT_WHM_STOCK_INBOUND this_
             WHERE   this_.IS_TOSAP = ‘0’
          ORDER BY   this_.TT_WHM_STOCK_INBOUND_ID ASC)
 WHERE   ROWNUM <= :2
查询分析如下：
SELECT STATEMENT, GOAL = ALL_ROWS CPU cost=29296 Cost=4 Cardinality=1 Time=1 Bytes=316 Optimizer=ALL_ROWS
 COUNT STOPKEY
  VIEW Object owner=MESOWN CPU cost=29296 Cost=4 Cardinality=1 Time=1 Bytes=316
   TABLE ACCESS BY INDEX ROWID Object owner=MESOWN Object name=TT_WHM_STOCK_INBOUND CPU cost=29296 Cost=4 Cardinality=1 Time=1 Bytes=149 Optimizer=ANALYZED
    INDEX RANGE SCAN Object owner=MESOWN Object name=IDX3_TT_WHM_STOCK_INBOUND CPU cost=21564 Cost=3 Cardinality=1 Time=1 Optimizer=ANALYZED

Answer 5

IS_TOSAP='0'的数据只有2000多条，
IS_TOSAP='1'的数据有几百万条。所以你在IS_TOSAP 上建索引当查询IS_TOSAP='1'时照样不会走索引尝试用其他的方法
TT_WHM_STOCK_INBOUND_ID 字段是否是个流水号，有什么规律没有？

Answer 6

个人认为一个变通的方法是：
如果TT_WHM_STOCK_INBOUND_ID 字段有规律可询，在TT_WHM_STOCK_INBOUND_ID 字段上建索引，sql做如下修改：
SELECT   *
  FROM   (  SELECT   this_.TT_WHM_STOCK_INBOUND_ID AS TT1_254_0_,
                     this_.LAST_UPDATE_TIME AS LAST2_254_0_,
                     this_.LAST_UPDATE_USERNAME AS LAST3_254_0_,
                     this_.PID AS PID254_0_,
                     this_.PID_IS_CLOSED AS PID5_254_0_,
                     this_.CARRIER_PACKAGE_ID AS CARRIER6_254_0_,
                     this_.CARRIER_PACKAGE_NUM AS CARRIER7_254_0_,
                     this_.COMMON_SHEET_TYPE AS COMMON8_254_0_,
                     this_.CUR_PACKAGE_NUM AS CUR9_254_0_,
                     this_.DLOC_ID AS DLOC10_254_0_,
                     this_.DLOC_PACKAGE_SYSID AS DLOC11_254_0_,
                     this_.IN_BOUND_GROUP_ID AS IN12_254_0_,
                     this_.INBOUND_QTY AS INBOUND13_254_0_,
                     this_.INBOUND_TIME AS INBOUND14_254_0_,
                     this_.INBOUND_USERNAME AS INBOUND15_254_0_,
                     this_.IS_TOSAP AS IS16_254_0_,
                     this_.LOC_ID AS LOC17_254_0_,
                     this_.SHEET_ID AS SHEET18_254_0_,
                     this_.SHEET_NO AS SHEET19_254_0_,
                     this_.STOCK_PART_LOT_NO AS STOCK20_254_0_,
                     this_.TM_BAS_PART_ID AS TM21_254_0_,
                     this_.TM_BAS_PLANT_ID AS TM22_254_0_,
                     this_.TM_BAS_WORKSHOP_ID AS TM23_254_0_,
                     this_.WAREHOUSE_ID AS WAREHOUSE24_254_0_
              FROM   TT_WHM_STOCK_INBOUND this_
             WHERE   this_.IS_TOSAP = ‘0’
 AND this_.TT_WHM_STOCK_INBOUND_ID >= (SELECT MAX(TT_WHM_STOCK_INBOUND_ID)- 2*:2 FROM TT_WHM_STOCK_INBOUND)
          ORDER BY   this_.TT_WHM_STOCK_INBOUND_ID ASC)
 WHERE   ROWNUM <= :2

Answer 7

高手，首先谢谢你的回答：
TT_WHM_STOCK_INBOUND_ID 字段是这表的主建，所以默认就有主建索引。还有请问一下，你这个语句是什么意思？谢谢！AND this_.TT_WHM_STOCK_INBOUND_ID >= (SELECT MAX(TT_WHM_STOCK_INBOUND_ID)- 2*:2 FROM TT_WHM_STOCK_INBOUND

Answer 8

我的想法如下：
如果TT_WHM_STOCK_INBOUND_ID 是个number型的字段，如1，2，、、、，1000。
要取最大的10个TT_WHM_STOCK_INBOUND_ID 对应的数据，则可以如下：
SELECT   *
  FROM   (  SELECT   *
              FROM   TT_WHM_STOCK_INBOUND this_
             WHERE   this_.IS_TOSAP = ‘0’
 AND this_.TT_WHM_STOCK_INBOUND_ID >= (SELECT MAX(TT_WHM_STOCK_INBOUND_ID)- 2*10 FROM TT_WHM_STOCK_INBOUND)
          ORDER BY   this_.TT_WHM_STOCK_INBOUND_ID deSC)
 WHERE   ROWNUM <= 10
也就是先缩小TT_WHM_STOCK_INBOUND_ID的范围再取。如果要取最小的10条记录：
SELECT   *
  FROM   (  SELECT   *
              FROM   TT_WHM_STOCK_INBOUND this_
             WHERE   this_.IS_TOSAP = ‘0’
 AND this_.TT_WHM_STOCK_INBOUND_ID >= (SELECT MIN(TT_WHM_STOCK_INBOUND_ID)+ 2*10 FROM TT_WHM_STOCK_INBOUND)
          ORDER BY   this_.TT_WHM_STOCK_INBOUND_ID ASC)
 WHERE   ROWNUM <= 10
至于用2*10，是为了选定一个比你要取的数据大一些的范围，防止取数缺失。至于是2*10，还是20*10，自己可以和实际情况结合确定。

Answer 9

这只是个变通的方法，自己可以再想想。主要就是将order by 的范围减小，同时order by的范围能包括你所需要的数据即可。