Oracle APEX Tree Hierarchy Limitations/Issues

Question

I am using Oracle APEX 4.2.2 and have constructed a Tree region based off a view.

Now when I take this query (see below) and run this query say in Oracle SQL Developer - all is fine but when I place this same query within the page in Oracle APEX based off a Tree region - all saves correctly but when I run this query, no records/tree is displayed at all.

Now the underlying view can change in record size but for the example I am talking about here, I have just over 6000 records that I need to build a Oracle Tree hierarchy from.

One thing I have noticed is that if I reduce the record size to say 500 rows, the tree displays perfectly.

Questions:

1) Now is there a limitation that I am not aware of as I really need to get this going based on whether there are 500 records or 6000 records?

2) Is 6000 rows too many for a tree hierarchy representation?

3) Could it possibly be because that Oracle APEX 4.2.2 is now using js for building trees and there causing issues due to the quantity of data?

4) Is there a means of reducing the depth of the tree records so that I can still at least display something to the user?

My query is something like:

    SELECT case when connect_by_isleaf = 1 then 0
            when level = 1 then 1
       else -1
       end as status,
       level,
       c as title,
       null as icon,
       c as value,
       null as tooltip,
       null as link
    FROM t
    start with p IS NULL
    CONNECT BY NOCYCLE PRIOR c = p;

Also I've noticed that if I try and run the query in SQL Workshop, it doesn't work there either unless I reduce the record size down to say 500 records.

Yes, IE8 - why? Same issue in FF and Chrome on windows platform. — tonyf

Tom Tom · Accepted Answer · 2013-08-07T09:28:04

I asked about using IE because the 'too large tree' issue especially plays up in IE. I've seen this issue pass by and asked about a couple of times already. The conclusion was simply that there isn't much to be done about it and generally the browser(s) don't cope too well with a tree with such a large dataset. Usually the issue isn't there or is minimal in ff or chrome though, and ie is mostly not playing ball, and my guess is that this has to do with memory and dom manipulation.

1) Now is there a limitation that I am not aware of as I really need to get this going based on whether there are 500 records or 6000 records?

No limitation.

2) Is 6000 rows too many for a tree hierarchy representation?

Probably, yes.

3) Could it possibly be because that Oracle APEX 4.2.2 is now using js for building trees and there causing issues due to the quantity of data?

Trees are being built with jstree since 4.0 (don't know about 3.2). Apex puts out a global variable in the tree region which holds all the data. The initialization of the widget will then create the complete ul-li list structure. Part of the issue might be that there are so many nodes to begin with, and then how this is ran through jstree, and the huge amount of dom manipulation occuring. I'm not sure if this would go better with the newer release of jstree (apex version is 0.9.9 while 1.x has been released for a while now).

4) Is there a means of reducing the depth of the tree records so that I can still atleast display something to the user?

If you want to limit the depth you can limit the query by using level in the where clause. eg

WHERE level <= 3

Alternative options will probably be non-apex solutions. Dynamic trees, ajax for the tree nodes, another plugin,... I haven't really explored those as I haven't had to deal with such a big tree yet.

Oracle APEX Tree Hierarchy Limitations/Issues

2 Answers