DQL Hints – Part 1

Posted by Sumeet on Oct 29, 2007 in Documentum |

DQL hints are handled differently by different RDBMS. Hence,

* Measure performance before and after using the hint.
* Turn on SQL tracing on the server. This will write the underlying SQL queries to the trace log which may then enable you to debug problems.
* Check “Show the SQL” in the DQL utility. This will also help you view the underlying SQL queries

Efficient Queries- Indexing
Underlying databases tend to search quickly on both the single-valued and repeating-valued tables because their rows (even in the repeating-valued) table are relatively unique.
While querying repeating attributes, use
select object_name from dm_document
where any (keywords = ‘anand’) or
any (authors = ‘Sumeet’)

instead of

select object_name from dm_document
where any (keywords = ‘anand’ or authors = ‘Sumeet’)

* Querying over non-indexed columns can be slow
* Indexing frees memory in a production environment
* Custom attributes with a high degree of uniqueness and often searched by users make good indexes
* System-defined indexes are created on some object type tables
* User-defined indexes are created using Documentum Administrator or by executing the DQL administrative method MAKE_INDEX
* Indexes are represented in the Docbase by objects of type dmi_index

Improve response time and resource use depending on RDBMS
SELECT object_name
FROM dm_document
ENABLE(RETURN_TOP 5)

Other Hints

* OPTIMIZE_TOP N hint directs the underlying database to return the first N rows returned by a query quickly. The remaining rows are returned at the normal speed.
* FORCE_ORDER hint controls the order in which the tables referenced in the query’s FROM clause are joined. The tables may be database tables or object type tables.
* Passthrough hints are passed on directly to a specific database. In DQL, you identify the database and then specify the hint meant for that database.

For example, here is a DQL query with a hint for Oracle that will cause the query to execute to begin returning rows as soon as possible.
SELECT r_object_id FROM dm_document
ENABLE (ORACLE(’all_rows’))

Refer to the Content Server DQL Reference Manual for a detailed description and the recommended usage of hints.

Reply

 


Copyright © 2017 dm_maniacs All rights reserved. Theme by Laptop Geek.

wordpress stats