Need ideas for "proof test"

Once again we are having a "friendly discussion" within my workgroup over the performance impact of key positioning and row insert sequence and the efficacy of a db reorg. My workgroup consists of 2 DBAs that work on DB2, and myself and another Oracle DBA. I and the DB2 guys claim none of these things make any difference on performance. The other Oracle DBA is adamant that it is important (from a performance standpoint) where within a row a key column is placed, and that regular re-orgs improve performance by sequencing the rows in key order. We've talked till we're blue in the face, but this guy is adamant.

I'd like to set up a test to settle this once and for all. I'm not asking anyone to do the work for me, but would appreciate some review of my plan.

I think the easiest way to set it up would be to dump the table in question to a flat file, sort the file by a NON-key field (to introduce some randomness to the order), then sqlldr it into an empty table of the same structure.

Next, run some unqalified SELECTS both with and without ORDER BY on the key field. Timing would be done by turning on a trace.

Next, reorg the table by doing an export/import.

Next, repeat the tests and timeing.

Am I overlooking anything? I want to make sure the test plan and results stand up to scrutiny.

And while I'm willing to do the work myself, if someone has some code or procedures laying around that they'd be willing to share . . . ;-) Received on Thu Feb 26 2004 - 11:10:51 CST