Don, what about this query : "Show me all the subsites I have access to". This query was performed on *every* single page in ShareNet and originally - iirc - took two seconds. Uh, of course we moved it into a static cache. Not using the get_url and name PL/SQL function made it a pretty light query.
Verbose description: used by search and site-wide linking. Very important packages...adding a varchar2(4000) was not performance-inhibiting on Oracle.
As it stands I'm more interested in deprecating two dangegerously useless PL/SQL functions than in throwing more fields onto acs_objects. There seems to be a consensus on how to achieve that: *fine*.
If I have achieved these things as well, I'll pop open an extra bottle of Weißbier tonight:
- Made people aware that PL/SQL in SQL (or PG/SQL in SQL) usually doesn't scale.
- How do I get this and that information efficiently in a frequently-used site-wide package? Hmm, why not from acs_objects_$annotation_table?
- Joining with acs_objects to get only trivial information like the creation user is expensive.
