Process for physical database design based on transaction workload

• US 8,959,090 B1
• Filed: 09/24/2013
• Issued: 02/17/2015
• Est. Priority Date: 09/10/2010
• Status: Active Grant

• Current Assignee: Giovanni Sacco
• Original Assignee: Giovanni Sacco
• Inventors: Sacco, Giovanni
• Primary Examiner(s): Corrielus, Jean M
• Application Number:
  US14/035,698
• Time in Patent Office: 511 Days
• Field of Search: 707/737, 707/615, 707/969, 707/970, 718/103
• US Class Current: 707/737
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/275 Synchronous replication

Abstract

A process is disclosed for using the computer for physical database design, which is based on transaction workload, and minimizes the cost of retrieval and modification for a set of transactions on a database.

Claims

1. A method of using a computer system for physical database design, said method synthesizing a physical schema for a relational or object-relational logical schema in a database, said synthesized physical schema is obtained by creating files and assigning attributes to said files, said method also including:

a) vertical partitioning, said vertical partitioning allocating attributes of a single relation to two or more subrelations, each represented by a file, said attribute allocation allowing a replication of one or more attributes;

b) attribute replication, said replication involving attributes of a same relation replicated in different partitions of said relation, or attributes belonging to different relations;

c) horizontal partitioning, said horizontal partitioning allocating the records of a single relation to two or more subrelations, each represented by a file, according to a criterion based on a value of one or more attributes of said relation; and

d) encoding of one or more attributes of a relation, said encoding representing values of said attributes by system-generated codes, and using an external lookup table including said system-generated codes and said values to find the value corresponding to a specific system-generated code, where said system-generated codes are primary keys of said lookup table, said lookup table is shared by attributes with a same underlying domain.

2. The method of claim 1, said method further providing data allocation transparency in a database system;

and using the computer system to translate a transaction from a logical level to the appropriate files and attributes at the synthesized physical level.

3. The method of claim 2, said method further providing replicated data coherence by updating all copies of replicated data upon modification of said data.

4. The method of claim 2, said method further providing data coherence for encoded attributes by inserting and deleting values from lookup tables, said insertion also creating a new code for the inserted value.

5. The method of claim 1 wherein said computer system is used to synthesize a physical schema that minimizes the access cost for a set of transactions, said access cost being a weighted combination of parameters including response time, throughput, and transaction priority.