RE: Preventing Nested Table Full Access

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 1 Apr 2013 12:05:59 -0400
Message-ID: <034001ce2ef2$cdbcd130$69367390$_at_rsiz.com>

Of course we have no way to know how long is clobs are.

In lieu of indexes (which I'm not sure how you do one nested tables being used as predicates, I'd have to look that up and I'm doubting it is an effective selection means), you're going to get a full table scan regardless of how big it is.

But following Andrew's suggest that an FTS of at least the predicate columns is probably less costly,

then *possibly* something like

select query from object_cache
where cache_id in

   (select cache_id

         from object_cache
         where darray = value_arr(   'a','b','c','d')
             and marray =value_arr('a','b')
             and farray = value_arr('a','b')

   );

would do better. You might have to inject a nomerge hint to prevent the CBO from consolidating the queries.

I still don't really get what you're trying to accomplish. However comparing the time of just the select cache_id inlist query meaning just the:
select cache_id

         from object_cache
         where darray = value_arr(   'a','b','c','d')
             and marray =value_arr('a','b')
             and farray = value_arr('a','b');

part
and comparing the retrieval time would help reveal whether the time is being spent pawing through the value_arr predicates or in hauling back the CLOB.

If hauling back the CLOB is the time consuming part, then scanning to get just the clob_id references that satisfy your query and then querying the "object_cache" table for just those clob_id reference might help.

Simply displaying the plan with

select * from table(dbms_xplan.display_cursor(format=>'COST ALLSTATS LAST')); having included the hint
--+ gather_plan_statistics

might tell you a lot.

Good luck.

mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber
Sent: Monday, April 01, 2013 11:34 AM
To: my.oralce_at_gmail.com
Cc: oracle Freelists; anupam pandey
Subject: Re: Preventing Nested Table Full Access

If the object is cached and has only 1000 rows, you most likely want Oracle to be doing a full table scan since the entire object is most likely read in a single IO operation. Forcing an indexed read on such a small object will force at least two Io operations. Consider doing some analysis of the query and make sure that it is the read of the 1000 row table that is taking the time and not some other part of the operation. Then, if that really is the delay you could always add an indexed hint or something like that.

On Mon, Apr 1, 2013 at 10:22 AM, Anupam Pandey <my.oralce_at_gmail.com> wrote:

> Hi ,
> I am using a table as a cache for an oracle object type
> collection.The query to build the object collection is time consuming
> .So idea is to build the cache object once and store the elements in
> cache table and when next time its needed then return from cache table
> itself rather than going through entire process .
> But the problem is Oracle does a Full Table Scan always when I select
> from this cache table . So select time increases as number of rows in
> table increases .Following is the script for what I am using ..

>

> CREATE OR REPLACE TYPE VALUE_arr as table of varchar2(30);

>

> CREATE TABLE OBJECT_CACHE
> (
> CACHE_ID NUMBER,
> DARRAY VALUE_ARR,
> MARRAY VALUE_ARR,
> FARRAY VALUE_ARR,
> AARRAY VALUE_ARR,
> SARRAY VALUE_ARR,
> QUERY CLOB,
> CONSTRAINT key_pk
> PRIMARY KEY(cache_id)
> )
> NESTED TABLE DARRAY STORE AS DARRAY_TBL , NESTED TABLE MARRAY STORE AS
> MARRAY_tbl , NESTED TABLE FARRAY STORE AS FARRAY_TBL , NESTED TABLE
> AARRAY STORE AS AARRAY_TBL , NESTED TABLE SARRAY STORE AS SARRAY_TBL ;

>

> Select query which I am using is following ..

>

> select query
> from OBJECT_CACHE
> where darray = value_arr( 'a','b','c','d')
> and marray =value_Arr('a','b')
> and farray = value_arr('a','b');

>
>

> As of now this table has 1000 rows and select statement is taking
> nearly 11 secs for fetching the query .

>

> Is there a way to avoid the FTS or is there any other way to work out
> this solution ?

>
>

> Thanks,
> Anupam

>
>

> --
> http://www.freelists.org/webpage/oracle-l

>
>
>

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 01 2013 - 18:05:59 CEST