条SQL语句的迷惑。

最新推荐文章于 2025-12-04 17:50:16 发布

原创最新推荐文章于 2025-12-04 17:50:16 发布 · 9.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #date #table #优化

数据仓库专栏收录该内容

2 篇文章

订阅专栏

探讨了不同SQL查询语句在相同数据库环境下执行效率的巨大差异，尤其是表连接顺序对性能的影响。

SELECT *
FROM
"dbo"."DIM_BZ",
"dbo"."DIM_PZLX",
"dbo"."DIM_SLLX",
"dbo"."DIM_ZT",
"dbo"."DIM_DATE",
"dbo"."DIM_PZ",
"dbo"."DIM_KM",
"dbo"."DIM_KMTX",
"dbo"."FT_PZFL"
WHERE
("dbo"."FT_PZFL"."KMID"="dbo"."DIM_KM"."KMID") AND
("dbo"."DIM_KM"."BZID"="dbo"."DIM_BZ"."BZID") AND
("dbo"."DIM_KMTX"."KMID"="dbo"."DIM_KM"."KMID") AND
("dbo"."FT_PZFL"."PZID"="dbo"."DIM_PZ"."PZID") AND
("dbo"."DIM_PZ"."PZLX"="dbo"."DIM_PZLX"."LXID") AND
("dbo"."FT_PZFL"."DATEID"="dbo"."DIM_DATE"."DATEID") AND
("dbo"."DIM_KM"."SLID"="dbo"."DIM_SLLX"."SLID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_KM"."ZTID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_PZ"."ZTID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_ZT"."ZTID")

这条语句200389条记录，执行49妙。

SELECT "dbo"."FT_PZFL"."KMID",
"dbo"."DIM_KMTX"."KMID",
"dbo"."FT_PZFL"."PZID",
"dbo"."DIM_PZLX"."LXID",
"dbo"."DIM_DATE"."YEARID",
"dbo"."DIM_DATE"."QUARTERID",
"dbo"."DIM_DATE"."MONTHID",
"dbo"."FT_PZFL"."DATEID",
"dbo"."DIM_SLLX"."SLID",
"dbo"."FT_PZFL"."ZTID",
"dbo"."FT_PZFL"."SLDJ",
"dbo"."FT_PZFL"."WBHL",
"dbo"."FT_PZFL"."PZJFJE",
"dbo"."FT_PZFL"."PZDFJE",
"dbo"."FT_PZFL"."PZJFSL",
"dbo"."FT_PZFL"."PZDFSL",
"dbo"."FT_PZFL"."WBJFJE",
"dbo"."FT_PZFL"."WBDFJE"
FROM
"dbo"."DIM_BZ",
"dbo"."DIM_PZLX",
"dbo"."DIM_SLLX",
"dbo"."DIM_ZT",
"dbo"."DIM_DATE",
"dbo"."DIM_PZ",
"dbo"."DIM_KM",
"dbo"."DIM_KMTX",
"dbo"."FT_PZFL"
WHERE
("dbo"."FT_PZFL"."KMID"="dbo"."DIM_KM"."KMID") AND
("dbo"."DIM_KM"."BZID"="dbo"."DIM_BZ"."BZID") AND
("dbo"."DIM_KMTX"."KMID"="dbo"."DIM_KM"."KMID") AND
("dbo"."FT_PZFL"."PZID"="dbo"."DIM_PZ"."PZID") AND
("dbo"."DIM_PZ"."PZLX"="dbo"."DIM_PZLX"."LXID") AND
("dbo"."FT_PZFL"."DATEID"="dbo"."DIM_DATE"."DATEID") AND
("dbo"."DIM_KM"."SLID"="dbo"."DIM_SLLX"."SLID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_KM"."ZTID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_PZ"."ZTID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_ZT"."ZTID")

这条语句15妙，和上一条的区别在Select 后的字段列表。

SELECT "dbo"."FT_PZFL"."KMID",
"dbo"."DIM_KMTX"."KMID",
"dbo"."FT_PZFL"."PZID",
"dbo"."DIM_PZLX"."LXID",
"dbo"."DIM_DATE"."YEARID",
"dbo"."DIM_DATE"."QUARTERID",
"dbo"."DIM_DATE"."MONTHID",
"dbo"."FT_PZFL"."DATEID",
"dbo"."DIM_SLLX"."SLID",
"dbo"."FT_PZFL"."ZTID",
"dbo"."FT_PZFL"."SLDJ",
"dbo"."FT_PZFL"."WBHL",
"dbo"."FT_PZFL"."PZJFJE",
"dbo"."FT_PZFL"."PZDFJE",
"dbo"."FT_PZFL"."PZJFSL",
"dbo"."FT_PZFL"."PZDFSL",
"dbo"."FT_PZFL"."WBJFJE",
"dbo"."FT_PZFL"."WBDFJE"
FROM
"dbo"."FT_PZFL",
"dbo"."DIM_KM",
"dbo"."DIM_BZ",
"dbo"."DIM_KMTX",
"dbo"."DIM_PZ",
"dbo"."DIM_PZLX",
"dbo"."DIM_DATE",
"dbo"."DIM_SLLX",
"dbo"."DIM_ZT"
WHERE
("dbo"."FT_PZFL"."KMID"="dbo"."DIM_KM"."KMID") AND
("dbo"."DIM_KM"."BZID"="dbo"."DIM_BZ"."BZID") AND
("dbo"."DIM_KMTX"."KMID"="dbo"."DIM_KM"."KMID") AND
("dbo"."FT_PZFL"."PZID"="dbo"."DIM_PZ"."PZID") AND
("dbo"."DIM_PZ"."PZLX"="dbo"."DIM_PZLX"."LXID") AND
("dbo"."FT_PZFL"."DATEID"="dbo"."DIM_DATE"."DATEID") AND
("dbo"."DIM_KM"."SLID"="dbo"."DIM_SLLX"."SLID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_KM"."ZTID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_PZ"."ZTID") AND
("dbo"."FT_PZFL"."ZTID"="dbo"."DIM_ZT"."ZTID")

这一句发生了质的变化，运行N久，吃掉15G硬盘空间，并最终由于缓存不足而出不了结果。仔细看他与上一语句的区别。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
仅仅在于FROM后TABLE的顺序！！！把数据量大的表放在后面
但是如果换成Select * 又和第一条没有区别。

ORCLE怎么写都没有问题。

所以结论是：千万别相信MS$的所谓SQL优化。别以为只有索引的好坏才能影响SQL性能。