频繁的数据类型转换导致查询很慢

2015年10月29日 9078点热度 0人点赞 0条评论

以下展示了如何发现数据类型转换问题。

表RES_DEV_TERMINAL数据量2100万条，列ROOM_ID有索引，但该列数据类型是VARCHAR2(512)！修改为'331856359'后，速度大幅度提升。如下：

SELECT COUNT(DISTINCT A.SEQ_UUID)

FROM RES_PORT_SEQ A,

(SELECT A.SEQ_UUID, A.ISTOP, A.STATUS, A.PORT_SEQ_NO, A.STATEFLAG

FROM RES_PORT_SEQ A

WHERE EXISTS (SELECT 0

FROM RES_DEV_TERMINAL B

WHERE B.ROOM_ID = '331856359'

AND B.INT_ID = A.PORT_ID)) B

WHERE A.STATEFLAG = 0

AND B.STATEFLAG = 0

AND A.ISTOP <> '0'

AND B.ISTOP <> '0'

AND A.STATUS = '0'

AND B.STATUS = '0'

AND A.OPTI_CIRCUIT_ID IS NULL

AND A.SEQ_UUID = B.SEQ_UUID

AND EXISTS (SELECT 0

FROM RES_DEV_TERMINAL B

WHERE B.ROOM_ID = '331856359'

AND B.INT_ID = A.PORT_ID);