I hava data in an Oracle table that is organized as a graph that can contain cycles (see example).

My goal is to get all nodes that are children of node X. Let's say 2. My expected result is then: 3, 4, 5, 6, 8

I know that I can design a query like this:

with T (parent, child) as
(
                  select 1 parent, 2 child from dual
        union all select 1 parent, 8 child from dual
        union all select 2 parent, 3 child from dual
        union all select 2 parent, 4 child from dual
        union all select 2 parent, 8 child from dual
        union all select 3 parent, 4 child from dual
        union all select 3 parent, 6 child from dual
        union all select 4 parent, 5 child from dual
        union all select 5 parent, 8 child from dual
        union all select 6 parent, 5 child from dual
        union all select 7 parent, 3 child from dual
        union all select 7 parent, 5 child from dual
        union all select 8 parent, 6 child from dual
)
SELECT child, sys_connect_by_path(child,'/')
   FROM T
  START WITH parent = 2
CONNECT BY NOCYCLE PRIOR child = PARENT;

The problem with such a query is that it will go through all possible paths until they cycle, and there are way too many of them in my actual data. The result consists of many duplicates – Here it is:

child | sys_connect_by_path (for information)
3     | /3
4     | /3/4
5     | /3/4/5
8     | /3/4/5/8
6     | /3/4/5/8/6
6     | /3/6
5     | /3/6/5
8     | /3/6/5/8
4     | /4
5     | /4/5
8     | /4/5/8
6     | /4/5/8/6
8     | /8
6     | /8/6
5     | /8/6/5

My actual data is much more complex. the cost of execution of such a query is so huge that my TEMP tablespace, which was autoextendable, reached 10Gb (originally 500 Mb) and my database actually broke because of disk full.

I tried to design the query like this (recursive WITH clause) :

WITH T(parent, child) AS (/* same as above */)
   , descendants(node) AS
( SELECT 2 node FROM dual
  UNION ALL
  (
  SELECT child
    FROM T
   INNER JOIN descendants D
      ON T.parent = D.node
   MINUS SELECT node FROM descendants
  )
)
SELECT * FROM descendants

The problem that I encounter is:

• with Oracle 10g, this is not implemented (ORA-32033: unsupported column aliasing, and some customers use Oracle 9 or 10),
• with Oracle 11g, I get ORA-32041: UNION ALL operation in recursive WITH clause must have only two branches. If I remove the MINUS clause I will get cycles (ORA-32044: cycle detected while executing recursive WITH query).

How would you query my original data to get those nodes 3, 4, 5, 6, 8 efficiently? PL/SQL solutions are also welcome.

Thank you.

What is your expected maximum depth to reach any child node?

If it's relatively small, you could loop down, while checking for nodes you have already visited, in a manner something like this...

(Note, I'm not an Oracle expert so this is closer to pseudo code with a little real SQL mixed in)

CREATE TABLE myMap (parent INT, child INT);

INSERT INTO myTable SELECT NULL, 2 FROM DUAL;

WHILE (SQL%ROWCOUNT > 0)
LOOP

  INSERT INTO
    myMap
  SELECT DISTINCT
    dataMap.parent,
    dataMap.child
  FROM
    myMap
  INNER JOIN
    dataMap
      ON myMap.child = dataMap.parent
  WHERE
    NOT EXISTS (SELECT * FROM myMap WHERE parent = dataMap.parent)

END LOOP;

Depending on performance, you may also want a depth field in myMap; optimising the join so as to only join on the most recent nodes. This would imply two indexes; one for the JOIN (depth) and one for the NOT EXISTS (parent).

EDIT

Added the DISTINCT key word, to avoid the following case...
- Node 2 maps to 3 and 4
- Nodes 3 and 4 both map to node 5
- All children of node 5 would now be processed twice

GROUP BY, or many other options, can be used to cater for this instead of DISTINCT. It's just that the NOT EXISTS on it's own is not sufficient.

I have a table which has active an inactive entries, active = 1 for active and active = 0 for inactive.

I have a variety of indexes on this table, but I only need the indexes maintained for active entries, as the application only queries against active data. Inactive data needs to be kept because it can become active again, but this is generally only done with bulk updates, which wouldn't use an index anyway.

I'm noticing indexing the inactive entries (of there are increasingly more than active entries) takes quite a bit of space.

Is there a way in Oracle (10g) to do something like this:

create index an_idx on tab (active, col1, col2, ... , coln) where active = 1?

Previous attempt:

I tried using a function based index to set the first column to null when active = 0 like so:

create index an_idx on tab (decode(active, 1, 1, null), col1, col2, ... , coln)

But Oracle still seems to index the inactive columns in this case.

Partition the table by ACTIVE, create local indexes, and make the indexes for the inactive partitions UNUSABLE. This will eliminate the time spent on indexing inactive data.

create table tab(active number, col1 number, col2 number, col3 number)
    partition by list(active) 
    (partition tab_active values(1), partition tab_inactive values(0));

create index tab_index1 on tab(col1) local;

alter index tab_index1 modify partition tab_inactive unusable;

But there are some potential downsides to this approach:

• Not all types of indexes can be unusable.
• It's not normal to have unusable objects in the database. People will likely complain about it or assume it's a bug and rebuild it.
• Some operations, such as truncate, will automatically make the indexes usable again.