Re: how to force full table scan when index is not valid?

In article <01c02125$25ba5950$19c1790f_at_bj210276>,   "junfan" <fanjun_at_writeme.com> wrote:
> dear all,
>
> while loading data into a table "barcode" with index by sqlldr in
 direct
> mode, the indexes of table barcode is not useable, and during the
 loading
> time window, my application has to search the table, so i got error
 stating
> the index is unusable. I wonder if I can instruct Oracle to use full
 scan
> rather than index scan when index is not valid? My situation is under
> oracle 8.15i / winnt 4.0
>
> Tks in advance
>

I'm not sure if this will solve your problem...Not sure what will happen to queries against tables with disabled indexes. But I attempt to answer here only the question..."How can I instruct oracle to use a full scan"

You can use the FULL hint in the sql, this will explicitly choose a full table scan on the specified table.

SELECT /*+ FULL(A) */ col1, col2,...

     FROM barcode A
    WHERE ... If your app doesn't support hints, you might try altering your session and mucking around with the session parameter OPTIMIZER_INDEX_COST_ADJ (alter session set optimizer_index_cost_adj=1000;). This allows you to specify the degree to which Oracle chooses access paths that are indexfriendly.   1 is very friendly and 1000 is least friendly. (you might try setting it to 1000 to see if you get a full scan).

For more details, see the documentation for hints and init parameters.

hope that helps,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Sep 18 2000 - 01:14:11 CDT