minte9

LearnRemember

Context

Revenew by year

Sales division needed to break down revenew by year. In this way, they could track recently active customers. They decided to add a series of new columns, each indicating the year it covered.

 
CREATE TABLE Customers (
    customer_id NUMBER(9) PRIMARY KEY,
    contact_info VARCHAR(255),
    business_type VARCHAR(20),
    revenue NUMBER(9,2)
);  

ALTER TABLE Customers ADD (<span class='keyword_code'>revenue2002</span> NUMBER(9,2));
ALTER TABLE Customers ADD (<span class='keyword_code'>revenue2003</span> NUMBER(9,2));
ALTER TABLE Customers ADD (<span class='keyword_code'>revenue2004</span> NUMBER(9,2));    

Maintenance

Each year, they needed to add one more column. A database administrator was responsible for managing Oracle’s tablespaces. So each year, they had to have a series of meetings, schedule a data migration. Ultimately they wasted a lot of time and money.

Performance

Performance{degrades for any database query as the volume of data goes up. Using indexes intelligently helps, but nevertheless the tables grow.

Antipattern

Clone Tables or Columns

In the television series Star Trek, tribbles are small furry animals kept as pets. Very appealing at first, but soon they reveal their tendency to reproduce out of control. Managing the overpopulation of tribbles becomes a serious problem.

Fewer rows

Quering a table with few rows is quicker than querying a table with many rows. This leads to a common error - we make every table with fewer rows, no matter what.

Errors

You discover that some 2010 bugs were entered in the Bugs_2009 by mistake. There’s no way to limit the data relative to the name of its table automatically. But you can declare a CHECK constraint in each of your tables.

 
CREATE TABLE Bugs_2009 (
        -- other columns
  date_reported DATE <span class='keyword_code'>CHECK</span> (EXTRACT(YEAR FROM date_reported) = 2009)
);    

No foreign key

A foreign key must specify a single table. In this case the parent table is split into many.

Legitimate use

One good use of manually splitting tables is for archiving.

Solution

Partition and Normalize

There are better ways to improve performance if a table gets too large. These include horizontal/vertical partitioning, and using dependent tables.

Partitioning

Physically, the table is split. You can still execute SQL statements against the table as though it were whole. Partitioning is not an SQL standard. Nevertheless, some form of partitioning is now supported by major brand of database.

 
CREATE TABLE Bugs (
    bug_id SERIAL PRIMARY KEY,
    -- other columns
    date_reported DATE
) <span class='keyword_code'>PARTITION BY HASH</span> ( YEAR(date_reported) )
    <span class='keyword_code'>PARTITIONS 4</span>;    

Horizontal partitioning

Horizontal partitioning splits a table by rows. Vertical partitioning splits a table by columns. Splitting by columns is useful when some columns are bulky or seldom needed.

Variable size

BLOB and TEXT columns have variable size, and they {{may be very large. Many brands automatically store columns with these data types separately. This could lead to performance problems. This, if you're in the habit of retrieving all columns using the * wildcard.