有没有比下面更好的插入表格的方法?

huangapple go评论65阅读模式
英文:

Is there a better way to insert into a table than below?

问题

I have a need where I need to use two queries which differ from each other by a single condition.
Eg.

SELECT *
FROM table
WHERE attribute1 = 'Y';

SELECT *
FROM table
WHERE attribute1 IS NULL;

My need is to insert data from these two queries into a single table the only condition being that the data from the first query should go in first and then the second query.
I can obviously run the first query first insert and run the second query and then insert again.
My question is there a better way to do that?

英文:

I have a need where I need to use two queries which differ from each other by a single condition.
Eg.

SELECT * 
  FROM table
 WHERE attribute1 = 'Y';

SELECT * 
  FROM table
 WHERE attribute1 IS NULL;

My need is to insert data from these two queries into a single table the only condition being that the data from the first query should go in first and then the second query.
I can obviously run the first query first insert and run the second query and then insert again.
My question is there a better way to do that?

答案1

得分: 1

以下是您要翻译的内容:

对我来说,它看起来像

SELECT *
  FROM your_table
 WHERE (   attribute1 = 'Y'
        OR attribute1 IS NULL);

一个(更差的)替代方法是使用 unionall)这两个查询:

SELECT *
  FROM your_table
 WHERE attribute1 = 'Y'
UNION ALL
SELECT *
  FROM your_table
 WHERE attribute1 IS NULL;
英文:

To me, it looks like

SELECT *
  FROM your_table
 WHERE (   attribute1 = 'Y'
        OR attribute1 IS NULL);

A (worse) alternative would be to union (all) these two selects:

SELECT *
  FROM your_table
 WHERE attribute1 = 'Y'
UNION ALL
SELECT *
  FROM your_table
 WHERE attribute1 IS NULL;

答案2

得分: 0

如果您要请求的行数占表格的显著比例,那么是的,没有更好的方法。如果您要求第一个插入在第二个插入之前发生,那么保证这一点的方法是按顺序执行每个插入,作为单独的SQL语句。任何将两者合并的操作都可能导致结果混合的可能性。当然,您可以使用 ORDER BY 强制排序,您可以对 UNION ALL 做出假设(但是在最新版本中带有并发 union PX,这不是一个安全的假设),但这些都是比仅使用两个语句要差的方法。排序可能会非常昂贵。

然而,如果您正在查询一个大规模的表格,每个查询只期望返回小部分行,那么排序的成本可能比两次扫描表格要便宜。在这种情况下,您可以组合这些查询,并使用显式的 ORDER BY 强制排序:

SELECT *
  FROM your_table
 WHERE NVL(attribute1,'Y') = 'Y'
ORDER BY DECODE(attribute1,'Y',1,2)

但除非这是为了实现手头的性能目标而必要,否则这种方式不够清晰,因此不是一种很好的编码方式。在大多数情况下,使用两个语句从各个方面来看都是最佳选择。

英文:

If the # of rows you are requesting is significant percentage of the table, then no, there is no better way to do that. If you require that the first insert happen before the second insert, then the way to guarantee that is to do each in order, as separate SQL statements. Any combining of the two leads to the possibility of mixing the results. Sure, you can force ordering with ORDER BY, you can make assumptions about UNION ALL (but with concurrent union PX in recent versions, this is not a safe assumption), but these are all inferior methods to simply using two statements. A sort can be quite expensive.

If however you are querying a massive table and each of your queries expects only a small percent of rows, then the cost of a sort might be cheaper than two table scans. In that case, you can combine the queries and force the order with an explicit ORDER BY:

SELECT *
  FROM your_table
 WHERE NVL(attribute1,'Y') = 'Y'
ORDER BY DECODE(attribute1,'Y',1,2)

But unless this is necessary to achieve the performance goals at hand, it is less clear and therefore not a great way to code. Most likely in your situation, using two statements is best from all perspectives.

huangapple
  • 本文由 发表于 2023年6月26日 19:28:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76556246.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定