Tuning sql query with over partition clause : HELP !!

From: NGUYEN Philippe (Cetelem) <philippe.nguyen_at_cetelem.fr>
Date: Wed, 5 May 2004 14:58:07 +0200
Message-ID: <DCEE3C62548AFE48ADF88CBFDB8260A20508909B@NGMAIL3>

Hi List !
I have a very big problem since my query is hanging for a while. Description :

-Just 1 big table : historized_file wich is partitionned by range on
month column (eg 200202,200203...).

-There is a primary key on (id_file,month)
-We want to create a table wich containt only the most recent file
from a period of time (eg from 200301 to 200401)

The orginal query was :

create table histo2 tablespace etud2_md_tab as select * historized_file p
where p.month>200301 and p.mois<=200401 and p.month=( select max(mois) from historized_file h

                where p.id_file=h.id_file and h.month>200301 and

h.month<=200401)
;

I tried to enhance it by the following : create table histo2 nologging tablespace etud2_md_tab as select p.* from historized_file p
where p.month>=200302
and p.month<=200401
and p.month=( select max(month) over (partition by id_file)   from historized_file t
  where t.month>=200302 and t.month<=200401   and id_file=p.id_file
  and rownum=1);

Is this last query more effecient from a SQL point of view? The same query without create as select responded very quickly giving me the first rows of results but it took also long time for the rest... It still running now.

TIA
Philippe

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 05 2004 - 07:55:40 CDT