Creating a big Sales table
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;