Thanks everyone for the reply. Thanks cheerio for the explanation why the sql pass through query should run faster.
In response to the question as to what server I'm sending my sql pass through query, it is to an Oracle database - Documentum. Here is the sql pass through query:
SELECT
cd_object_name as Prefix,
cd_std_revision as revision,
c.document_class as Doc_Type,
ccd_object_name As Change_Document,
ccd_std_revision As Change_Revision,
to_char(a.date_on_document,'MM/DD/YYYY') as Change_Doc_Date
from ccd_view a, ccd_affects_cd_v b, cd_view c
where c.cd_oid = b.child_id (+)
and b.parent_id = a.ccd_oid (+)
You will find that FROM clause includes views. For security reasons, I chose to retrieve the data from views instead of actual base tables. Supposedly, the columns "cd_oid","child_id","parent_id" are indexed.
When I changed the query to a regular select, I linked to the same views and the regular select used them:
SELECT cd_object_name AS Prefix, cd_std_revision AS revision, CD_VIEW.DOCUMENT_CLASS AS Doc_Type, ccd_object_name AS Change_Document, ccd_std_revision AS Change_Revision, format(CCD_VIEW.date_on_document,'MM/DD/YYYY') AS Change_Doc_Date
FROM (CD_VIEW LEFT JOIN CCD_AFFECTS_CD_V ON CD_VIEW.CD_OID = CCD_AFFECTS_CD_V.CHILD_ID) LEFT JOIN CCD_VIEW ON CCD_AFFECTS_CD_V.PARENT_ID = CCD_VIEW.CCD_OID;
Also, the report that uses the above query accepts a user parameter. So via a module, I set the record source to something like this:
"select from query_name WHERE field = " & user_parameter
Cheerio, if the Oracle optimizer somehow ignores the indexes, would this explain why the regular select runs faster than the sql pass through query?
Please advise.
Thanks,
mlz