Re: speed this up?
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 22 Oct 2008 21:45:42 -0500 (CDT)
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