Testing, testing, testing

First of all, I want to thank everyone for participating in my little quiz — I was amazed at the number and creativity of responses.

Also, I realized that I didn’t really give you a good set of test data — shame on me.  My silly little table of Red Sox pitchers really didn’t cut the mustard.

Here are some commands I used to create a test table for this quiz:

SQL> create table account (
  2  account_id number(10) not null,
  3  parent_account_id number(10),
  4  account_name varchar2(30) not null,
  5  constraint account_pk primary key (account_id),
  6  constraint account_fk_parent foreign key (parent_account_id)
  7  references account (account_id)
  8* )
SQL> /

Table created.

SQL> create index account_fk_parent on account (parent_account_id);

Index created.

SQL> create index account_ik_name on account (account_name);

Index created.

SQL> insert into account values (1,null,’sys’);

1 row created.

SQL> insert into account
  2  select rownum+1, 1, account_name
  3  from
  4  (
  5  select lower(table_name) account_name
  6  from dba_tables where owner = ‘SYS’
  7  union all
  8  select lower(view_name) account_name
  9  from dba_views where owner = ‘SYS’
 10  );

2319 rows created.

SQL> insert into account
  2  select rownum+10000, a.account_id, lower(b.column_name)
  3  from account a, dba_tab_columns b
  4  where b.owner = ‘SYS’
  5  and lower(b.table_name) = a.account_name;

24833 rows created.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’ACCOUNT’,cascade=>true);

PL/SQL procedure successfully completed.

Answers from my staff coming up in the next post — I’ll include execution statistics so you can compare against them.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder