If all you want to do is waste a lot of space, you don't need a fancy table at all. A single varchar(4000)
(filled up) should be enough:
SQL> create table foo(a varchar(4000));
Table created.
SQL> insert into foo select rpad(to_char(rownum), 4000, '*')
from dual connect by level <= 1024 ;
1024 rows created.
SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
PL/SQL procedure successfully completed.
SQL> select num_rows, blocks from user_tables where table_name = 'FOO';
NUM_ROWS BLOCKS
---------- ----------
1024 1126
Reason: there's no room to put another 4000 byte row in a 8k block given the block and row headers.
Or play with a small row but a stupid PCT_FREE
. For example:
SQL> create table foo (a varchar(128)) pctfree 99 ;
Table created.
SQL> insert into foo select rpad(to_char(rownum), 128, '*')
from dual connect by level <= 1024 ;
1024 rows created.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
PL/SQL procedure successfully completed.
SQL> select num_rows, blocks from user_tables where table_name = 'FOO';
NUM_ROWS BLOCKS
---------- ----------
1024 1126
Add an ordinary primary key and you can select whatever you want from there, with one row eating up about one block in SGA.
What you must really avoid is full-scanning that table (or anything that goes to direct reads and bypasses the SGA).
That being said, have you looked into simply reducing the SGA on your test system? If your volume of data (or the "width" of the dataset you can test) is X times smaller than production, start testing with X times less SGA and tweak (up or down) to get your IO subsystem to work. (You'll probably want more than X times less if your production SGA isn't large.)
I believe you'll have a better chance of having the SGA contents more representative of a real system (especially index/data block mix).
It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes).
Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y
) then the data is more likely to be in a contiguous set of blocks.
Best Answer
How about this:
PS. Not tested, as you didn't provide a script to create and populate the tables.