Today I Learned
  • Today I Learned
  • Database
    • Intro to Database Systems
      • Advanced SQL
      • Database Storage
        • Disk oriented Architecture
        • Storage Hierarchy
        • Disk-oriented DBMS
        • Why not use the OS?
        • Storage manager
        • Database Pages
        • Record IDs
        • Tuple layout
      • Database storage II
        • Log-structured file organization
        • Tuple storage
        • Postgres: numeric
        • Large value
        • External value storage
        • System catalogs
        • Observation
        • OLTP
        • OLAP
        • N-ARY STORAGE MODEL (NSM)
        • Decomposition storage model (DSM)
        • Tuple Identification
      • Buffer Pool & Memory management
        • Buffer pool organization
        • Buffer pool meta-data
        • Page Table vs. Page Directory
        • Allocation Polocies
        • Buffer Pool Optimizations
          • Multiple Buffer Pools
          • Pre-Fetching
          • Scan Sharing
        • Buffer Pool Bypass
        • OS Page Cache
        • Buffer Replacement Policies
        • Least-Recently Used
        • Clock
        • Problems
        • Better Policies: LRU-K
        • Better Policies: Localization
        • Better Policies: Priority Hints
        • Dirty Pages
        • Background Writing
      • Hash Table
        • Hash tables
          • Chain hashing
          • Extendible hashing
          • Linear hashing
      • Tree indexes I
        • Table indexes
        • B+ Tree
          • Properties
          • Nodes
        • B-Tree vs. B+Tree
        • Cluster indexes
        • Selection conditions
        • Node size
        • Merge threshold
        • Non-Unique indexes
        • Intra-node search
        • Prefix compression
        • Suffix truncation
        • Bulk insert
        • Pointer swizziling
      • Tree Indexes II
        • B+Tree: Duplicate Keys
        • Implicit Indexes
        • Partial Indexes
        • Covering Indexes
        • Index Include Columns
        • Functional/Expression Indexes
        • Observation
        • Trie Index
          • Trie Index Properties
          • Trie key span
          • Radix Tree
          • Observation
        • Inverted Index
        • Query Types
        • Design decisions
      • Multi-Threaded Index Concurrency Control
        • Locks vs. Latches
        • Latch modes
        • Latch implementations
        • Hash table latching
        • B+ Tree concurrency control
        • Latch crabbing/coupling
        • Leaf node scans
        • Page 5
      • Sorting & Aggregations
        • External Merge Sort
          • 2-Way External Merge Sort
        • Using B+ Trees For Sorting
          • Case #1 - Clustered B+ Tree
          • Case #2 - Unclustered B+ Tree
        • Aggragations
          • Sorting aggregation
          • Alternatives to sorting
          • Hashing aggregate
  • Back-end
    • Docker
      • Basic cmd
    • Network
      • The internet
        • Protocol Stacks and Packets
        • Networking Infrastructure
        • Internet Infrastructure
        • The Internet Routing Hierarchy
        • Application Protocols: HTTP and the World Wide Web
        • Transmission Control Protocol
        • Internet Protocol
    • Programming Languages
      • Compiled vs Interpreted
  • Personal
    • Reflections on the Tiny Things I Learned in
      • 2023
Powered by GitBook
On this page
  1. Database
  2. Intro to Database Systems
  3. Buffer Pool & Memory management
  4. Buffer Pool Optimizations

Scan Sharing

Queries can reuse data retrieved from storage or operator computations

-> This is different from result caching

Allow multiple queries to attach to a single cursor tha scans a table

  • Queries do not have to be exactly the same

  • Can also share intermediate results

If a query starts a scan and if there one already doing this, then the DBMS will attach to the second query's cursor

-> The DBMS keeps track of where the second query joined with the first so that it can finish the scan when it reaches the end of the data structure.

Fully supported in IBM DB2 and MSSQL.

Oracle only supports cursor sharing for identical queries

PreviousPre-FetchingNextBuffer Pool Bypass

Last updated 3 years ago