DQL Hints – Optimize Top
August 14, 2007 at 11:30 am | In Documentum and Oracle, Performance | 5 CommentsDQL Hints are a very useful feature brought in by Documentum 5 that has been incrementally improved from 5.1 through 5.3. No doubt we will have some more in Documentum 6. DQL hints usually translate into a SQL level hint when Content Server transforms DQL into a SQL statement. For example when the database is Oracle the SQL level hint will look some thing like this:
select /*+ hint */ object_name from dm_sysobject_s
The hint is added after the ’select’ keyword between the /*+ and */ tokens.
There is a DQL Hint called optimize top which might produce a performance boost when returning the first few records from a potentially large result set. According to the DQL documentation this translates to an ‘optimize top’ hint in Oracle. This got me scratching my head as I didn’t recall such a hint in Oracle. A quick google confirmed that this is not a recognised Oracle hint so the quickest way of finding out is to check the actual SQL produced by documentum. As I’ve pointed out before there are 2 ways to do this, either call ‘execute get_last_sql’ after you have run a DQL or use the Documentum Administrator ’show SQL’ option (which does the same thing on your behalf). What I found was that ‘optimize top’ actually translates to a ‘first_rows’ hint in Oracle.
The idea of first_rows is that the Cost-based Optimizer (CBO) should choose a query execution plan that retrieves the first rows quickly even if the overall cost to retrieve all rows is greater than other query plans. This is potentially a good idea for many queries that are executed in ‘paged’ query scenarios. A paged query occurs for example when viewing a large folder in Webtop. The query to return all the documents is run but only the first 50 or 100 rows are initially retrieved to build the first few pages. If the user then selects the ‘next’ page the next 50 or 100 rows are retrieved.
first_rows is actually a deprecated hint in Oracle. Since Oracle 9 the preferred hint is first_rows_n where n=the number of rows to optimise for. So for example first_rows_10 will optimise for returning the first 10 rows quickly. If you want to use this hint from DQL then you would have to use ‘pass-through’ hints:
select * from dm_document enable(oracle('first_rows_10'))
5 Comments »
RSS feed for comments on this post. TrackBack URI
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
I’ve recently started using ENABLE(FETCH_ALL_RESULTS 0) to grab large result sets from the content server quickly. The hints seem like a great way to manipulate the optimizer when having a performance problem.
Comment by imjoebond — August 29, 2007 #
Interesting. Is this in Oracle or SQL Server? In oracle I wouldn’t have thought it affects optimization as there is no change to the underlying sql with this hint.
Comment by Robin East — August 30, 2007 #
My mistake, the FETCH_ALL_RESULTS 0 forces the content server to grab all results from the db and close the cursor.
per the dql documentation
“The hint does not affect the execution plan, but may free up
database resources more quickly.”
It is for Oracle.
Comment by imjoebond — September 5, 2007 #
Hi Robin,
I am working in Documentum and need a favour from you.. I need some sample DQL queries and guidelines and references for DQL queries. If you have any please share and any sites for reference.
Many Thanks !!!
SRK
Comment by evergreensrk — December 13, 2008 #
Hi Robin,
I am working in Documentum and need a favour from you.. I need some sample DQL queries and guidelines and references for DQL queries. If you have any please share and any sites for reference.
Many Thanks !!!
SRK
Comment by evergreensrk — December 13, 2008 #