Query running problem

[neskin]

Hi there,
Is anyone tell me why my query too slow????????????????
unix_to_date is user-defined function .
la_max_timeand start_time are unix format - number

select a.nmt_id,a.la_max_date,a.la_max,
sum(a.CurCNE),
sum(a.N70),
sum(a.N80),
sum(a.N90)
from

(select ne.nmt_id ,trunc(unix_to_date(la_max_time))la_max_date,ne.la_max,
(case when to_number(to_char((unix_to_date(la_max_time)),'hh24mi'))>=2300

or to_number(to_char((unix_to_date(la_max_time)),'hh24mi')) <=600 then 1 else 0 end )CurCNE,

(case when (la_max)/10>=70 then 1 else 0 end) N70,

(case when (la_max)/10>=80 then 1 else 0 end) N80,

(case when (la_max)/10>=90 then 1 else 0 end) N90

from ne
where track_id <>0 and
trunc(unix_to_date(la_max_time)) between to_date('1/01/2006','dd/mm/yyyy') and to_date('1/02/2006','dd/mm/yyyy') and
to_char(unix_to_date(la_max_time),'yyyymmddhh24') in
(select to_char(unix_to_date(start_time),'yyyymmddhh24')
from nc
where climate_type = 1 and nc.nmt_id = ne.nmt_id and
trunc(unix_to_date(la_max_time)) = trunc(unix_to_date(start_time))))a
group by a.nmt_id,a.la_max_date,a.la_max


regards and thanks
nat

 

[SantaMufasa]

Is anyone tell me why my query too slow?

What makes you believe that your query is "too slow", Nat?

Please answer these specific questions:

1) How many rows are in the NE table?
2) How many rows are in the NC table?
3) How many rows does your query return?
4) How long does your query take?
5) When was the last time that you gathered statistics on the NE and NC tables?
6) Are you aware that because of your WHERE-clause constructions, Oracle must do full-table scans on each of your tables?
7) What columns are indexed on the NE and NC tables?
8) How much faster much your query run to become "fast enough"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

Hi Mufasa,
I am new to Oracle
Let me now answer all your ?
1. NE table - 373678 rows
2. NC table - 66098 rows
3. Don't know i have always cancel the query
4 more then 20 min and have cancelled
5.don't know how to do
6.no can you explaine please
7.NE - nmt_id,la_max_time,track_id ;
NC - nmt_id,start_time,climate_type

What i am trying to do IN clause I want only rows from ne where the climate type is 1(hourly) and date and hour equal in NE and NC

Nat

 

[neskin]

I forget to tell you i anm using TOAD for Oracle 8.53

 

[djbjr]

If you look at the schema browser in toad at each table you can see the last time it was analyzed on the Stats/Size pane on the right side.

You can also analyze the tables by clicking on the calculator icon while highlighting the table name in the schema browser.