Performance Tuning  «Prev  Next»

Lesson 2 House-O-Pets Database Schema
Objective Understand the schema used for the course project and its role in performance tuning exercises.

House-O-Pets Database Schema

Database Administrator Role
You are the database administrator for House-O-Pets.
The House-O-Pets company is an online retailer of pet products based in San Francisco. Its e-commerce site runs on an Oracle database that manages inventory, customers, and purchase transactions. As the new DBA, your role is to evaluate this system and recommend strategies for improving performance.

FactFile – Database

FactFile database schema with six core tables
The FactFile database consists of six core tables that form the foundation of the House-O-Pets system.
  1. PRODUCT_OBJ_TABLE – View table
  2. CUSTOMER_OBJ_TABLE – View table
  3. SALE_HEADER – View table
  4. PET_CARE_LOG_OBJ_TABLE – View table
  5. MONTHLY_SALES – View table
  6. PRODUCT_PHOTO_OBJ_TABLE – View table

All tables together can be explored here: Complete data tables.

Course Project Goals

Throughout the course, you will use the FactFile schema to practice performance-tuning techniques in Oracle. Exercises include:
  1. Create an index-organized table to optimize row retrieval.
  2. Build a materialized view to improve query response time for analytical reporting.
  3. Design descending and function-based indexes to enhance search performance.
  4. Explore Advanced Queueing to manage resource-intensive processes, such as credit checks.
If you have access to Oracle, you will be encouraged to run these exercises directly. Otherwise, simulation prompts will guide you through problem-solving steps in a controlled environment. In the next module, you will begin improving database performance by examining optimizers and query execution strategies.

SEMrush Software 2 SEMrush Banner 2