相同的 SQL 但不同的解释计划
我在具有相同索引集和表大小的 2 个不同环境中运行相同的 SQL（如下）。 但他们给了我 2 个不同的解释计划（附加）
- 使用合并连接笛卡尔 - 非常慢
- 使用 PX Coordinator / PX Send / PX RECEIVE - 非常快
查询：
SELECT *
FROM SIEBEL.S_PARTY PRTY, SIEBEL.S_CONTACT CONT, HPQ_IF_ENTERPRISE_DIRECTORY ED,SIEBEL.S_BU BU
WHERE PRTY.ROW_ID = CONT.PAR_ROW_ID
AND BU.ROW_ID(+)=CONT.BU_ID
AND CONT.EMP_NUM IS NOT NULL
AND ED.HPSTATUS NOT IN ('Terminated', 'Retired', 'Deceased')
AND ED.EMPLOYEENUMBER = UPPER (LPAD (CONT.EMP_NUM, 8, '0'))
AND (SUBSTR(ED.MODIFYTIMESTAMP,1,14) >= '19800101' OR ED.MODIFYTIMESTAMP IS NULL)
知道可能导致这种差异的原因是什么吗？第二个解释计划与（PX 事物）是什么意思？
请注意，我并不是要更改 SQL 查询（在生产中冻结）。
多谢。
I am running same SQL (below), at 2 different environments with same index set and table size.
But they gave me 2 different explain plans (attached)
- uses a Merge Join Cartesian -- very slow
- uses PX Coordinator / PX Send / PX RECEIVE -- very fast
Query:
SELECT *
FROM SIEBEL.S_PARTY PRTY, SIEBEL.S_CONTACT CONT, HPQ_IF_ENTERPRISE_DIRECTORY ED,SIEBEL.S_BU BU
WHERE PRTY.ROW_ID = CONT.PAR_ROW_ID
AND BU.ROW_ID(+)=CONT.BU_ID
AND CONT.EMP_NUM IS NOT NULL
AND ED.HPSTATUS NOT IN ('Terminated', 'Retired', 'Deceased')
AND ED.EMPLOYEENUMBER = UPPER (LPAD (CONT.EMP_NUM, 8, '0'))
AND (SUBSTR(ED.MODIFYTIMESTAMP,1,14) >= '19800101' OR ED.MODIFYTIMESTAMP IS NULL)
Any idea what is the possible things to cause this difference? And what does 2nd explain plan with (PX things) mean?
Note that I am not looking for changing the SQL query (freeze in production).
Thanks a lot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
PX表示并行处理。由于会话设置（或者如果其他数据库是不同的版本），这可能在其他数据库上不可用。
PX indicates parallel processing. That may not be available on the other database due to session settings (or if the other database is a different edition or version).
查询计划不仅取决于表大小或索引，还取决于许多其他因素，主要是表、列和索引的统计信息。这些统计数据包括诸如聚类因子之类的内容，这可能会对计算的成本产生很大的影响。
此外，不同的系统统计数据、优化器参数、表结构（例如分区与非分区）以及数据库块大小都会发挥作用，环境之间最细微的差异都可能导致采用不同的计划。
The plan for a query is not just dependant on the table size or indexes, but also on many other factors, mainly the statistics for the table, its columns, and its indexes. These statistics include such things as the clustering factor, which can make a big difference to the calculated cost.
In addition, different system statistics, optimizer parameters, table structure (e.g. partitioned vs. non-partitioned), and database block size, all come into play and the slightest difference between environments can cause a different plan to be favoured.
这些表的度数是否不同？在两种环境中检查此查询：
Do the tables have a different DEGREE? Check this query in both environments: