Exemplo n.º 1
1
  /**
   * Compute a logical, reasonably efficient join on the specified tables. See project description
   * for hints on how this should be implemented.
   *
   * @param stats Statistics for each table involved in the join, referenced by base table names,
   *     not alias
   * @param filterSelectivities Selectivities of the filter predicates on each table in the join,
   *     referenced by table alias (if no alias, the base table name)
   * @param explain Indicates whether your code should explain its query plan or simply execute it
   * @return A Vector<LogicalJoinNode> that stores joins in the left-deep order in which they should
   *     be executed.
   * @throws ParsingException when stats or filter selectivities is missing a table in the join, or
   *     or when another internal error occurs
   */
  public Vector<LogicalJoinNode> orderJoins(
      HashMap<String, TableStats> stats,
      HashMap<String, Double> filterSelectivities,
      boolean explain)
      throws ParsingException {

    // See the project writeup for some hints as to how this function
    // should work.

    // some code goes here
    PlanCache pc = new PlanCache();
    for (int i = 1; i <= joins.size(); i++) {
      Set<Set<LogicalJoinNode>> subsets = enumerateSubsets(joins, i);
      for (Set<LogicalJoinNode> subset : subsets) {
        CostCard best = new CostCard();
        best.cost = Double.MAX_VALUE;
        for (LogicalJoinNode node : subset) {
          CostCard subCard =
              computeCostAndCardOfSubplan(stats, filterSelectivities, node, subset, best.cost, pc);
          if (subCard != null) {
            if (subCard.cost < best.cost) best = subCard;
            pc.addPlan(subset, best.cost, best.card, best.plan);
          }
        }
      }
    }
    if (explain) printJoins(joins, pc, stats, filterSelectivities);
    HashSet<LogicalJoinNode> joinSet = new HashSet<LogicalJoinNode>();
    for (int i = 0; i < joins.size(); i++) {
      joinSet.add(joins.get(i));
    }
    Vector<LogicalJoinNode> order = pc.getOrder(joinSet);
    if (order != null) return order;
    return joins;
  }
Exemplo n.º 2
0
  /**
   * Helper function to display a Swing window with a tree representation of the specified list of
   * joins. See {@link #orderJoins}, which may want to call this when the analyze flag is true.
   *
   * @param js the join plan to visualize
   * @param pc the PlanCache accumulated whild building the optimal plan
   * @param stats table statistics for base tables
   * @param selectivities the selectivities of the filters over each of the tables (where tables are
   *     indentified by their alias or name if no alias is given)
   */
  private void printJoins(
      Vector<LogicalJoinNode> js,
      PlanCache pc,
      HashMap<String, TableStats> stats,
      HashMap<String, Double> selectivities) {

    JFrame f = new JFrame("Join Plan for " + p.getQuery());

    // Set the default close operation for the window,
    // or else the program won't exit when clicking close button
    f.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);

    f.setVisible(true);

    f.setSize(300, 500);

    HashMap<String, DefaultMutableTreeNode> m = new HashMap<String, DefaultMutableTreeNode>();

    // int numTabs = 0;

    // int k;
    DefaultMutableTreeNode root = null, treetop = null;
    HashSet<LogicalJoinNode> pathSoFar = new HashSet<LogicalJoinNode>();
    boolean neither;

    System.out.println(js);
    for (LogicalJoinNode j : js) {
      pathSoFar.add(j);
      System.out.println("PATH SO FAR = " + pathSoFar);

      String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias));
      String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias));

      // Double c = pc.getCost(pathSoFar);
      neither = true;

      root =
          new DefaultMutableTreeNode(
              "Join "
                  + j
                  + " (Cost ="
                  + pc.getCost(pathSoFar)
                  + ", card = "
                  + pc.getCard(pathSoFar)
                  + ")");
      DefaultMutableTreeNode n = m.get(j.t1Alias);
      if (n == null) { // never seen this table before
        n =
            new DefaultMutableTreeNode(
                j.t1Alias
                    + " (Cost = "
                    + stats.get(table1Name).estimateScanCost()
                    + ", card = "
                    + stats.get(table1Name).estimateTableCardinality(selectivities.get(j.t1Alias))
                    + ")");
        root.add(n);
      } else {
        // make left child root n
        root.add(n);
        neither = false;
      }
      m.put(j.t1Alias, root);

      n = m.get(j.t2Alias);
      if (n == null) { // never seen this table before

        n =
            new DefaultMutableTreeNode(
                j.t2Alias == null
                    ? "Subplan"
                    : (j.t2Alias
                        + " (Cost = "
                        + stats.get(table2Name).estimateScanCost()
                        + ", card = "
                        + stats
                            .get(table2Name)
                            .estimateTableCardinality(selectivities.get(j.t2Alias))
                        + ")"));
        root.add(n);
      } else {
        // make right child root n
        root.add(n);
        neither = false;
      }
      m.put(j.t2Alias, root);

      // unless this table doesn't join with other tables,
      // all tables are accessed from root
      if (!neither) {
        for (String key : m.keySet()) {
          m.put(key, root);
        }
      }

      treetop = root;
    }

    JTree tree = new JTree(treetop);
    JScrollPane treeView = new JScrollPane(tree);

    tree.setShowsRootHandles(true);

    // Set the icon for leaf nodes.
    ImageIcon leafIcon = new ImageIcon("join.jpg");
    DefaultTreeCellRenderer renderer = new DefaultTreeCellRenderer();
    renderer.setOpenIcon(leafIcon);
    renderer.setClosedIcon(leafIcon);

    tree.setCellRenderer(renderer);

    f.setSize(300, 500);

    f.add(treeView);
    for (int i = 0; i < tree.getRowCount(); i++) {
      tree.expandRow(i);
    }

    if (js.size() == 0) {
      f.add(new JLabel("No joins in plan."));
    }

    f.pack();
  }