/**************************************************************************************** * @file QP_Formula.java * * @author John Miller * @version 0.9, Tue Oct 19 10:27:52 EDT 2010 */ import scala.math._ /************************************************************************ * Formulas for B+Trees (bp), Linear Hashing (lh) and Extendable Hashing (eh). */ trait QP_Formula { val s_b = 4096 val p = 40 val q = ceil (p / 2.) def log_2 (x: Double) = log (x) / log (2.) def log_p (x: Double) = log (x) / log (p) def log_q (x: Double) = log (x) / log (q) def h_min (n: Int) = ceil (log_p (n / (p - 1.))) def h_max (n: Int) = floor (log_q (n / (2. * (q - 1.)))) + 1 def bp (n: Int) = (h_min (n) + h_max (n)) / 2. + 1 def lh (n: Int) = 2. // 1. + alpha / 2. -- assumes alpha = 2 def eh (n: Int) = 2. def fb (s_z: Int) = floor (s_b / s_z.asInstanceOf [Double]) } // QP_Formula trait /************************************************************************ * Formulas for Scan Select: sigma (r) * @param t_r the number of tuples in relation r * @param s_r the size (in bytes) of each tuple in relation r * @param v_X_r the number of distinct X-values V(X, r) in relation r */ class QP_Scan_Select (val t_r: Int, s_r: Int, v_X_r: Int) extends QP_Formula { val fb_r = fb (s_r) val f_r = ceil (t_r / fb_r) def n_R = f_r def n_W = ceil (t_r / (v_X_r * fb_r)) } // QP_Scan_Select class /************************************************************************ * Formulas for Indexed Select (index on X): sigma (r) * @param t_r the number of tuples in relation r * @param s_r the size (in bytes) of each tuple in relation r * @param v_X_r the number of distinct X-values V(X, r) in relation r * @param unique whether the index is unique (true) or non-unique (false) */ class QP_Indexed_Select (t_r: Int, s_r: Int, v_X_r: Int, unique: Boolean = true) extends QP_Scan_Select (t_r, s_r, v_X_r) with QP_Formula { override def n_R = bp (v_X_r) + 1 + (if (unique) 0. else n_W) } // QP_Indexed_Select class /************************************************************************ * Formulas for Block Nested Loop Join (Y is PK): r join s * @param t_r the number of tuples in relation r * @param t_s the number of tuples in relation s * @param s_r the size (in bytes) of each tuple in relation r * @param s_s the size (in bytes) of each tuple in relation s * @param lossFrac the fraction of foreign key references that fail */ class QP_BNL_Join (val t_r: Int, val t_s: Int, s_r: Int, s_s: Int, lossFrac: Double = 1.) extends QP_Formula { val fb_r = fb (s_r) val fb_s = fb (s_s) val fb_j = fb (s_r + s_s) val f_r = ceil (t_r / fb_r) val f_s = ceil (t_s / fb_s) def n_R = min (f_r + f_r * f_s, f_s + f_s * f_r) def n_W = ceil (lossFrac * t_r / fb_j) } // QP_BNL_Join class /************************************************************************ * Formulas for Indexed Join (Y is PK, index on Y): r join s * @param t_r the number of tuples in relation r * @param t_s the number of tuples in relation s * @param s_r the size (in bytes) of each tuple in relation r * @param s_s the size (in bytes) of each tuple in relation s * @param lossFrac the fraction of foreign key references that fail */ class QP_Indexed_Join (t_r: Int, t_s: Int, s_r: Int, s_s: Int, lossFrac: Double = 1.) extends QP_BNL_Join (t_r, t_s, s_r, s_s, lossFrac) with QP_Formula { override def n_R = f_r + t_r * (bp (t_s) + 1) } // QP_Indexed_Join class /************************************************************************ * Formulas for Non-Unique Indexed Join (Y is PK, index on X): r join s * @param t_r the number of tuples in relation r * @param t_s the number of tuples in relation s * @param s_r the size (in bytes) of each tuple in relation r * @param s_s the size (in bytes) of each tuple in relation s * @param v_X_r the number of distinct X-values V(X, r) in relation r * @param lossFrac the fraction of foreign key references that fail */ class QP_NU_Indexed_Join (t_r: Int, t_s: Int, s_r: Int, s_s: Int, v_X_r: Int, lossFrac: Double = 1.) extends QP_BNL_Join (t_r, t_s, s_r, s_s, lossFrac) with QP_Formula { override def n_R = f_s + t_s * (bp (v_X_r) + 1 + ceil (t_r / (v_X_r * fb_r))) } // QP_NU_Indexed_Join class /************************************************************************ * Formulas for Sort-Based Project: pi (r) * @param t_r the number of tuples in relation r * @param s_r the size (in bytes) of each tuple in relation r * @param s_x the size (in bytes) of each projected tuple * @param uniqFrac the fraction of the tuples that are unique (not duplicated) */ class QP_Sort_Project (val t_r: Int, s_r: Int, s_x: Int, uniqFrac: Double = .5) extends QP_Formula { val fb_r = fb (s_r) val fb_x = fb (s_x) val f_r = ceil (t_r / fb_r) val v_X_r = floor (t_r * uniqFrac).asInstanceOf [Int] def n_R = ceil (2. * f_r * log_2 (f_r)) def n_W = ceil (v_X_r / fb_x) } // QP_Sort_Project class /************************************************************************ * Formulas for Indexed Project (index on X): pi (r) * @param t_r the number of tuples in relation r * @param s_r the size (in bytes) of each tuple in relation r * @param s_x the size (in bytes) of each projected tuple * @param uniqFrac the fraction of the tuples that are unique (not duplicated) */ class QP_Indexed_Project (t_r: Int, s_r: Int, s_x: Int, uniqFrac: Double = .5) extends QP_Sort_Project (t_r, s_r, s_x, uniqFrac) with QP_Formula { override def n_R = f_r + t_r * bp (v_X_r) override def n_W = v_X_r + ceil (v_X_r / fb_x) } // QP_Indexed_Project class /************************************************************************ * Test the formulas. */ object QP_FormulaTest extends Application { val ss = new QP_Scan_Select (30000, 400, 1000) println ("----------------------") println ("Scan_Select") println ("tuples t_r = " + ss.t_r) println ("blocks f_r = " + ss.f_r) println ("reads n_R = " + ss.n_R) println ("writes n_W = " + ss.n_W) val ins = new QP_Indexed_Select (30000, 400, 1000, false) println ("----------------------") println ("Indexed_Select - Non-Unique") println ("tuples t_r = " + ins.t_r) println ("blocks f_r = " + ins.f_r) println ("reads n_R = " + ins.n_R) println ("writes n_W = " + ins.n_W) val ius = new QP_Indexed_Select (30000, 400, 30000) println ("----------------------") println ("Indexed_Select - Unique") println ("tuples t_r = " + ius.t_r) println ("blocks f_r = " + ius.f_r) println ("reads n_R = " + ius.n_R) println ("writes n_W = " + ius.n_W) val nlj = new QP_BNL_Join (200000, 30000, 200, 400) println ("----------------------") println ("Block Nested Loop Join - ForeignKey (r) = PrimaryKey (s)") println ("tuples t_r = " + nlj.t_r) println ("blocks f_r = " + nlj.f_r) println ("tuples t_s = " + nlj.t_s) println ("blocks f_s = " + nlj.f_s) println ("reads n_R = " + nlj.n_R) println ("writes n_W = " + nlj.n_W) val ij = new QP_Indexed_Join (200000, 30000, 200, 400) println ("----------------------") println ("Indexed Join - ForeignKey (r) = PrimaryKey (s)") println ("tuples t_r = " + ij.t_r) println ("blocks f_r = " + ij.f_r) println ("tuples t_s = " + ij.t_s) println ("blocks f_s = " + ij.f_s) println ("reads n_R = " + ij.n_R) println ("writes n_W = " + ij.n_W) val nij = new QP_NU_Indexed_Join (200000, 30000, 200, 400, 30000) println ("----------------------") println ("Non-Unique Indexed Join - ForeignKey (r) = PrimaryKey (s)") println ("tuples t_r = " + nij.t_r) println ("blocks f_r = " + nij.f_r) println ("tuples t_s = " + nij.t_s) println ("blocks f_s = " + nij.f_s) println ("reads n_R = " + nij.n_R) println ("writes n_W = " + nij.n_W) val sp = new QP_Sort_Project (30000, 400, 100) println ("----------------------") println ("Sort-Based Project") println ("tuples t_r = " + sp.t_r) println ("blocks f_r = " + sp.f_r) println ("reads n_R = " + sp.n_R) println ("writes n_W = " + sp.n_W) val ip = new QP_Indexed_Project (30000, 400, 100) println ("----------------------") println ("Indexed Project") println ("tuples t_r = " + ip.t_r) println ("blocks f_r = " + ip.f_r) println ("reads n_R = " + ip.n_R) println ("writes n_W = " + ip.n_W) println ("----------------------") } // QP_FormulaTest object