条SQL语句的迷惑。

最新推荐文章于 2024-09-14 18:42:05 发布

LocustWei

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量9k

点赞数

本文链接：https://blog.csdn.net/LocustWei/article/details/884306

版权

数据仓库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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性能。