SQL statement order of precedence, please advise.

Question

SQL statement order of precedence, please advise.

liorde

SSC Enthusiast

Points: 103
More actions
August 17, 2005 at 1:27 am

#88623

Hello all,
Since I am a newbie in the SQL area in general, I would like to ask something here which I believe has to do with SQL order of execution.
Please observe:
select * from
(
     select * from
     (
         select customer ,count (*) from subscriber
             where
             customer_ban between 1 and 1000000
             and
             customer not in (161,3)
             and sub_stat = 'A'
             and DUMMY_SUB is null
             group by customer having count(*) > 2
     ) where rownum < 401
&nbsp where rownum < 2
This query takes about forever to execute. I am not even sure it would return any result sometime...
If I were to ommit the upper select clause, the query takes about 4 seconds to perform.
What happens to the query then, when I add the upper 'select ... where rownum < 2'
Thanks in advance !
p.s. there is a winky next to the last 'where'. It should be a closing paranthesis.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Ok, I'll bite. You are 1st looking at approx 1 million records and grouping them. Next you are saying out of that I only want where rownum is less than 401 AND THEN out of that I really only want the first record?????

Basically I think you are trying to wander down the forest path with an Abrams tank vs. nice garden stroll.

What is you desired result that you are looking for and we can help you there. I honestly think that you are running this query 3 different times and each time is squeezing more life out of the system....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

liorde SSC Enthusiast Points: 103 More actions · Answer 2

Hello AJ,

Thank-you for the reply.

What I wish to do is this:

"select customer ,count (*) from subscriber

where customer between 1 and 40000000

and customer not in (161,3)

and sub_status = 'A' and DUMMY_SUB_IND is null

group by customer having count(*) > 2"

This inner query is suppose to retreive a customer that has at least 2 subscribers for it and is not numbered 161 or 3 but it's customer numbe is between 1 and 40000000.

The upper query

" select * from ( ... ) rownum < 401 "

is suppose to return to me no more than 400 rows.

And then the most upper select * from... is suppose to return only the first row from all of this, am I right ?

Thanks again.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

As your query is currently written you will get a "random" customer every time you do the following:

1. Give me approx 1 million customers

2. Ok now rerun #1 and give me 400

3. Ok now rerun 2 (which reruns 1) to give me a single record.

Instead why not research SELECT TOP ...... i.e SELECT TOP 400 FROM or SELECT TOP 1 FROM ... may give you better performance....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

liorde SSC Enthusiast Points: 103 More actions · Answer 4

Thank-you AJ,

I will try what you suggested. I was not aware that there is a functionality like this: 'TOP'.

I will let you know if it helped me.

Regards.