VoltDB first steps - schema

The first, and most critical, step for working with VoltDB is figuring out what table(s) will be partitioned, and how. So, the schema...

The case I'm looking at is for a hypothetical ticket booking agency, for very large events (rock bands playing large stadiums, etc.). There's three basic tables:

1. seat_info
2. booking_info
3. booking_seats

seat_info is a list of all of the seats, and whatever metadata is required.
booking_info is a list of the bookings, so far, and whatever metadata is required.
booking_seats relates seats to bookings, and events.

booking_seats is the table that's interesting; it will be the largest, and hottest, by far. The schema elements for booking_seats is below:

create table booking_seats (
  booking_id varchar(10) not null,
  seat_number smallint not null,
  seat_row int not null,
  event_id int not null,
  constraint booking_seats_unique_hash unique (seat_number, seat_row, event_id)
);

partition table booking_seats on column seat_row;

create index event_booking_hash_idx on booking_seats (event_id);

booking_id is a foreign key for booking_info.
seat_number and seat_row form a composite key into the seat_info table.
event_id will be a foreign key relationship "in the future".

seat_row is used for partitioning the booking_seats table.

The VoltDB user manual has an example of a flight booking schema that's been partitioned by flight_id, analogous to event_id in my example.  The use case here is different, and so the partitioning scheme needs to be different.

• event_id would cause all of the traffic for the ticket booking system would be directed to a single partition when tickets for that event were put on sale - which sort of defeats the purpose of partitioning the data in the first place.
• booking_id could be used for partitioning, but as it's not a part of the unique constraint, the constraint would be expensive to enforce.
• seat_number isn't a good choice for partitioning because buying multiple tickets is normal, and each transaction should be kept within a single partition, if possible.
• seat_row is my choice for partitioning, because most bookings tend to be in the same row, and because it's a part of the unique constraint that prevents seats being double-booked.

In theory booking_id, or even event_id, may make a sound choice for partitioning. For this example I really would like to have aggressive partitioning of the data to see what breaks stress VoltDB's capabilities.

This is a "not bad" schema; but the query for finding available seats is a bit ugly. I added the index on event_id, to optimise the query for available seats for a given event, but testing is really essential to see if it helps or hinders performance overall.

Empty an Oracle schema, leave an empty schema

What it says on the tin. Sufficient code analysis will reveal that some bizarre schema dependencies may not be destroyed by this; but experience will show that's not actually a problem :-)

The code lives as a gist over on github. A snapshot is below, for simplicity:

set echo off
set verify off
set serveroutput on size 100000

-- Hosted at http://lastinfinitetentacle.blogspot.com/2012/03/empty-oracle-schema-leave-empty-schema.html

-- Disable all contraints
BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

-- remove all objects
declare

cursor dropObjectsCusor is
select 'drop ' || object_type || ' ' || object_name as sqlDropStmt
from user_objects
where object_type <> 'TABLE' and object_type <> 'INDEX'
order by object_type;

cursor dropTablesCusor is
select 'truncate table ' || object_name as sqlTruncTbl,
'drop table ' || object_name || ' cascade constraints' as sqlDropTbl
from user_objects
where object_type = 'TABLE'
order by object_type;

begin

for ob in dropTablesCusor
loop
begin
  execute immediate ob.sqlTruncTbl;
exception when others then dbms_output.put_line('Could not truncate a table.');
end;
begin
  execute immediate ob.sqlDropTbl;
exception when others then dbms_output.put_line('Could not drop a table.');
end;
end loop;

for ob in dropObjectsCusor
loop
begin
  execute immediate ob.sqlDropStmt;
exception when others then dbms_output.put_line('Could not drop some object.');
end;
end loop;


end;
/
purge recyclebin;