Re: speed this up?
- From: "Nigel Thomas" <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: guangmei@xxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Nov 2008 10:31:33 +0000
Just for the record, another approach is to use an analytic function to
avoid the self join.
select column1 from (
select row_number() over (partition by column_name) rn,
column1
from table1
where column2 > value
)
where rn = 2
Using rn=2 means that single rows get skipped, and only column1 values with
more than 1 row will make it through (once each).
Relative performance - depends... If there are not many duplicates or worse,
this could be the best way.
Regards Nigel
2008/10/22 Guang Mei <guangmei@xxxxxxxxx>
> 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
