Tuning sql query with over partition clause : HELP !!
- From: "NGUYEN Philippe (Cetelem)" <philippe.nguyen@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 14:58:07 +0200
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Tuning sql query with over partition clause : HELP !!
