Oracle SQL question

From: <gweilo97_at_my-deja.com>
Date: Mon, 10 Jan 2000 12:25:12 GMT
Message-ID: <85cj2v$21$1@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 - 06:25:12 CST