Re: Spooky SQL

From: (wrong string) érôme Villafruela <jvillafruela_at_bigfoot.com>
Date: Sun, 23 May 1999 16:29:56 GMT
Message-ID: <37482820.3023347_at_news.directprovider.net>


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

>Hello,
>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
>SELECT PRODUCT_ID, PRODUCT_DESCRIPTION, SUM(QUANTITY)
>FROM ORDER_DETAILS
>GROUP BY PRODUCT_ID, PRODUCT_DESCRIPTION
>ORDER BY 3;
>
>The above will return all the products and quantity sold. How do I
>limit it to the top five best sellers.
>Thanks in advance.
>RNK.
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