Re: cbo/rbo: full blob-table scan..

Jan

Do you have access to the SQL or is it a black box application? If you access to the SQL, add a hint to tell it to use the PK index.

HTH. Pete

Jan Rosinowski wrote:

> hi,
>
> we've got a table like
>
> create table templates(
> name varchar(50),
> releasedate date,
> data long raw,
> primary key(name,releasedate));
>
> and are interested in obtaining
>
> select name,max(realeasedate)
> from templates
> group by name;
>
> usually this select will retrieve about 1/3 of the records in
> templates and could be satisfied by use of the pk.
>
> cbo and rbo decide to make a full table scan which under normal
> circumstances might be quite a good idea, but as templates.data holds
> blobs of about 4k-40k we encounter severe thrashing due to the table
> scans..
>
> what can we do? we use ora 7.3.4
>
> is createing a templateswithoutdata-table, maintained by triggers, the
> only way to go?
>
> ciao, jan
>
> dokumentenmanagement und mehr: http://www.rsp.de/ vertriebspartner gesucht!

--
Regards

Pete

Received on Fri Jul 30 1999 - 10:53:33 CDT