Carlos Sierra's Tools and Tips

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;