Query Opt
  Algorithms for SPJ queries
    Sigma
     =PK -> bin search, hash search
     no PK: most likely scan (read all rows) 
    Project on non-key: in general, SELECT DISTINCT==GROUP BY==Pi, 
      large n: sort records in general,
      small n: array or hash table indexed by group by key
       many values-->sorting
       few values: hashing, or an array
  ->Join
    R join S on K==R join_K S   
    2 nested for loops->O(n^2) sort algorithms, -> nested loop join
     for r1 in R
       for r2 in S
          if r1.k=r2.k then write result table r1+r2
     O(n^2)     
    Optimized versions:      
     
     if R,S have to be sorted then sort-merge join
     if R,S are already sorted or you have an index then no sorting needed. Therefore, only merge join
     detail:
      sort R, sort S->merge them-> sort (merge sort), 
 
     for loop on R, index on S -> indexed join. Therefore, O(n log n)
     can we maintain tables sorted?? NO..because of transactions working in RAM mostly
     that is why we have indexes..what is an index? 
       algorithms: it is a data structure
       theory: they are projections on the key 
     issue--> inconsistency (remember ACID)
     last case, both tables have index on the join column-->rare
     =>PK/FK most common
       PK/PK rare in OLTP...more common in analytics
       FK/FK does not make sense->ER design? many to many relationship==>middle table
 
Heuristics
  pipelining: 2+ operations a the same time when accessing disk
  pushing: sigma, pi; push them in the tree
            push sigma is much more common & useful.
  vanilla flavor: sigma first, join last          
                  pi tends to be last

SPJ evaluation 
what kind of tree is used to eval queries? (a+b)*(c+d)==mul(add(a,b),(add(c,d))
 binary tree: internal nodes== relational operators
              leaves==tables/relations
 R U S= binary
 R X S= binary
 R join S=binary
 sigma(R)=unary
 pi(R)=unary              
 
infix notation R*S a+b==add(a,b)
 ==>  not good for evaluation
functional notation
 pi(sigma(join(R,S),B=1),A) ==> tree
join: binary operator
pi,sigma: unary operators

C++:
 parser of SELECT
 binary files, in blocks


R Join_B S==>pi_B(R)   pi_B(S)??  
T1 Join A T2
intersectioon opf the key  


T1
A

3
4
6
2
8
1

T2
A
20
4
5
6
8


pi_B(R)
pi_B(S)
inefficient: pi second
efficient: pi first

caveat: create a temp table wirth different structure-->lose indexes


efficient: select B=0 R 50%, S 50% R Join S 25%!! 4 X faster, select first, join second
inefficeint join fist, sigma second
  
R
A B
1 0
2 1
3 0
4 1
5 1

S

B C
0 10
1 100