UTPA STEM/CBI Courses/Databases/Partitioning

Course Title: Database Design and Implementation

Lecture Topic: Database Tuning

Instructor: Artem Chebotko

Institution: University of Texas - Pan American

Backwards Design

Course Objectives

Primary Objectives- By the next class period students will be able to:
- Talk about potential performance issues in large relational databases
- Identify situations when applying horizontal and vertical partitioning techniques can be used
- Explain advantages and disadvantages of relation partitioning
- Apply horizontal and vertical partitioning techniques to relations

Sub Objectives- The objectives will require that students are familiar with notions:
- Response time
- Throughput
- Denormalization
- Schema redesign

Difficulties
- Once students learned and applied database schema normalization techniques throughout the course, it may be confusing to learn that certain cases may require denormalization. Examples are:

Real-World Contexts- There are many ways that students can use this material in the real-world.
- You work as a database architect/administrator at ABC, Inc. For a few weeks, you have been getting increasingly many emails from the Customer Service department about slow performance of some database queries that retrieve information about customers of the company. You checked the Customer table in the database and realized that your company is doing very well: there are several million customers in the table. Customers are in general served by branches based on their geographical location. The Customer table can be partitioned both horizontally and vertically.
- Relational RDF databases use table Triples to store RDF triples. Horizontal partitioning of this table based on a predicate column value results in tables known as property relations. Vertical partitioning of table Triples can also be beneficial for column-based relational databases.

Model of Knowledge

Concept Map
- Normalization
- Denormalization
- Horizontal Partitioning
- Vertical Partitioning

Content Priorities
- Enduring Understanding
  - Estimate of response time and throughput for a particular schema design
  - Conditions that suggest horizontal or vertical partitioning
  - Application of horizontal and vertical partitioning
- Important to Do and Know
  - Normalization vs. denormalization
  - Cons and pros of partitioning
  - Cons and pros of normalization and denormalization
- Worth Being Familiar with
  - Cost estimation that involves indexes
  - RDF databases: partitioning based on instance or instance type
  - Redundancy techniques to improve performance

Assessment of Learning

Formative Assessment
- In Class (groups)
  - Example of table Triples from RDF databases and its horizontal partitioning into property tables based on values in the predicate column
  - Estimate cost of a query with one relational join over table Triple vs. property tables
- Homework (individual)
  - Repeat cost estimation for a different queries and different indices
  - Explore vertical partitioning of table Triples and explain a rational for such partitioning when a column-based relational database is used
Summative Assessment
- Quiz
- Exam

OBJECTIVE

By the next class period, students will be able to:

Talk about potential performance issues in large relational databases
Identify situations when to apply horizontal and vertical partitioning techniques
Explain advantages and disadvantages of relation partitioning
Apply horizontal and vertical partitioning techniques to relations

The objectives will require that students be able to:

Estimate cost of a given schema design for particular SQL queries
Choose an "optimal" query execution plan for a logical plan
Understand how different storage structures (heap, sorted file, integrated with an index file) work
Understand how indices work (B+ tree, hash)
Understand how algorithms for join, sorting, selection and projection work
Understand how pipelining works
Understand how index covering works

THE CHALLENGE

What is the most efficient database schema that can store RDF data?

GENERATE IDEAS

Start with table Triples. Choose indexes. Estimate cost of a query. How can we do better? Brainstorming ... Horizontal partitioning!

MULTIPLE PERSPECTIVES

Vertical partitioning and column-based stored. Native RDF stores.

RESEARCH & REVISE

Estimate cost of several queries between property tables vs. table Triple. Choose the best indexing scheme to support those queries.

TEST YOUR METTLE

Written and oral feedback from students

GO PUBLIC

Groups agree and present the results of their schema design, cost estimation, and indexing analysis. Discussion and conclusion.

What is a logical query plan?
How to choose a good execution plan for a query?
How to locate index entries in a B+ tree index? Cost?
How to locate index entries in a hash index? Cost?
How to retrieve tuples using an integrated B+ tree index? Cost?
How to retrieve tuples using an integrated hash index? Cost?
How to retrieve tuples using a clustered unintegrated B+ tree index? Cost?
How to retrieve tuples using a clustered unintegrated hash index? Cost?
How to retrieve tuples using an unclustered B+ tree index? Cost?
How to retrieve tuples using an unclustered hash index? Cost?
How to compute a join using the block nested-loops join algorithm? Cost?
How to compute a join using the indexed-based block nested-loops join algorithm? Cost?
When pipelining can be applied? Cost?
When pipelining cannot be applied?
When to choose a clustered index?
When to choose an unclustered index?
What is normalization?
What anomalies exist in relations that are not normalized?
How to evaluate a database schema design?
What are cons and pros of indices?

What is horizontal partitioning?
What is vertical partitioning?
When to apply horizontal partitioning?
When to apply vertical partitioning?
What are pros and cons of horizontal partitioning?
What are pros and cons of vertical partitioning?
What are RDF databases?
What are relational RDF databases?
Is horizontal partitioning used in RDF databases?
Is vertical partitioning used in RDF databases?
What is a query response time?
What is a database throughput?
How to improve a query response time?
How to improve a database throughput?
How can views be used to hide some of the complexity of horizontal partitioning?
How can views be used to hide some of the complexity of vertical partitioning?
What is a good indexing scheme for table Triple? Why?
What is a good indexing scheme for property tables? Why?
What are relationships among schema redesign, normalization, denormalization, and partitioning?
Can the same relation be a subject to both horizontal and vertical partitioning?