delete statistics 删除统计信息

今天一个sql语句很奇怪，统计信息以后，表竟然走全表扫描。后来没有办法我只得删除统计信息。

exec dbms_stats.delete_table_stats('missh','cancel_orderln');

删除统计信息很容易：两个参数表的“宿主”，和“表名”，两个参数都用单引号''

sql具体语句如下：

SELECT
ORDDATE,
A.ORDNO,
STOCKNO,
CUST_CODE,
CUST_NAME,
A.MEMO,
PROPERTY_CODE,
TYPE_CODE,
REGION_CODE,
DEPT_NO,
EMP_NAME,
DELIVERY_ADD,
LINKMAN,
PACKAGE,
PRO_CODE,
PRO_NAME,
PRO_SPEC,
BRAND_CODE,
WAREHOUSE,
PLACE,
QUANTITY,
OR_PRICE,
PRICE_QUOTA,
UNIT_PRICE,
SUM1,
B.MEMO MEMO_L,
SMALLTYPE_CODE,
PMY_TYPE,
AGIO_SUM,
BIGTYPE_CODE,
WARRANT,
A.ENDDATE,
B.LN_ACCOUNT
FROM missh.V_ORDERHD A,missh.V_ORDERLN B
WHERE A.CORPID = :b1
AND B.CORPID = A.CORPID
AND B.ORDNO = A.ORDNO
AND A.ORDDATE
BETWEEN NVL(TO_DATE(TO_CHAR(:b2,'yyyymmdd'),'yyyymmdd'),A.ORDDATE)
AND NVL(TO_DATE(TO_CHAR(:b3,'yyyymmdd') || ':23:59:59'

,'YYYYMMDD:Hh24:MI:SS'),A.ORDDATE)
AND NVL(REGION_CODE,'X')
BETWEEN NVL(:b4,NVL(REGION_CODE,'X'))
AND NVL(:b5,NVL(REGION_CODE,'X'))
AND NVL(CUST_CODE,'X')
BETWEEN NVL(:b6,NVL(CUST_CODE,'X'))
AND NVL(:b7,NVL(CUST_CODE,'X'))
AND NVL(DEPT_NO,'X')
BETWEEN NVL(:b8,NVL(DEPT_NO,'X'))
AND NVL(:b9,NVL(DEPT_NO,'X'))
AND NVL(EMP_NO,'X')
BETWEEN NVL(:b10,NVL(EMP_NO,'X'))
AND NVL(:b11,NVL(EMP_NO,'X'))
AND NVL(PACKAGE,'X') = NVL(:b12,NVL(PACKAGE,'X'))
AND NVL(SMALLTYPE_CODE,'X')
BETWEEN NVL(:b13,NVL(SMALLTYPE_CODE,'X'))
AND NVL(:b14,NVL(SMALLTYPE_CODE,'X'))
AND NVL(BIGTYPE_CODE,'X')
BETWEEN NVL(:b15,NVL(BIGTYPE_CODE,'X'))
AND NVL(:b16,NVL(BIGTYPE_CODE,'X'))
AND NVL(PRO_CODE,'X')
BETWEEN NVL(:b17,NVL(PRO_CODE,'X'))
AND NVL(:b18,NVL(PRO_CODE,'X'))
AND NVL(WAREHOUSE,'X')
BETWEEN NVL(:b19,NVL(WAREHOUSE,'X'))
AND NVL(:b20,NVL(WAREHOUSE,'X'))
AND NVL(BRAND_CODE,'X')
BETWEEN NVL(:b21,NVL(BRAND_CODE,'X'))
AND NVL(:b22,NVL(BRAND_CODE,'X'))
AND STATUS_IN = 'Y'

ORDER BY A.ORDNO

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1044880/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1044880/