Process for physical database design based on transaction workload

• US 8,566,318 B1
• Filed: 09/10/2010
• Issued: 10/22/2013
• 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:
  US12/879,819
• Time in Patent Office: 1,138 Days
• Field of Search: 707/737, 707/769, 707/796, 707/803, 707/812, 707/615, 707/968, 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 comprising:

creating files in a physical schema for a relational or object-relational logical schema in a database and assigning data fields to said files, being-based on an actual workload for a set of transactions, said actual workload including a frequency of use of each transaction and an execution priority for each transaction, said execution priority being defined by a user and being used to prioritize the transactions for execution, said files and said data field assignment being derived in such a way as to minimize access costs for said set of transactions, each said access cost being a weighted combination of parameters including response time, throughput, and transaction priority, and a definition of indices,

said method further comprising;

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 being able to share by attributes with the same underlying domain.