如何优化给定的 db2 查询， [英] how to optimise the given db2 query,
问题描述
考虑我的 DB2 查询如下.
Consider my DB2 query as below.
Select something from some tables,
(select something from some tables where bhla=bhla
Unionall
select something from some tables where bhla=bhla
union
select something from some tables where bhla=bhla) x
where bhla=bhla
看起来像
select x.something, y.somethingelse from table y,
(select something from some tables where bhla=bhla
Unionall
select something from some tables where bhla=bhla
union
select something from some tables where bhla=bhla) x
where bhla=bhla
我的查询是优化上面的查询(更好的查询方式).请提供一些关于索引的输入以及如何使用它.我需要优化上述查询，所以请提出一些建议.
My query is to optimise the above query(better way of use the query). please provide some inputs on indexes and how to use it. I need to optimise the above query so please pour some suggesstion.
你能建议 hw 重新编码下面的 where 子句吗
Can you guyz suggest hw to recode the below where clause
在哪里
(ID.EXCH_RT <> 0.000000 AND ID.EXCH_RT IS NOT NULL)
AND I.ACT_LOC_TS BETWEEN ( :Param_290_From_Date CONCAT ' 00:00:00.00000') AND ( :Param_300_To_Date CONCAT ' 23:59:59.99999')
推荐答案
无法根据给出的信息进行优化.
当人们提出不具体的问题时，通常只会参考如何提出问题足够的.但是，我看到很多这种特殊情况:寻求帮助以优化定义不明确的 SQL 查询.所以我想更详细地解决这里的问题.
Normally people are just referred to How to Ask a Question when they ask a question that isn't specific enough. However, I see a lot of this specific case: asking for help with optimizing an SQL query that is not well-defined. So I wanted to address what is wrong here in more detail.
为什么不能优化?
我们不知道任务.优化是您为任务所做的事情，而不是一段代码.您的目标是尽可能高效地完成任务.您发布的代码可能是也可能不是最好的方法.如果我们只有代码，不知道代码的用途，我们就无法评估您的代码是否合适.
We don't know the task. Optimization is something you do for a task, not a piece of code. Your goal is to get the task done as efficiently as possible. The code you have posted may or may not be the best way of doing that. If we only have the code, and don't know what the code is for, we have no way of assessing the appropriateness of your code.
我们不知道表结构.SQL 的优化很大程度上取决于相关表的结构.涉及哪些列，哪些数据类型?这些表是如何相互关联的?在这种情况下，我们甚至不知道涉及到多少个表，因为一些查询作用于某些表".
We don't know the table structure. Optimization for SQL very much depends on the structure of the tables in question. What columns are involved, and what datatypes? How do the tables relate to each other? In this case, we don't even know how many tables are involved, because some of the queries act on "some tables".
我们不知道数据.每张桌子有多大?考虑下面的示例查询.优化了吗?答案是，这一切都取决于数据.如果 table1 很小而 table2 很大，那将是优化查询的好方法.但是，如果 table1 很大而 table2 很小，那将是一个非常糟糕的查询设计，会浪费大量时间.如果不了解数据，就无法判断最佳设计.
We don't know the data. How big is each table? Consider the example query below. Is it optimized? The answer is, that all depends on the data. If table1 is small and table2 is huge, that would be a good way to optimize the query. However, if table1 is huge and table2 is small, that would be a very poor query design that wastes lots of time. There is no way to tell the best design without knowing anything about the data.
这取决于您使用的数据库(感谢 WarrenT).在本例中，您已经指定了 DB2，与许多仅陈述SQL"的问题相比，这是一个好的开始.但是，了解您使用的平台和版本仍然会有所帮助.这对可用的功能有很大的影响.
It depends on what database you are using (Thanks WarrenT). In this case, you have specified DB2, which is a good start compared to the many questions that just state "SQL". However, it would still be helpful to know which platform and version you are using. This makes a significant difference in what features are available.
查询示例:
with complicated as (
select id,
char(foo) || case when bar = 1234 then 'a' else 'c' end || 'baz'
as complicated_field
from table1
)
select * from table2
join complicated on
complicated.id = table2.id and
complicated.complicated_field = table2.some_field
我希望这会有所帮助.如果您提供更多信息，我们很乐意为您提供进一步帮助.
I hope this is helpful. We would be glad to help further if you provide more information.
这篇关于如何优化给定的 db2 查询，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
