Re: speed this up?

From: Riyaj Shamsudeen <>
Date: Wed, 22 Oct 2008 21:35:47 -0500
Message-ID: <>

Hi Guang
  You may have to do self join. I don't know about selectivity of 'column2> some_value' predicate and so, this rewritten version could be worse. Also, make sure that it uses index on column2 using explain plan..

create table tmp_table nologging as
select distinct column1 from table1 t1 where column2 > some_value and exists (

    select 1 from table1 t2 where t1.column1 = t2.column1 and t2.column2> some_value

     and t1.row_id != t2.row_id)

The Pythian Group

Guang Mei wrote:
> Oracle
> I have a sql that is slow to run:
> create table tmp_table nologging as
> (select column1 from table1 where column2 > some_value
> having count(*) > 1 group by column1);
> table1 is large, column2 has index on it. The count(*) value in the above query is quite big (typically in thousands) . Since I only need to check if the count(*) is large than 1, is there a way (say to use rownum somehow ) so that when count(*) gets up to 2, it stop counting (using stopkey), therefore I can have the above query run faster?
> Thanks.
> Guang
> --

Received on Wed Oct 22 2008 - 21:35:47 CDT

Original text of this message