https://moodle.cs.ubbcluj.ro/pluginfile.php/50353/mod_resource/content/1/DBMSs_Lecture7.pdf

https://moodle.cs.ubbcluj.ro/pluginfile.php/50545/mod_resource/content/1/DBMSs_Lecture8.pdf

https://moodle.cs.ubbcluj.ro/pluginfile.php/50546/mod_resource/content/1/DBMSs_Lecture9.pdf

1. RLV (row level versioning) - S4, P8
   1. Read committed snapshot isolation - S4, P9
2. Query governor, DBCC log, PIVOT/ UNPIVOT, OUTPUT - incep la S4, P17
3. Merge statement - S4, P21
4. Query fine-tuning
   1. Identify waits - S5, P3
   2. Correlate waits with queues - S5, P4
   3. Drill down to DB / file level - S5, P9
   4. Drill down to process level - S5, P12
   5. Indexes - S5, P13
   6. Tools to analyze query performance - S5, P16
   7. STATISTICS IO, STATISTICS TIME - S5, P19
   8. Graphical execution plan - S5, P26

Relational algebra

Queries

composed of relational operators:

• selection (𝜎)
  • selects a subset of records from a relation
• projection (𝜋)
  • eliminates certain columns from a relation
• join (⊗)
  • combines data from two relations
• cross-product (R1 × R2)
  • returns every record in R1 concatenated with every record in R2
• set-difference (R1 - R2)
  • returns records that belong to R1 and don’t belong to R2
• union (R1 ∪ R2)
  • returns all records in relations R1 and R2
• intersection (R1 ∩ R2)
  • returns records that belong to both R1 and R2
• grouping and aggregate operators (algebra extensions)
  • every operation returns a relation ⇒ operations can be composed
  • an operator can have several implementation algorithms

Optimizer

• input: SQL query Q
• output: an efficient execution plan for evaluating Q

Algorithms for operators

based on 3 techniques:

iteration

• examine iteratively:

  • all tuples in input relations

  or

  • data entries in indexes, provided they contain all the necessary fields (data entries are smaller than data records)

indexing