Order status transaction

Input

• w_id = const.
• d_id = rand [1..10]
• customer selected 60% via last name and 40% via id

customer selection stuff ... c_last und c_id

Start Transaction

GetCustomerByCId

select C_ID, C_FIRST, C_MIDDLE, C_LAST, C_BALANCE
from CUSTOMER
where C_W_ID = w_id
  and C_D_ID = d_id
  and C_ID = c_id
limit 1

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "CUSTOMER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["C_ID", "C_FIRST", "C_MIDDLE", "C_LAST", "C_BALANCE"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

GetCustomersByLastName

select C_ID, C_FIRST, C_MIDDLE, C_LAST, C_BALANCE
from CUSTOMER
where C_W_ID = w_id
  and C_D_ID = d_id
  and C_LAST = c_last
order by C_FIRST asc
limit 1

{
  "operators": {
    "load": {
      "type": "GetTable",
      "name": "CUSTOMER"
    },
    "select": {
      "type": "SimpleTableScan",
      "predicates": [
        {"type": "AND"},
        {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
        {"type": "AND"},
        {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
        {"type": "EQ", "in": 0, "f": "C_LAST", "vtype": 2 /*Str*/, "value": %(c_last)s}
      ]
    },
    "project": {
      "type": "ProjectionScan",
      "fields": ["C_ID", "C_FIRST", "C_MIDDLE", "C_LAST", "C_BALANCE"]
    },
    "order": {
      "type": "SortScan",
      "fields": ["C_FIRST"]
    }
  },
  "edges": [["load", "select"], ["select", "project"], ["project", "order"]]
}

getLastOrder

select O_ID, O_CARRIER_ID, O_ENTRY_D
from ORDERS
where O_W_ID = c_w_id
  and O_D_ID = c_d_id
  and O_C_ID = c_id
order by O_ID desc
limit 1

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDERS"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "O_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "O_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "O_C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["O_ID", "O_CARRIER_ID", "O_ENTRY_D"]
    },
    "order": {
       "type": "SortScan",
       "fields": ["O_ID"],
       "asc": false
    }
  },
  "edges": [["load", "select"], ["select", "project"], ["project", "order"]]
}

getOrderLines

select OL_SUPPLY_W_ID, OL_I_ID, OL_QUANTITY, OL_AMOUNT, OL_DELIVERY_D
from ORDER_LINE
where OL_W_ID = o_w_id
  and OL_D_ID = o_d_id
  and OL_O_ID = o_id
limit 1

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDER_LINE"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "OL_O_ID", "vtype": 0 /*Int*/, "value": %(o_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["OL_SUPPLY_W_ID", "OL_I_ID", "OL_QUANTITY", "OL_AMOUNT", "OL_DELIVERY_D"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

Commit

Output

TPC