Re: speed this up?

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)
;

Cheers
Riyaj
The Pythian Group
blog: http://orainternals.wordpress.com

Guang Mei wrote:

Oracle 10.2.0.1.0

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
-- 
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l

Other related posts: