RE: Preventing Nested Table Full Access
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <my.oralce@xxxxxxxxx>, "'oracle Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Apr 2013 11:40:08 -0400
well, you have no indexes on any of your predicate columns, so it indeed
will have to do a full table scan.
Your query looks vaguely like you might want to be querying a bitmap index,
but you seem to be storing hard wired predicate values in nested tables, and
you're trying to look up a candidate set of clob payloads.
I am at a loss to discern what you are actually trying to accomplish.
Perhaps a little more exposition about what you're trying to accomplish
would be helpful.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Anupam Pandey
Sent: Monday, April 01, 2013 11:23 AM
To: oracle Freelists
Cc: anupam pandey
Subject: Preventing Nested Table Full Access
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
