Re: Spooky SQL

From: (wrong string) érôme Villafruela <>
Date: Sun, 23 May 1999 16:29:56 GMT
Message-ID: <>

On Fri, 21 May 1999 12:35:13 +0100, Oracle Developer <> wrote:

>I am trying trying to write a select statement that will return five top
>selling products from a database table. The base table is the
>ORDER_DETAILS table which includes the product identity and the quantity
>sold. So I want to
>The above will return all the products and quantity sold. How do I
>limit it to the top five best sellers.
>Thanks in advance.
If you've a large number of products you can optimize this query at the cost of denormalizing a bit the DB :

  1. Create a table PRODUCT_SALES summarizing the sales by product : columns : PRODUCT_ID, QTY_SOLD = total quantity sold for that product. index on qty_sold This table can be populated by triggers on order_details or by a batch .
  2. use an optimizer hint to ask Oracle to read the index in descending order : SELECT /*+ index_desc (PRODUCT_SALES INDEX_QTY_SOLD) */ * FROM PRODUCT_SALES WHERE QTY_SOLD>0 AND rownum<6 (I'm not sure of the syntax, I've not the manuals at hand)

I was told this trick by an instructor during a tuning class : on a 700 000 rows table the query should take 2 or 3 seconds

Hope this helps,

Jérôme Villafruela
Grenoble - FRANCE
Please forgive any spelling mistake, english is not my native language.
Received on Sun May 23 1999 - 18:29:56 CEST

Original text of this message