Tilmann, the site_node function is in there because it is in the code I found. I didn't write the query, I am just using it as an example. Where did I find this query? In /www/index-oracle.xql - the first page you visit on a freshly installed OpenACS system. Or well, the first page you visit on any OpenACS system.
Don, yes the CBO is apparently bad about estimating the price of a PL/SQL function - hence performs it on every row.
Barry, look above. Of course I had run the query without the permission check and had posted the result in this thread. For your convenience the result:
98 recursive calls
8 db block gets
592 consistent gets
0 physical reads
Still expensive (the result has only 13 rows), but *definitely* not the culprit here.
http://technet.oracle.com/doc/oracle8i_816/server.816/a76962/ch4.htm: Yeah, materialized views are EE edition only 🤔
Why not just pass on the magic object id from the TCL code. It would give the database all the information it needs. cc_users could be commented e.g.: -- this is ugly -1 is the magic_object_id for the_public
Robert, the CBO rewrites the query anyway. Per default it tries up to 80.000 different execution plans and however you shuffle around your PL/SQL function, your query plan is most likely the same. (There is a "trick" with rownum in subselects though)
