Composite Primary Key column order

clustered-primary-keydatabase-designmulti-tenantoracleprimary-key

How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition):

I have a database where I offer the same application to many users. I want to be perfectly sure that all tenants have separated data and that under no circumstances leaving out a WHERE (by user or persistence framework mistake) can show customer data to another customer. Also, I want to have as few maintenance as possible, so I do not want to have the same schema over and over again (because then, in order to make perfectly sure that all the environments are the same, one has neverending schema compare marathons).

My solution:

  • 1 data schema with column "tenant_id" in every table and every view.
  • For every customer a view VTxxx_tablename or VTxxx_viewname in the form "SELECT * FROM underlyingobject WHERE tenant_id = x WITH CHECK
    OPTION"     with Select/Insert/Update/Delete-grants.
  • n customer schemas with no data.
  • For every of the n tenants synonyms to their granted views in their no-data schemas.
  • All of the above generated (and re-generated) by script.

Result:

  • All tables have a PK of (tenant_id, id) (or: (id, tenant_id), this is the question)
  • All FKs obviously include the tenant_id column, therefore no mixing of customer data possible.

The data:

  • "normal" OLTP-application (everything normalized, many JOINs in the SELECTs)
  • tenant_id is currently from [1,2,3], but might eventually get into the range 1..100.
  • ID is filled by one sequence per table.

The access:

  • All access is though the views, therefore every SELECT has an implicit WHERE tenant_id = x
  • Many SELECTs have a WHERE clause with full PK (implicit tenant_id through the used VIEW plus extra id like in WHERE tenant_id = x AND id = y)

Right now I decided for PK-column order (tenant_id, id).

Pro:

  • when ID column not present, INDEX SKIP SCAN or INDEX RANGE SCAN is possible
  • As no Oracle EE is available, this is my "poor mans partitioning" (downside below)

Con:

  • assuming the the application is used the same amount by all tenants, the PK has to be reorganized pretty much / for every insert, as low tenant_id rows are added as well.

What would you do in this case and why:

  • PK (tenant_id, id)
  • PK (id, tenant_id)
  • own schema (with own tablespace) per tenant nevertheless (more admin work (schema compare), better speed, simpler SQLs)

I'm looking for Oracle related physical differences only (like SCANs, Index Update costs or more general read costs/bottlenecks against write costs/bottlenecks)

Thank you,
Blama

Best Answer

Store the tenant_id first. When you do this you can enable index key compression.

See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts.

In your case, you can do it like this:

create unique index mytable_idx on mytable(tenant_id,id) compress 1;

alter table mytable add constraint mytable_pk primary key(tenant_id, id);
Related Question