Oracle hints - still !!!

[Pietersz Mark x8938 <mpieters@CITIPOWER.COM.AU>]

Arnon

I have looked hi and low for the faq.txt and can't find it.  Is it in the
reserved area for those on a maintenance contract ?  We are licensed for SQR
through PeopleSoft and therefore don't have a direct support arrangement
with Brio.  Could you please send me a copy

Also the syntax that you have provided does not appear to work eg

begin-SELECT DISTINCT
/* +ordered */
account
from ps_gl_account_tbl
end-select

Produced the error

 SQL expression not ended, perhaps missing &name.
from ps_gl_account_tbl

Whereas the following syntax does run.

begin-SELECT DISTINCT
/*+ ordered */
account
from ps_gl_account_tbl
end-select

However the log file ( using -S parameter for SQR ) is producing this

select distinct /*+ ordered */account  from ps_gl_account_tbl

When I really want this

select /*+ ordered */ distinct account  from ps_gl_account_tbl

When I use the syntax

begin-SELECT
/*+ ordered */
etc etc

on my problem query the execution time is quick - about 2sec

When using

begin-SELECT DISTINCT
/*+ ordered */
etc etc

The execution time is slow - about 8.5 minutes - indicating that the hint is
being ignored as the hint is placed in the wrong area of the select.

Execution of the statements in straight SQL verifies these results and that
the addition of the distinct does not impact upon the performance of the
query to this extent.


For those who are still with me this is the SQR statement that I am using

begin-SELECT DISTINCT ON-ERROR=SQL-Error
/*+ ordered */

L.STATISTICS_CODE

SUM(L.POSTED_TOTAL_AMT) &Sum_Posted_Total_Amt

from [$TableNames]
WHERE L.STATISTICS_CODE [$_Stats_Clause]
[$_gsTreeDtlFldNameWhere] AND
L.LEDGER = $Ledger AND
L.Scenario = $Scenario
AND J.product = L.product
AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_product_TBL J2
WHERE J2.EFFDT <= $_Rqst_AsOfDate AND J.product = J2.product)
[$_Group_Where]
[$_Type_Where]
[$Timespan]
AND [$_Sort_Type_Fieldname]     =  $_Sort_Type_Field
AND [$_Sort_Group_Fieldname]    = $_Sort_Group_Field
[$Chartfield_And]
[$_Prcs_Business_Unit_Where]
[$_Rqst_Cp_Job_Where]
[$_Rqst_Cp_Project_Id_Where]
[$_Rqst_Cp_Function_Cd_Where]
[$_Rqst_Cp_Job_Exp_Cd_Where]
GROUP BY L.STATISTICS_CODE
END-SELECT

And this is the statement from the SQR.LOG file

 Cursor #22:
   SQL = select distinct /*+ ordered */L.STATISTICS_CODE,
         SUM(L.POSTED_TOTAL_AMT)  from [$lsSQLTemp] WHERE L.STATISTICS_CODE
         [$_Stats_Clause] [$_gsTreeDtlFldNameWhere] AND L.LEDGER = :1 AND
         L.Scenario = :2 AND J.product = L.product AND J.EFFDT = (SELECT
         MAX(J2.EFFDT) FROM PS_product_TBL J2 WHERE J2.EFFDT <= :3 AND
         J.product = J2.product) [$_Group_Where] [$_Type_Where] [$Timespan]
         AND [$_Sort_Type_Fieldname]     =  :4 AND [$_Sort_Group_Fieldname]
           = :5 [$Chartfield_And] [$_Prcs_Business_Unit_Where]
         [$_Rqst_Cp_Job_Where] [$_Rqst_Cp_Project_Id_Where]
         [$_Rqst_Cp_Function_Cd_Where] [$_Rqst_Cp_Job_Exp_Cd_Where] GROUP BY
         L.STATISTICS_CODE
 Compiles = 3
 Executes = 3
 Rows     = 3

Why does my hint appear to no work.  Help !!

Regards

Mark Pietersz

-----Original Message-----
From: Arnon Oppenheimer [mailto:arnono@SEMECH.CO.IL]
Sent: Friday, 31 March 2000 17:21
Subject: Re: Oracle hints


Mark,

Use:
BEGIN-SELECT DISTINCT
/* +ORDERED */    ! at column 1 - without blanks before the /*  - like table
column in sqr

BTW, you can find this at /sqr/ora/readme/faq.txt  from Brio !

Regards,
Arnon Oppenheimer
 -------------------------------------------------------------------
 SEMECH SOFTWARE MARKETING LTD.
 TEL : (972) - 3 - 5333144
 FAX : (972) - 3 - 5333132
 Email: arnono@semech.co.il
 -------------------------------------------------------------------


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This email message has been swept for the presence of computer viruses.

CitiPower Pty ACN 064 651 056
**********************************************************************