理解range-predicate&pivoted partitioning in Database

• Overview

  From [1], Predication estimation is used increasingly in the Rdb optimizer to determine the cost and productivity of various index scans.

  When a particular query is executed, the conditions in the row selection expression, the “where” clause of a SQL statement, determine which rows will be selected.

  These conditions, or predicates, can be used to limit the parts of an index that are scanned to find data rows.

• predicate

  A predicate is a condition expression that evaluates to a boolean value, either true or false.

  Predicates can be used as follows:

  • In a SELECT statement’s WHERE clause or HAVING clause to determine which rows are relevant to a particular query.
  • In a JOIN operation’s ON clause to determine which rows are relevant to the join operation
  • In an UPDATE or DELETE statement’s WHERE clause, to detemine which rows are to be modified
  • In a WHERE CURRENT OF statement’s AND clause
  • In a CREATE TRIGGER statement’s WHEN clause to determine when to apply triggered action code

• Actian: Predicates in SQL

  Predicates are keywords that specify a relationship between two expression.

• Partition

  A partition is a division of a logical database or its constituent elements into distinct independent parts.

  It is popular in disributed database management systems, where each partition may be spread over multiple nodes, with users at the node performing local transactions on the partition.

  Current high-end RDMS provides for different criteria to split the database. They take a partitioning key and assign a partition based on certain criteria:

  • Range partitioning

    selects a partition by determining if the partitioning key is within a certain range

  • List partitioning

    a partition is assigned a list of values, if the partitioning key has one of these values, the partition is chosen

  • Composite partitioning

    allows for certain combinations of the above partitioning schemes

  • Round-robin partitioning

    the simplest strategy, it ensures uniform data distribution

  • Hash partitioning

    applies a hash function to some attribute that yields the partition number

• Pivot table

  A pivot table is a table of statistics that summarizes the data of a more extensive table.

  This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.

  Pivoting data is a rather common problem that comes in many different flavors. At its heart, the requirement is to transpose data from multiple rows into columns of a single row.

• References

1. Guide to Database Performance and Tuning: Predicate Estimation A feature of Oracle Rdb

2. InterSystems: Overview of Predicates

3. Microsoft SQL Docs: Predicates

4. Actian: Predicates in SQL

5. Pivot - Rows to Columns