DQL Hints – Optimize Top

August 14, 2007 at 11:30 am | Posted in Documentum and Oracle, Performance | 5 Comments

DQL 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'))

Advertisements

5 Comments »

RSS feed for comments on this post. TrackBack URI

  1. 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.

  2. 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.

  3. 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.

  4. 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

  5. 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


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: