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.

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,
 ROUND(ABS(DBMS_RANDOM.NORMAL) * 100) quantity_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: Logo

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: