Re: Problems when using distinct and hints in a SQR select statement -Reply

[Richard Watts <rwatts@MDC.COM>]

One of two ways I have handled this is to create a view with
the hint and, in SQR,  select distinct on the view, or create a
distinct view and hint the SQR.  I prefer a hinted view with
distinct SQR, that way the sort(distinct) is performed on the
result of the query rather than on the entire table.  If you
distinct an unqualified view it will most often select all rows in
the table.

Regards
Rick Watts
rwatts@imainc.com
Information Management Associates, Inc.



>From:  Lie, Ove - Broomfield
>Sent:  Tuesday, March 18, 1997 4:05 PM
>To:    Multiple recipients of list SQR-USERS
>Subject:       Problems when using distinct and hints in a
SQR select statement
>
>I am currently running a large query against an Oracle
database that is
>using cost based optimization. For some strange reason
the optimizer is
>not using one of the indexes on a table (> 120,000 rows,
where 65% of
>the fields are unique for the field used to identify the rows in
the
>table).  Using sqlplus and a rule base hint makes the query
20 times
>faster.
>
>But the problem is to force SQR to use the hint. It work well
as long as
>I am not using a "distinct" after the begin-select. But I need
the
>distinct. Any suggestions....?
>
>Here is an example just to illustrate the problem:
>
>begin-select distinct
>--+RULE                    ! DOES NOT WORK unless the
distinct is removed.
>b.col1 (+1,1)
>from  batch_param b
>where          b.id = 15178
>end-select
>
>Thanks.
>