Rough order of magnitude for rows/second

From: sol beach <sol.beach_at_gmail.com>
Date: Thu, 30 Dec 2004 18:40:19 -0800
Message-ID: <40a16b3604123018402b007966@mail.gmail.com>

Yes, I realize "it depends" on many factors. Assume sufficient CPU, RAM & I/O exist; a lightly loaded OLTP system For the query below what is the rough order of magnitude for rows/second being read

        1
      10
     100

    1000
   10000
  100000
 1000000
10000000

SELECT d.product_id id,

       d.name data,

       NULL url,

       COUNT(DISTINCT li.customer_id) customers,

       COUNT(DISTINCT li.order_id) orders,

       SUM(li.price) revenue,

       SUM(li.shipping) shipping,

       SUM(li.qty) products,

       DECODE(COUNT(DISTINCT li.order_id), 0, 0, SUM(li.price)/COUNT(DISTINCT li.order_id)) avg_selling_price,

       SUM(li.discount) discount

FROM DW_2000000010525.line_item li,

       DW_2000000010525.product d

WHERE li.date_created >= TO_DATE('2004-12-23','YYYY-MM-DD')

AND li.date_created < TO_DATE('2004-12-24','YYYY-MM-DD')

AND li.product_id = d.product_id

AND li.prod_category_id = 3611326867412

GROUP BY d.name,

       d.product_id,

       NULL ORDER BY ORDERS DESC,        d.product_id

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 30 2004 - 20:35:39 CST