Oracle 分层查询性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/243528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Hierarchical Query Performance
提问by Ben
We're looking at using Oracle Hierarchical queries to model potentially very large tree structures (potentially infinitely wide, and depth of 30+). My understanding is that hierarchal queries provide a method to write recursively joining SQL but they it does not provide any real performance enhancements over if you were to manually write an equivalent query... is this the case? What sort of experiences have people had, performance wise, with using oracle hierarchical queries?
我们正在考虑使用 Oracle Hierarchical 查询来模拟可能非常大的树结构（可能无限宽，深度超过 30）。我的理解是，分层查询提供了一种编写递归连接 SQL 的方法，但是如果您要手动编写等效的查询，它们并没有提供任何真正的性能增强......是这种情况吗？人们在使用 oracle 分层查询方面有什么样的经验，在性能方面？
回答by
Well the short answer is that without the hierarchical extension (connect by) you couldn't write a recursive query. You could programmitically issue many queries which were recurisively linked.
嗯，简短的回答是，如果没有分层扩展（连接方式），您将无法编写递归查询。您可以以编程方式发出许多递归链接的查询。
The rule of thumb with everything database is, especially oracle, is that if you can issue your result in a single query it will almost always be faster than doing it programatically.
所有数据库的经验法则，尤其是 oracle，是如果您可以在单个查询中发布您的结果，它几乎总是比以编程方式执行更快。
回答by Josh Bush
My experiences have been with much smaller sets, so I can't speak for how well heirarchical queries will perform for large sets.
我的经验是使用小得多的集合，所以我不能说分层查询对大集合的执行情况如何。
When doing these tree retrievals, you typically have these options
在进行这些树检索时，您通常有以下选项
- Query everything and assemble the tree on the client side.
- Perform one query for each level of the tree, building on what you know that you need from the previous query results
- Use the built in stuff Oracle provides (START WITH,CONNECT BY PRIOR).
- 查询所有内容并在客户端组装树。
- 对树的每一层执行一个查询，建立在您从先前查询结果中知道的需要的基础上
- 使用 Oracle 提供的内置内容（START WITH,CONNECT BY PRIOR）。
Doing it all in the database will reduce unnecessary round trips or wasteful queries that pull too much data.
在数据库中完成这一切将减少不必要的往返或浪费的查询，这些查询会提取过多的数据。
回答by dacracot
Try partitioning the data within you hierarchical table and then limiting the partition included in the query.
尝试对分层表中的数据进行分区，然后限制查询中包含的分区。
CREATE TABLE
loopy
(key NUMBER, key_hier number, info VARCHAR2, part NUMBER)
PARTITION BY
RANGE (part)
(
PARTITION low VALUES LESS THAN (1000),
PARTITION mid VALUES LESS THAN (10000),
PARTITION high VALUES LESS THAN (MAXVALUE)
);
SELECT
info
FROM
loopy PARTITION(mid)
CONNECT BY
key = key_hier
START WITH
key = <some value>;
The interesting problem now becomes your partitioning strategy. Oracle provides several options.
有趣的问题现在变成了您的分区策略。Oracle 提供了几个选项。
回答by ScottCher
I've seen that using connect by can be slow but compared to what? There isn't really another option except building a result set using recursive PL/SQL calls (slower) or doing it on your client side.
我已经看到使用 connect by 可能很慢，但与什么相比？除了使用递归 PL/SQL 调用（较慢）构建结果集或在客户端执行此操作之外，实际上没有其他选择。
You could try separating your data into a mapping (hierarchy definition) and lookup tables (the display data) and then joining them back together. I guess I wouldn't expect much of a gain assuming you are getting the hierarchy data from indexed fields but its worth a try.
您可以尝试将数据分成映射（层次结构定义）和查找表（显示数据），然后将它们重新组合在一起。假设您从索引字段获取层次结构数据，我想我不会期望有多大收获，但值得一试。
Have you tried it using the connect by yet? I'm a big fan of trying different variations.
您是否尝试过使用 connect by 呢？我非常喜欢尝试不同的变化。
