RE: question about the query..

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 05 Sep 2001 07:48:15 -0700
Message-ID: <F001.0038354D.20010905075108@fatcity.com>

How many rows in the table?

Did you do an explain plan of this query?

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
[mailto:Srinivas_Madala_at_circuitcity.com] Sent: Wednesday, September 05, 2001 11:39 AM To: Multiple recipients of list ORACLE-L

Hi List
I have a question regarding a SQL query I am working on. I have a table named 'tmp_scores', with a column 'rank' and other columns. Rank varies from 1 to 12. I need to get the top 10 million records based on the rank.

I have used the logic behind the following query(to get top 4 salaried employees)

select ename,job,sal from emp e
where 4 > (select count(*) from emp

                      where e.sal<sal)

order by sal;

to get my task accomplished as below:

SELECT a.customer_id,
       a.first_name,
       a.last_name,
       a.carrier_route_code,
       a.unit_designator||'|'||a.street_pre_directional||'|'||
       a.primary_street_number||'|'||a.primary_street_name||'|'||
       a.street_post_directional||'|'||a.secondary_address_number||'|'||
       a.secondary_address_name "Street",
       city,
       a.state_code,
       a.postal_code,
       a.postal_code_extension,
       a.combined_score_demi_decil_rank

FROM tmp_stg_van_customer_details a
WHERE 10000000 > = (SELECT count(*) FROM

                      tmp_stg_van_customer_details
                      WHERE a.combined_score_demi_decil_rank <

combined_score_demi_decil_rank)
ORDER BY combined_score_demi_decil_rank;

I have issued the query and it has been running for the last three days without giving any error. I am not able to conclude what to do. Any help is highly appreciated.

Thanks in advance.
srinivas

--

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

Author:
INET: Srinivas_Madala_at_circuitcity.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

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

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------