I had this query that originally caused massive timeouts:
select d.r_object_id
from isc_fiche d, dmr_con c
where any c.parent_id = d.r_object_id
group by d.r_object_id
having count(*) > 2
yet when I add the enable (return_top 10)
to the end then the performance issues seem a thing off the past. Apparently (according to colleagues) that statement could have a perfomance-improving effect.
Could someone clarify this for me?
Full query with 'way' better performance:
select d.r_object_id
from isc_fiche d, dmr_con c
where any c.parent_id = d.r_object_id
group by d.r_object_id
having count(*) > 2
enable(return_top 10)
enable (return_top 10)
modifies the executed SQL statement, it adds a limiting clause to it, like ROWNUM <= 10
in Oracle. It depends on the underlying RDBMS, so I guess it's not ROWNUM <= 10
if you use EMC Documentum with Microsoft SQL Server.
You can run DQLs on the web interface of ECM Documentum (if I remember correctly it's called Webtop). There is a checkbox on the DQL run page which shows the generated SQL. You should check there what's the difference between the two DQL query.