Oracle Hierarchical Query Performance

Question

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?

Answer 1

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.

Answer 2

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).

Doing it all in the database will reduce unnecessary round trips or wasteful queries that pull too much data.

Answer 3

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.

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.

Answer 4

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.