Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect Tuning (9i)
Bulk Collect Tuning [message #293082] Thu, 10 January 2008 17:20 Go to next message
psp007
Messages: 2
Registered: January 2008
Junior Member
I am using the following Query:

SELECT COL1
BULK COLLECT INTO lt
FROM TABLE1
WHERE CONTAINS( COL2, '%' || parameter || '%', 1) > 0;

This is taking 1 sec to return data from TABLE1 of 500,000 records.
I have a context index on the VARCHAR2 col I am using in the CONTAINS as I am using part of the string for matching.

Since I need to use this 1/2 million times how can I further tune this query to reduce the time?

Re: Bulk Collect Tuning [message #293136 is a reply to message #293082] Fri, 11 January 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The real question is why do you need to execute this 1/2 million times?

1 sec is pretty good response time given the query.

Regards
Michel
Re: Bulk Collect Tuning [message #293241 is a reply to message #293082] Fri, 11 January 2008 05:46 Go to previous messageGo to next message
psp007
Messages: 2
Registered: January 2008
Junior Member
The different Data from Table1 needs to be processed for 1/2 million records hence Table1 will be hit 1/2 million times.

Any ideas will be useful to reduce the time.
Re: Bulk Collect Tuning [message #293243 is a reply to message #293241] Fri, 11 January 2008 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The different Data from Table1 needs to be processed for 1/2 million records hence Table1 will be hit 1/2 million times.

Why? Are you sure there is a real need for this?

Regards
Michel
Re: Bulk Collect Tuning [message #293444 is a reply to message #293082] Sat, 12 January 2008 19:29 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you should rethink your update process maybe. Also, are you sure this query is correct. For example, if your parameter was this value:

ABC

then of these two rows which ones should you see from your query:

'XYZABCDEF'
'XYZ ABC DEF'

if you are doing an instr() type deal then you would expect both, but if you are doing a word search the you would expect only the second one (assuming you are delimiting words with space).

question: with one invocation of the query, how many rows should come back? one? if so, are you going to look up one row, 1/2 million times? if so, then perhaps you should rethink on how you could instead lookup 1/2 million rows, one time. Of course I am not sure you would want to bulk collect that many items into memory but if it is only one column, maybe you can get away with it easy enough.

Still you should be getting the idea of my thoughts here. It ain't the bulk collect that will help or hurt you with this one, it is how you cycle through your rows that is the horse for this cart.

Kevin
Previous Topic: Curious Behavior on Old Oracle 10g Server
Next Topic: What is the meaning of this statement
Goto Forum:
  


Current Time: Wed Dec 07 08:49:49 CST 2016

Total time taken to generate the page: 0.08215 seconds