"FriendFeed" Model Schema-Less Data Storage

Modern application developers face a challenge ill-suited to traditional RDBMS'. The data collected by applications is a moving target, with additional data points added often as applications mature. Also, many applications aggregate data from multiple sources where data points captured may differ or change beyond the control the application developer. Traditional RDBMS and the tools and checks built into those tools prefer static schemas. For example, Netbeans has a wizard to create Java entity beans from tables, but no simple way to update the entity beans when the schema changes. While creating the bean can take 5 minutes, adding a column takes hours. What's an architect to do?

One solution is to stuff an entire record into a LOB column in a traditional RDBMS (or whatever the equivalent of the LOB is in the particular RDBMS). This, however, makes indexing difficult.

Oracle has built in libraries to allow indexing and searching of XML data in BLOB and CLOB columns, but the performance is poor (though better than extracting the whole LOB and parsing it in, say, Java). Columns for indexing could be added to the LOB table, however if additional indexes are needed at a further date, the table schema must be altered.

The solution is to the indexing problem is to create separate tables for each index. Details of how this was accomplished at FriendFeed can be found here.

Note also, that if value comparison searches are not needed, a persistent hash map like Project Voldemort can be used instead.

Database Partitioning

Partitioning

What is it?
Splitting storage of data based on a key into different tables. End users of data don't generally "see" the partitioning, but do receive performance benefits. The key should be a value that is always available, such as user id.

Oracle Partitioning
Oracle partitioning creates sub tables for a given table and is defined when the table is created. Records are put into sub tables based on a key defined when the table is created.

CREATE TABLE mytable(
my_id NUMBER( 20 ) NOT NULL,
...
)
PARTITION BY HASH (MY_ID) -- Hash on MY_ID
PARTITIONS ???
STORE IN (tablespace_name)
/

Oracle partitioning limits:
- cannot span across hosts
- can't do an update to a row that would change which sub table the row is stored in-- if you want to move the record to another subtable, you must deleted the row and insert it again

Partitioning schemes
hash(column_name) (must understand Oracle hash function well to debug)
integer_column mod 10 (easy to debug, but can only span 2 or 5 sub tables)
integer_column mod 12 (harder to debug, but can be spanned across 6,4,3 or 2 sub tables)

Logical Partitioning/Sharding
It is possible to implement partitioning at the data access layer. Hibernate calls this sharding.

Mixing of sharding and partitioning
Temporal data

Uses of partitioning
Partitioning by geographic location (so user data is stored near their physical location)
partition by date

Oracle SQL predefined exceptions

If you've written PL/SQL stored procedures and functions, you may have use BEGIN/END blocks with EXCEPTION. Basically, this is kind of like try/catch in Java, and you can catch different exceptions and handle them. The syntax is like this:

BEGIN
 -- your code goes here.  If there isn't any, put NULL;
 NULL;
EXCEPTION
 WHEN NO_DATA_FOUND OR BLAHBLAH THEN
   ...
 WHEN OTHERS THEN
END;

To get a list of the constants ("predefined exceptions") that you can catch, look here.

Creating User Defined Types in Oracle

You can roll your own types in Oracle (and Postgres and mysql also) that include member variables and procedures though the procedures won't be portable. Here is how to create and use a type that has no procedures:

create or replace type MYUDT as object
(
  -- Author  : HELMUT
  -- Created : 8/20/2008 10:36:10 AM
  -- Purpose : example user defined type
 
  -- Attributes
  SILLY NUMBER,
  FOO VARCHAR2(200)
 
  -- Member functions and procedures
 
)

Create a table like normal:

-- Create table
create table DUMMYTABLE
(
  USERTYPECOLUMN MYUDT
)
;

You can then insert into the table like so:

INSERT INTO DUMMYTABLE (USERTYPECOLUMN) VALUES ( MYUDT(10,'Obamarama') );

and read from the table like so:

SELECT USERTYPECOLUMN.FOO FROM DUMMYTABLE WHERE USERTYPECOLUMN.SILLY > 5;

Passing XML stored in a String into Oracle Stored Procs

For stored procedures, you can pass XML data stored in a java.lang.String into a procedure via a CLOB type. To cofigure you Oracle callablestatement, use setObject on the java.lang.String. It's harder to pass in XMLType because it's an opaque type and requires some fiddling. In your stored procedure, just create an XMLType from the input CLOB via code like v_arg := XMLType(p_arg);

If your XML is not stored in a String, I don't know what to say since I tend to grab XML from a request and it comes out as a String.

Setting a SELECTs where Clause to Include Current Date/Time

You can use SYSDATE in the WHERE clause of a SELECT statement to only get certain fields. For example, if I only want entries from the last day, and the DATE/TIME column is DATADATETIME:

SELECT * FROM MYTABLE DATADATETIME < (SYSDATE) AND DATADATETIME > ((SYSDATE)-1)