Skip to content

Mar 28: Storage, GP2 Scripts

Learning Objectives

After today's class, you should be able to:

  • Summarize how tables and indexes are physically stored.
  • Explain the contents of each script file required for GP2.

Lesson Outline

Data Storage [20 min]

Highlights from Chapter 5
  • 5.1 Storage media

    • Data stored on disks, read in as blocks
    • Blocks are usually row-oriented
    • Column-oriented is sometimes faster
  • 5.2 Table structures

    • Tables rows are usually stored unordered
    • Tables can be sorted, hashed, or clustered
  • 5.3 Single-level indexes

    • Index file store values and pointers
    • Selectivity – whether to use an index
  • 5.4 Multi-level indexes

    • Large indexes stored in a tree structure
    • B+trees are a balanced multi-level index
  • 5.5 Other indexes

    • Hash indexes good for equality queries
    • Bitmap indexes for few distinct values
    • Logical indexes store primary key values
    • Function indexes store function results
  • 5.6 Tablespaces and partitions

    • Different tables can be stored on different disks
    • The same table can be stored using multiple disks
  • 5.7 Physical design

    • Physical design is independent of local design
    • CREATE INDEX and EXPLAIN statements
    • Physical design process (query profiling)

Lecture / Demo [35 min]

Working Time [20 min]

  • Create stubs for each of the GP2 files
    • Each group member create one file
    • git add and git commit the file
    • Make sure everyone can git pull

Your To-Do List

  • Finish reading zyBook sections 5.15.7
  • Finish working on GP2 (due Monday)