bi-temporal database – first lesson

Milestone in PWM — similar. I was using only the validFrom/validTill columns.

I think this is all about accountably modifying historical record.

snapshot — For a given observation date like 31/12/1999, “where TxStart < observDate and observDate < TxEnd” would return a snapshot as of 31/12/1999.

A “Tx” or “Transaction” — means a data entry. TxStart/TxEnd marks the observation window or period of belief. If we observe the price on a day within this window, i.e. take a snapshot on that day, then all the data rows outside this window is filtered out.

Indexing for performance — an index on (TxStart, TxEnd) would speed up such “snapshot” queries.

Data error 1 — If a wrong price was entered but quickly corrected, and never affected any customer, then I doubt bi-temporal is designed for this. Therefore the “never-delete” is not so strict.

Data error 2 — However, if a customer was charged the wrong price, then “never-delete” principle holds. The wrong price was in force at that time, so it is valid history (though not valid by company policy). Valid history is never deleted and must be milestoned.

Example —

c#Reuters database IV

Database Question

Table E
int id
string empType
string email
string firstName
string lastName

CLUSTERED (id, empType)
UNIQUE (empType, id, firstName)
DUPLICATE (empType, firstName)

Table E has: 10,000,000,000 rows

Table E has: one row with firstName “bettlejuice”

Table E has: 1,000,000,000 rows with empType = ‘fulltime’

SELECT * FROM E WHERE email = ‘‘;

SELECT * FROM E WHERE id = 100 AND firstName = ‘beetlejuice’;

SELECT * FROM E WHERE empType = ‘fulltime’ AND firstName = ‘beetlejuice’;

SELECT * FROM E WHERE empType = ‘fulltime’ AND firstName = ‘beetlejuice’;

How many pages for leaf nodes does the above query read?

INSERT: Writes how many pages?

oracle tablespace striping

If an audit table or a trade capture table gets a lot of concurrent writes, you may want to stripe it across disks to increase parallelism and write performance. One way is to configure the table with multiple tablespaces, according to a friend. Each tablespace maps to a physical disk.

The same stripe technique also speeds up reading.


basic oracle instance tuning tips #Mithun

You can turn on tracing for a given session. All queries will be traced. You can also trace across all sessions.

Oracle provides dynamic performance stats in the form of so-called V$ views, but I think the trace files are more detailed.

Another common technique is to record timestamps

– client log – issuing query
– server log – receiving query
– server log – returning data
– client log – receiving data
– client log – sending out data to requester, which could be in the same or another process.

Latency is additive. Find the largest component.

simple snoop table to monitor DB access

 charp1 VARCHAR(16384) DEFAULT  ”  NULL,
 charv1 VARCHAR(16384) DEFAULT  ”  NULL,
 charp2 VARCHAR(16384) DEFAULT  ”  NULL,
 charv2 VARCHAR(16384) DEFAULT  ”  NULL,
 nump1 VARCHAR(99)     DEFAULT  ”  NULL,
 numv1 FLOAT           DEFAULT  0   NULL,
 datep1 VARCHAR(99)    DEFAULT  ”  NULL,

/* one way to use this table is to save multiple params when calling a proc
insert snoop(charp1,charv1,nump1,numv1,datep1,datev1,sproc)
      values(‘param1’,?, ‘param2’,?,  ‘param3’,?, ‘myProc’)
 sproc VARCHAR(99) DEFAULT  ”  NULL,
 remark VARCHAR(99) DEFAULT  ”  NULL