Re: speed this up?

I was thinking the best way is to not create the temp table at all. Reminds me of the way SQL Server works... <shudder>

Rich

> 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

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