RE: Rough order of magnitude for rows/second

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Thu, 30 Dec 2004 20:09:29 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B562918@fiji.arraybp.com>

You are kidding, aren't you? This sounds like a homework assigment.

What does " ... sufficient CPU, RAM & I/O exist; a lightly loaded OLTP = system" mean? Is a 4.22Mhz 8086 a sufficient CPU for you?

However, in the spirit of DNA, the real answer is ...

--

Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----

From:	oracle-l-bounce_at_freelists.org on behalf of sol beach
Sent:	Thu 12/30/2004 7:40 PM
To:	Oracle-L Freelists
Cc:=09
Subject:	Rough order of magnitude for rows/second

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 >=3D TO_DATE('2004-12-23','YYYY-MM-DD')

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

AND li.product_id =3D d.product_id

AND li.prod_category_id =3D 3611326867412

GROUP BY d.name,

d.product_id,

NULL ORDER BY ORDERS DESC, d.product_id
--

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

This electronic message transmission is a PRIVATE communication which = contains
information which may be confidential or privileged. The information is = intended=20
to be for the use of the individual or entity named above. If you are = not the=20
intended recipient, please be aware that any disclosure, copying, = distribution=20
or use of the contents of this information is prohibited. Please notify = the
sender of the delivery error by replying to this message, or notify us = by
telephone (877-633-2436, ext. 0), and then delete it from your system.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 30 2004 - 21:04:43 CST