Assignment 3 Solution

Introduction to Database Systems

DataLab

CS, NTHU

Modified/Added Classes

• Parse– Lexer

– Parser

– queryData

• Algebra– ExplainPlan、ExplainScan

– TablePlan、ProductPlan、SelectPlan、 SortPlan、GroupByPlan、ProjectPlan

• Planner– BasicQueryPlanner

• An example of Experiment Results

2

Overview

3

createPlan()

Planner

eatKeyword()

QueryData()

BasicQueryPlanner

QueryData

queryCommand()

createQueryPlan()

Parser

Lexer

matchKeyword()

Modified/Added Classes

• Parse– Lexer

– Parser

– queryData

• Algebra– ExplainPlan、ExplainScan

– TablePlan、ProductPlan、SelectPlan、 SortPlan、GroupByPlan、ProjectPlan

• Planner– BasicQueryPlanner

• Examples of Experiment Results

4

Lexer

5

createPlan()

Planner

eatKeyword()

QueryData()

BasicQueryPlanner

QueryData

queryCommand()

createQueryPlan()

Parser

Lexer

matchKeyword()

Parse

• Lexer

– added “explain” in keywords.

6

Parser

7

createPlan()

Planner

eatKeyword()

QueryData()

BasicQueryPlanner

QueryData

queryCommand()

createQueryPlan()

Parser

Lexer

matchKeyword()

Parse

• Parser

– add isExplain

8

Parse

• Parser

– Parser returns SQL data

– In method “queryCommand()”

9

QueryData

10

createPlan()

Planner

eatKeyword()

QueryData()

BasicQueryPlanner

QueryData

queryCommand()

createQueryPlan()

Parser

Lexer

matchKeyword()

Parse

• QueryData

11

Parse

• QueryData

12

Modified/Added Classes

• Parse– Lexer

– Parser

– queryData

• Algebra– ExplainPlan、ExplainScan

– TablePlan、ProductPlan、SelectPlan、 SortPlan、GroupByPlan、ProjectPlan

• Planner– BasicQueryPlanner

• Examples of Experiment Results

13

14

ExplainPlan open()

SortPlan open() toString()

ProjectPlan open() toString()

SelectPlan open() toString()

ProductPlan open()

TablePlan open()

toString()

toString()

getVal()

ExplainScanSP/JDBC Connection

ExplainPlan

15

ExplainScan

• That the result shows once

16

ExplainScan

• Return the result of explain

17

ExplainScan

• Return the number of actual records

18

TablePlan

19

->TablePlan on (warehouse) (#blks=2, #recs=1)

ProductPlan

20

->ProductPlan (#blks=22, #recs=10)->TablePlan on (warehouse) (#blks=2, #recs=1)->TablePlan on (district) (#blks=2, #recs=10)

SelectPlan

21

->SelectPlan pred:(d_w_id=w_id) (#blks=22, #recs=10)->ProductPlan (#blks=22, #recs=10)

->TablePlan on (warehouse) (#blks=2, #recs=1)->TablePlan on (district) (#blks=2, #recs=10)

SortPlan

22

->SortPlan (#blks=2, #recs=10)->SelectPlan pred:(d_w_id=w_id) (#blks=22, #recs=10)

->ProductPlan (#blks=22, #recs=10)->TablePlan on (warehouse) (#blks=2, #recs=1)->TablePlan on (district) (#blks=2, #recs=10)

GroupByPlan

23

->GroupByPlan: (#blks=2, #recs=1)->SortPlan (#blks=2, #recs=10)

->SelectPlan pred:(d_w_id=w_id) (#blks=22, #recs=10)->ProductPlan (#blks=22, #recs=10)

->TablePlan on (warehouse) (#blks=2, #recs=1)->TablePlan on (district) (#blks=2, #recs=10)

ProjectPlan

24

->ProjectPlan (#blks=2, #recs=1)->GroupByPlan: (#blks=2, #recs=1)

->SortPlan (#blks=2, #recs=10)->SelectPlan pred:(d_w_id=w_id) (#blks=22, #recs=10)

->ProductPlan (#blks=22, #recs=10)->TablePlan on (warehouse) (#blks=2, #recs=1)->TablePlan on (district) (#blks=2, #recs=10)

Modified/Added Classes

• Parse– Lexer

– Parser

– queryData

• Algebra– ExplainPlan、ExplainScan

– TablePlan、ProductPlan、SelectPlan、 SortPlan、GroupByPlan、ProjectPlan

• Planner– BasicQueryPlanner

• Examples of Experiment Results

25

BasicQueryPlanner

26

createPlan()

Planner

eatKeyword()

QueryData()

BasicQueryPlanner

QueryData

queryCommand()

createQueryPlan()

Parser

Lexer

matchKeyword()

BasicQueryPlanner

27

Modified/Added Classes

• Parse– Lexer

– Parser

– queryData

• Algebra– ExplainPlan、ExplainScan

– TablePlan、ProductPlan、SelectPlan、 SortPlan、GroupByPlan、ProjectPlan

• Planner– BasicQueryPlanner

• Examples of Experiment Results

28

Examples of Experiment Results

29

Examples of Experiment Results

30

Examples of Experiment Results

31

Examples of Experiment Results

32

Examples of Experiment Results

33