Re: Full table scan query

Hi Sybrand,

thanks for your reply, answers to your questions follow :

>> Given the fact your db_file_multiblock_read_count is 25 (which is way
>> to high, as it should not exceed 64k when multiplied with
>> db_block_size), it looks like almost every execution plan with a full
>> table scan will be cheaper than using the index.

Why should it not be greater than 64K ?
It is an 8K block size (I forgot to mention) so it is 200Kb.

>> At this point a few questions /hints remain
>> - what is the datatype of the affected column?

The leading column is NUMBER(12) the second is VARCHAR2(25).

>> - are you using literals or bind variables (bind variables will not
>> use histograms)

I'm know that the application uses either packages or bind variable, but I'm not sure which way it has been configured (it's a Uniface app), however, I'm told by the support people here that a literal is being used at the moment. I am getting confirmation of this fact. I'm aware that bind variables won't let the optimiser use the histograms, but in testing myself, I'm using literals.

>> - did you try setting event 10051 trace name context forever, level 1
>> This will show you the reasoning of the optimiser in your trace file.

This is a new one for me, but I shall try it out and let you know how I get on.

Thanks again.

Regards,
Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com
------------------------------------------------------------------------