CONCLUSIONES

Listing 2.1 contains the full SQL code of TPC-H query 19. Listing 2.1: TPC-H Query 19 select sum( l e x t e n d e d p r i c e ∗ ( 1 − l d i s c o u n t ) ) as r e v e n u e from l i n e i t e m , p a r t where ( p p a r t k e y = l p a r t k e y and p b r a n d = ’Brand#12’

and p c o n t a i n e r in (’SM CASE’, ’SM BOX’, ’SM PACK’, ’SM PKG’)

and l q u a n t i t y >= 1 and l q u a n t i t y <= 1 + 10

and p s i z e between 1 and 5

and l s h i p m o d e in (’AIR’, ’AIR REG’)

and l s h i p i n s t r u c t = ’DELIVER IN PERSON’

)

or

(

p p a r t k e y = l p a r t k e y

and p b r a n d = ’Brand#23’

and p c o n t a i n e r in (’MED BAG’, ’MED BOX’, ’MED PKG’, ’MED PACK’)

and l q u a n t i t y >= 10 and l q u a n t i t y <= 10 + 10

and p s i z e between 1 and 10

and l s h i p m o d e in (’AIR’, ’AIR REG’)

and l s h i p i n s t r u c t = ’DELIVER IN PERSON’

)

or

(

p p a r t k e y = l p a r t k e y

and p b r a n d = ’Brand#34’

and p c o n t a i n e r in (’LG CASE’, ’LG BOX’, ’LG PACK’, ’LG PKG’)

and l q u a n t i t y >= 20 and l q u a n t i t y <= 20 + 10

and p s i z e between 1 and 15

and l s h i p m o d e in (’AIR’, ’AIR REG’)

and l s h i p i n s t r u c t = ’DELIVER IN PERSON’

)

Listing 2.2: Data-structures to represent the Lineitem and Parts tables

s i z e t numTuples ; float ∗ l e x t e n d e d p r i c e ; float ∗ l d i s c o u n t ; t u p l e t ∗ l p a r t k e y ; unsigned int ∗ l q u a n t i t y ; u i n t 8 t ∗ l s h i p m o d e ; u i n t 8 t ∗ l s h i p i n s t r u c t ; } ; struct P a r t T a b l e { s i z e t numTuples ; t u p l e t ∗ p p a r t k e y ; u i n t 8 t ∗ p b r a n d ; u i n t 8 t ∗ p c o n t a i n e r ; unsigned int ∗ p s i z e ; } ;

Our simulated column store represents the TPC-H tables as structs of column pointers as depicted in Listing 2.2. Please note, that we only represent the columns accessed in TPC-H Q19. The type tuple t is a <key,payload> pair with the rowID as the payload. We depict the filter predicate implementation in Listing 2.3.

Listing 2.3: Filter conditions

inline bool p r e J o i n ( L i n e i t e m T a b l e ∗ l , s i z e t rowID ) {

return ( l −>l s h i p i n s t r u c t [ rowID ] == DELIVER IN PERSON &&

( l −>l s h i p m o d e [ rowID ] ==AIR | | l −>l s h i p m o d e [ rowID ] == AIR REG) ) ;

}

inline bool p o s t J o i n ( L i n e i t e m T a b l e ∗ l , P a r t T a b l e ∗p , s i z e t rowIDL ,

s i z e t rowIDP ) { u i n t 8 t p b r a n d = p−>p b r a n d [ rowIDP ] ; u i n t 8 t p c o n t a i n e r = p−>p c o n t a i n e r [ rowIDP ] ; auto l q u a n t i t y = l −>l q u a n t i t y [ rowIDL ] ; auto p s i z e = p−>p s i z e [ rowIDP ] ; return ( p b r a n d == BRAND12

&& ( p c o n t a i n e r == SM CASE | | p c o n t a i n e r == SM BOX | | p c o n t a i n e r == SM PACK | | p c o n t a i n e r == SM PKG) && l q u a n t i t y >= 1 && l q u a n t i t y <= 1 + 10

&& 1 <= p s i z e && p s i z e <= 5 ) | | ( p b r a n d == BRAND23 &&

( p c o n t a i n e r == MED BAG | | p c o n t a i n e r == MED BOX | | p c o n t a i n e r == MED PKG | | p c o n t a i n e r == MED PACK) && l q u a n t i t y >= 10 && l q u a n t i t y <= 10 + 10

&& 1 <= p s i z e && p s i z e <= 1 0 ) | | ( p b r a n d == BRAND34 &&

( p c o n t a i n e r == LG CASE | | p c o n t a i n e r == LG BOX | | p c o n t a i n e r == LG PACK | | p c o n t a i n e r == LG PKG)

2.9. Effects on Real Queries 45

&& l q u a n t i t y >= 20 && l q u a n t i t y <= 20 + 10 && 1 <= p s i z e && p s i z e <= 1 5 ) ;

}

These predicates correspond one-to-one to the predicates in the SQL query depicted in Listing 2.1.

Listing 2.4 shows the pseudo code for the Q19 implementation using the NOP join. All threads build a hash table on p partkey concurrently. Afterwards, every thread is responsible for a fixed chunk of tuples of the probe relation and first accesses the necessary attributes in LineitemTable to evaluate the pre- Join predicate. All passing tuples from the LineitemTable are probed against the hash table and as soon as a join partner is found the postJoin predicate is evaluated. If the matched tuples pass this predicate, the l extendedprice and l discount attributes are immediately accessed and added to the final aggre- gate. With this execution strategy it is not necessary to materialize a join index for further processing. This execution strategy also corresponds to the strategy described for the HyperDB system [68].

Listing 2.4: Q19 Pseudo code for NOP

1 q u e r y r e s u l t t

2 NOPQ19( L i n e i t e m T a b l e ∗L , P a r t T a b l e ∗P , int threadCount ) { 3 p a r a l l e l B u i l d (P , threadCount ) ;

4 p a r a l l e l for i n chunks numTuples / threadCount 5 for (int i =0; i < L−> numTuples;++ i ) {

6 if ( p r e J o i n ( L , i ) ) {

7 auto t u p l e=p r o b e ( L−>l p a r t k e y ) ; 8 if ( p o s t J o i n ( L , P , i , t u p l e . rowID ) ) {

9 r e s+=L−>l e x t e n d e d p r i c e [ i ] ∗ ( 1 . 0 − L−>l d i s c o u n t [ i ] ) ; 10 }}}}