Query Help Performance

[james777]

I am trying to write a query to get the results
that were marked with "--+" in one sql.
The table got nearly Million rows and tried with different
sql queries but hanging and the results are not coming out.
Can anyone tell me to write an efficient query.


Col1 Col2 Col3
1 18 1011--+
1 27 1011
2 1 1011--+
2 1 1301
2 1 1201
2 2 1011
2 3 1011
5 1 1011--+
5 49 1011
6 2 1011--+
6 3 1011
6 4 1011
6 5 1011
6 16 1011
6 36 1011
7 1 1203--+
7 1 1203
7 3 1203
7 3 1203
7 4 1203
7 8 1011
8 2 1011--+
8 16 1011
8 26 1011
11 17 1011--+
12 1 1011--+
12 8 1011
12 17 1011
13 1 1011
13 3 1011
13 10 1011
13 13 1011
14 1 1011--+
14 1 1011
14 1 1011
14 1 1011
14 2 1030
14 2 1011

Thanks
NewBee 2 Oracle

 

[kmfna]

Is there an index on the column you are trying to query off of?

good luck,
Kevin

 

[SantaMufasa]

James,

Kevin is correct...the solution to you problem reside with indexes. Since you did not post your query (and the query criteria are not obvious/intuitive), I cannot be specific about the indexes, but so long as you have the WHERE-clause columns in indexes, that should speed your query to near-instantaneous response time.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:13 (01Jul04) UTC (aka "GMT" and "Zulu"), 13:13 (01Jul04) Mountain Time)

 

[james777]

select/*+ FIRST_ROWS */ distinct a.col1,a.col2,a.col3
from dtab a
where a.col2 = ( select min(b.col2) from dtab b where a.col1 = b.col1)
and a.col3 = ( select min(c.col3) from dtab c where a.col1 = c.col1)

Million rows and Index on Col1

 

[carp]

You might try something like:

select col1, col2, col3
from  dtab 
where (col1, col2, col3) IN
(SELECT col1, min(col2), min(col3) from dtab
 GROUP BY col1);

You will lose two sorts as well as the correlated queries, and I think you will get the same results.

 

[dbtoo2001]

-- try analytics?

select /*+ ALL_ROWS */ col1,
min(col2) over (partition by col1 order by col2) as min_col2,
min(col3) over (partition by col1 order by col3) as min_col3
from dtab;

 

[kmfna]

woah dbtoo, very interesting. I've never used the over and partition commands. I'll have to check into that I bet there are a lot of good uses.

Kevin.

/*- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts" */