Benchmark envolvendo junções de tabelas para SGBDs Relacionais

Os testes na máquina CPU1 Pentium 4, 1.7 Ghz envolveram Firebird, Postgres e MySql e na máquina CPU2 Pentium 4, 2.0 Ghz os três primeiros mais Oracle 9i. Os testes com Oracle 8.0.5 e Oracle 7 não são comparáveis com os demais por terem sido feitos em máquinas substancialmente mais lentas.

As máquinas dos testes possuem as seguintes características:

nome	linhas	colunas
customer	650	17
grape_variety	21	2
inventory	1049	6
items	7780	7
orders	2254	6
region	9	4
wine	1048	6
winery	300	6
wine_variety	1553	3

   -- joins all tables in winestore DB             Celio G. Nov 2002
   select count(*) from
   wine w, inventory i, region r, grape_variety gv,
   wine_variety wv, winery wy, customer c, items it, orders o
1  where i.wine_id= w.wine_id
2  and w.winery_id = wy.winery_id
3  and wy.region_id = r. region_id
4  and wv.wine_id = w.wine_id
5  and wv.variety_id= gv.variety_id
6  and it.cust_id= c.cust_id
7  and it.order_id = o.order_id
8  and it.wine_id = w.wine_id
9  and o.cust_id= c.cust_id

Esquema da Base de Dados Winestore

-- create_tables.sql            BD Winestore
-- versão Postgres  Obs: bytea equivale a blob

CREATE TABLE users (
  cust_id int DEFAULT '0' NOT NULL,
  user_name varchar(50) DEFAULT '' NOT NULL,
  password varchar(15) DEFAULT '' NOT NULL,
  PRIMARY KEY (user_name)
);
   CREATE INDEX password on users(password);

CREATE TABLE customer (
  cust_id  serial,
  surname varchar(50) DEFAULT '' NOT NULL,
  firstname varchar(50) DEFAULT '' NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) DEFAULT '' NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) DEFAULT '' NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20),
  phone varchar(15),
  fax varchar(15),
  email varchar(30) DEFAULT '' NOT NULL,
  birth_date date DEFAULT '0000-01-01' NOT NULL,
  salary int DEFAULT '0' NOT NULL,
  PRIMARY KEY (cust_id)
);

  CREATE INDEX fullname on customer(surname,firstname);

CREATE TABLE grape_variety (
  variety_id serial,
  variety varchar(50) DEFAULT '' NOT NULL,
  PRIMARY KEY (variety_id)
);

  CREATE INDEX var on grape_variety(variety);

CREATE TABLE inventory (
  wine_id int DEFAULT '0' NOT NULL,
  inventory_id int DEFAULT '0' NOT NULL,
  on_hand int DEFAULT '0' NOT NULL,
  cost numeric(5,2) DEFAULT '0.00' NOT NULL,
  case_cost numeric(5,2) DEFAULT '0.00' NOT NULL,
  date_added timestamp,
  PRIMARY KEY (wine_id,inventory_id)
);

CREATE TABLE items (
  cust_id int DEFAULT '0' NOT NULL,
  order_id int DEFAULT '0' NOT NULL,
  item_id int DEFAULT '1' NOT NULL,
  wine_id int DEFAULT '0' NOT NULL,
  qty int,
  price numeric(5,2),
  date timestamp,
  PRIMARY KEY (cust_id,order_id,item_id)
);

CREATE TABLE orders (
  cust_id int DEFAULT '0' NOT NULL,
  order_id int DEFAULT '0' NOT NULL,
  date timestamp,
  discount numeric(3,1) DEFAULT '0.0',
  delivery numeric(4,2) DEFAULT '0.00',
  note varchar(120),
  PRIMARY KEY (cust_id,order_id)
);

CREATE TABLE region (
  region_id serial,
  region_name varchar(100) DEFAULT '' NOT NULL,
  description bytea,
  map bytea,
  PRIMARY KEY (region_id)
);

  CREATE INDEX region_name on region(region_name);

CREATE TABLE wine (
  wine_id serial,
  wine_name varchar(50) DEFAULT '' NOT NULL,
  type varchar(10) DEFAULT '' NOT NULL,
  year int DEFAULT '0' NOT NULL,
  winery_id int DEFAULT '0' NOT NULL,
  description bytea,
  PRIMARY KEY (wine_id)
);

  CREATE INDEX wine_name on wine(wine_name);
  CREATE INDEX winery_id on wine(winery_id);

CREATE TABLE wine_variety (
  wine_id int DEFAULT '0' NOT NULL,
  variety_id int DEFAULT '0' NOT NULL,
  id int DEFAULT '0' NOT NULL,
  PRIMARY KEY (wine_id,variety_id)
);

  CREATE INDEX wine_id on wine_variety(wine_id,variety_id);

CREATE TABLE winery (
  winery_id serial,
  winery_name varchar(100) DEFAULT '' NOT NULL,
  region_id int DEFAULT '0' NOT NULL,
  description bytea,
  phone varchar(15),
  fax varchar(15),
  PRIMARY KEY (winery_id)
);

  CREATE INDEX winery_name on winery(winery_name);
  CREATE INDEX region_id on winery(region_id);