Re: Oracle SQL question

the /*+ ..... */ indicates the force to use a certain method and another hint is to use a bitmap index on columns where there are just a few possible distinct values like sex has probably three: M,F and null, it will increase speed.

Have fun

<gweilo97_at_my-deja.com> wrote in message news:85cj2v$21$1_at_nnrp1.deja.com...
> Hello.
>
> Can anyone shed some light on what the following query is doing and if
> it should work...
>
> SELECT /*+ index (flow, flowpk) */
> flow.sec_id,flow.flow_id,flow.flow_amount,flow.flow_date,flow.flow_term,
> flow.flow_type_code,flow.official_b,flow.period_start_date,
> flow.period_maturity_date,flow.flow_calc_date,flow.flow_calc_date,
> flow.flow_calc_code,flow.flow_rate_calc_code,flow.principal_amount,
> flow.underlying_flow_id,flow.underlying_interest_flow_id,
> flow.reset_group_id,flow.start_term,flow.maturity_term,
> flow.flow_calc_rate,flow.flow_discount_rate,flow.ref_index_spread_rate,
> flow.ref_index_factor,flow.ref_index_term,flow.manual_reset_b,
> flow.exercise_code,flow.put_call_code,flow.strike_price,
> flow.option_maturity_date,flow.option_maturity_term
> FROM temp_sec, flow , sec
> WHERE temp_sec.sec_id = flow.sec_id AND temp_sec.hostname_pid_code =
> 'XXXX'
> AND flow.sec_id = sec.sec_id AND sec.custom_flows=1
>
> The questions really is about the /*+ index (flow,flowpk) */ statement.
>
> 2 thoughts on this, (1) does this mean the code between the /* and */
> is ignored/commented out or (2) this forces the use of the index flowpk
> from the table flow (only problem is that this index flowpk does not
> exist).
>
> If (2) is the case and the index does not exist are there any
> implications, e.g. performance?
>
> We found this in the Oracle documentation, in the Optimization Modes
> and Hints in Server Tuning chapter : Hints for Access Methods : Index :
>
> If you know that the value in the WHERE clause of your query appears in
> a very small percentage of the rows, you can use the INDEX hint to force
> the optimizer to choose an index scan. In this statement, the INDEX
> hint explicitly chooses an index scan on the SEX_INDEX, the index on
> the SEX column:
>
> SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few
> male patients */ name, height, weight
> FROM patients
> WHERE sex = 'M';
>
> Thanks
>
> Jason
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Jan 10 2000 - 07:08:47 CST