Using Hint in Stored Procedure

[saw15]

I am using a stored procedure to run a select distinct against and table, spooling the file to disk.

I need to add a hint specifying a index.

Not sure where / how this goes, any help or tips would be helpful. Below is the sql statement, name of the index is: idx1_contract_search. Again, I need to specify that this select statement use this index.

SELECT DISTINCT t1.seq_prov_id,t2.line_of_business,
to_number(t3.contract_search_type),t2.panel_id,
t2.ipa_id,t2.pcp_flag
FROM master t1,contract t2,contract_search t3

 

[carp]

No index will be used; index usage is driven off of a WHERE clause. Your query will use a full table scan; since it needs to look at every row in the table, an index would actually be less efficient.

Also, since you have not specified a WHERE clause, your query will also result in a Cartesian join.

 

[ociruj]

try this...

/*select statement using index idx1_contract_search*/

SELECT DISTINCT t1.seq_prov_id,t2.line_of_business,
to_number(t3.contract_search_type),t2.panel_id,
t2.ipa_id,t2.pcp_flag
FROM master t1,contract t2,contract_search t3


did i get ur question right...?

 

[DaPi]

carp is spot on.

I would add that the Cartesian join (due to lack of WHERE clause) can be VERY SLOOOOOW (three tables of 1'000 rows each, would produce 1'000'000'000 rows of output - that takes a while!). It is also referred to as a Cartesian product (as in multiplication).

If ever you do an Explain Plan, the word "Cartesian" in the output should set the alarm bells ringing.

My guess is that the logic must require a WHERE - once that is sorted: (a) you will get the right results (b) it may run OK without any hints at all.