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

In article <37a142b4.97783345_at_160.45.4.4>,   spamfilter_at_rosinowski.de 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!
>

Hi.

 Do you have a table with "name" field as PK.  If yes -

1. Define FUNCTION : CREATE OR REPLACE FUNCTION get_last ( p_name IN VARCHAR2 ) RETURN DATE IS CURSOR temp_crs ( p_n VARCHAR2 ) IS SELECT /*+ INDEX_DESC ( template template_pk ) */ releasedate FROM template WHERE name = p_n and releasedate < SYSDATE; l_date DATE := NULL; BEGIN OPEN temp_crs ( p_name ); FETCH temp_crs INTO l_date; IF temp_crs%NOTFOUND THEN l_date := NULL; END IF; CLOSE temp_crs; RETURN l_date; END get_last;
2. SELECT name, get_last ( name ) FROM ttt ;

 In no such table exists - try to use BLOB data instead of RAW LONG :  BLOBs are stored physically at another location and only REF is kept  within the base table. It may save you FULL scans.  I think it's good idea anyway because you supposed to have now a lot  of row-chainings and you pay dearly for any access to that table.

 Another possibility is to create templateswithLASTdata and maintain it  through triggers.

 Good luck. Michael.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Aug 01 1999 - 14:48:53 CDT