Enterprise Java Development@TOPIC@

Chapter 82. H2 Execution Plans

82.1. Column Index
82.2. Summary

82.1. Column Index

Figure 82.1. Explain Plan: Where Column Not Indexed

EXPLAIN 
select m.title, role.movie_role 
from queryex_movie m 
join queryex_movierole role on role.movie_id = m.id 
where m.rating='R' and role.movie_role='Chip Diller';

PLAN  
SELECT
    M.TITLE,
    ROLE.MOVIE_ROLE
FROM PUBLIC.QUERYEX_MOVIEROLE ROLE
    /* PUBLIC.QUERYEX_MOVIEROLE.tableScan */
    /* WHERE ROLE.MOVIE_ROLE = 'Chip Diller'
    */
INNER JOIN PUBLIC.QUERYEX_MOVIE M
    /* PUBLIC.PRIMARY_KEY_C7B: ID = ROLE.MOVIE_ID */
    ON 1=1
WHERE (ROLE.MOVIE_ID = M.ID)
    AND ((M.RATING = 'R')
    AND (ROLE.MOVIE_ROLE = 'Chip Diller'))

Figure 82.2. Index Where Column

create index movierole_role_movie_idx on queryex_movierole(movie_role)

Figure 82.3. Explain Plan: Where Column Indexed

PLAN  
SELECT
    M.TITLE,
    ROLE.MOVIE_ROLE
FROM PUBLIC.QUERYEX_MOVIEROLE ROLE
    /* PUBLIC.MOVIEROLE_ROLE_MOVIE_IDX: MOVIE_ROLE = 'Chip Diller' */
    /* WHERE ROLE.MOVIE_ROLE = 'Chip Diller'
    */
INNER JOIN PUBLIC.QUERYEX_MOVIE M
    /* PUBLIC.PRIMARY_KEY_C7B: ID = ROLE.MOVIE_ID */
    ON 1=1
WHERE (ROLE.MOVIE_ID = M.ID)
    AND ((M.RATING = 'R')
    AND (ROLE.MOVIE_ROLE = 'Chip Diller'))

82.2. Summary