1

I'm new to Documentum and have a simple problem, I am trying to retrieve all the record according to last modified.

Basically I have a datatable with 1000 records.

current we use

Select * from docfolder enabled (FETCH_ALL_RESULTS 1000)

The problem with the above statement is sometimes a newly created report or modified report will out of the 1000 range and our users will complain report not found * valid complain *

actually the last modified record does not even need to be the first on the list, it just need to appear.

I tried using 

Select * from docfolder order by r_modify_date enabled (FETCH_ALL_RESULTS 1000)

but this takes too long(never complete). I try replacing * with a,b,c,d (fields) but it does not work too.

May I know if there is other solutions to my issue?

I am considering documentum "ENABLE (RETURN_TOP 10)" hint but I doubt it work for Oracle 11g and how does documentum define top 1000?

UPDATE: It seems that using data link via toad is faster than using DQL, but I need a DQL solution due to legacy issues.

Documentum 6.0 and Oracle 11g.

seesee
  • 1,145
  • 5
  • 20
  • 46

2 Answers2

2

What version of Documentum are you using?

Ensure that there are indexes on the r_object_id. You may also want to add an index to the r_modify_date.

Further, when adding fields a,b,c,d - ensure that these fields are "non-repeating". In this way, Documentum will not need to join the _r table making the overall query faster.

Further, in DA, if you do the query, you can actually see the SQL query passed to Oracle. Take this query and run it in Toad and look for optimizations. You may also register the _s table so that your can DQL query the _s table directly.

Matt
  • 21
  • 1
  • egister the _s table so that your can DQL query the _s table directly. << how do you do this? – seesee Jan 22 '13 at 17:01
0

I manage to solve this problem by querying the under lining table in oracle database.

The reason for slow performance was because of the table begin joint behind to obtain the result.

In future if you have exhausted all ways to optimize your DQL, just fall back to querying the oracle database.

I have recommended for all table view and search to query via oracle.

Only individual report are retrieved via documentum, sometimes I question the purpose of having documentum.

seesee
  • 1,145
  • 5
  • 20
  • 46