Re: Preventing Nested Table Full Access
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: "my.oralce@xxxxxxxxx" <my.oralce@xxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Mon, 1 Apr 2013 13:10:47 -0700 (PDT)
what is the granularity of re-use for the query?
Do you only get to re-use the query when you get an exact match on all the
arrays that come in?
Or can you do a partial match and reduce the intensiveness of the query
generation process?
If only an exact match works, you might as well just use the approach someone
previously suggested to hash all the inputs and if you get a match, re-use the
clob.
That makes your design really easy.. two columns
checksum/hash
clob
if you get a match on the checksum/hash, than use the clob you already have.
Job
________________________________
From: Anupam Pandey <my.oralce@xxxxxxxxx>
To: Mark W. Farnham <mwf@xxxxxxxx>
Cc: oracle Freelists <oracle-l@xxxxxxxxxxxxx>; anupam pandey
<pandey83@xxxxxxxxx>
Sent: Monday, April 1, 2013 12:04 PM
Subject: Re: Preventing Nested Table Full Access
Thanks Mark and Andrew for looking into it .
We have a application from where we get these list of arrays . Based on the
array values we formulate a query and return it back to the application .
Logic to create the query is very complicated and takes around 15 to 20 sec
for execution . So we thought of building a table
where we will store this array values and the queury which was returned to
the application .
So first time when application calls my procedure I will find out the query
through process and will store the array elements and the corresponding
query in that table .Next time when we get the same call we can return the
query for same from the table itself rather than going through entire
process.
But this is now becoming bottleneck as the query for selecting the "query"
is doing FTS and also taking time .
Is there anyway to index the nested table or any other way I can try out
for speeding up the selection process ..
Thanks,
Anupam
On Mon, Apr 1, 2013 at 9:10 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
