Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Creating a big Sales table

with 2 comments

I need a large Table to do some testing of SQLTXPLAIN in Exadata. Seeded SALES Table out of the SH sample schema is not big enough for the test I want to do. This SH.SALES tables has less than 1M rows and I need a few billion rows. So, I could just fabricate my own data or I could expand the SH.SALES table. I opted for the latter so I could join this big table with some dimension tables out of the same sample schema.

This is the script that I used. It is still running. I expect 31B rows out of it. Sharing here in case you need a big sales table.

CREATE TABLE sales_big AS
WITH
p AS (SELECT prod_id FROM sh.products WHERE ROWNUM <= 50), -- max 50
c AS (SELECT cust_id FROM sh.customers WHERE ROWNUM <= 5000), -- max 50000
t AS (SELECT time_id FROM sh.times WHERE ROWNUM <= 500), -- max 1000
h AS (SELECT channel_id FROM sh.channels WHERE ROWNUM <= 5), -- max 5
r AS (SELECT promo_id FROM sh.promotions WHERE ROWNUM <= 50) -- max 500
-- 50 * 5000 * 500 * 5 * 50 = 31,250,000,000
SELECT p.prod_id,
 c.cust_id,
 t.time_id,
 h.channel_id,
 r.promo_id,
 ROUND(ABS(DBMS_RANDOM.NORMAL) * 100) quantity_sold,
 ROUND(DBMS_RANDOM.VALUE(0, 1000) * ABS(DBMS_RANDOM.NORMAL), 2) amount_sold
 FROM p, c, t, h, r;

Written by Carlos Sierra

August 12, 2013 at 5:36 pm

Posted in Exadata, Testing

2 Responses

Subscribe to comments with RSS.

  1. Hi Carlos,

    just wondering why you chose dbms_random.normal instead of just …value

    Andrey Goryunov

    August 12, 2013 at 6:40 pm

    • Oh, because I can some skewed data. Most rows will have a small value on this column.

      Carlos Sierra

      August 12, 2013 at 8:15 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,603 other followers

%d bloggers like this: