Re: Most Performant way of performing slow sql query

C Foy wrote:

> Hi All,
>
> I need some help to find an optimal way to perfom the following :
>
> Given a table sydsession with column sessionid which contains numeric
> data between 0 and 65535
>
> problem: I need to find the most performant way of performing the
> following query -
> want to select a sessionid between 0 and 65535 that is not present in
> the table sydsession (sorted from 0 to 65535)
>
> The following query works but is extremely slow:
>
> SELECT sydsessionid FROM (SELECT sydsessionid FROM (SELECT
> column_value AS sydsessionid FROM TABLE(CAST(numbers(0,65535) AS
> number_table))) WHERE sydsessionid NOT IN (SELECT DISTINCT
> sydsessionid FROM sydsession WHERE sydsessionid BETWEEN 0 AND 65535)
> ORDER BY sydsessionid) WHERE rownum <= 1;
>
> (taking about 6 seconds to execute)
>
> In addition, this query also works but is still slow (although faster
> than the previous):
>
> SELECT MIN(sydsessionid) FROM (SELECT sydsessionid from counters MINUS
> SELECT DISTINCT sydsessionid FROM sydsession WHERE sydsessionid
> BETWEEN 0 AND 65535);
>
> This table uses a temporary table called counters which contains
> numeric values 0 to 65535.
>
> Thanks in advance,
>
> Colin

Go to http://www.psoug.org
click on Morgan's Library
click on explain plan
You will fine a six queries, labelled 1 through 6. Take a look at the various examples and adapt them for your purpose: Especially number 6 and modify to use NOT EXISTS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
   http://www.newsfeed.com       The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----