DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool ******************** EXPLAIN INSTANCE ******************** DB2_VERSION: 11.05.8 FORMATTED ON DB: SAKILA SOURCE_NAME: SQLC2P31 SOURCE_SCHEMA: NULLID SOURCE_VERSION: EXPLAIN_TIME: 2022-11-08-01.39.39.830961 EXPLAIN_REQUESTER: DB2INST1 Database Context: ---------------- Parallelism: None CPU Speed: 5.117063e-08 Comm Speed: 100 Buffer Pool size: 41726 Sort Heap size: 951 Database Heap size: 4556 Lock List size: 16549 Maximum Lock List: 98 Average Applications: 1 Locks Available: 518976 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 34 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No Query Degree: 1 Original Statement: ------------------ SELECT customer.last_name || ', ' || customer.first_name AS customer, address.phone, film.title FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id INNER JOIN address ON customer.address_id = address.address_id INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id INNER JOIN film ON inventory.film_id = film.film_id WHERE rental.return_date IS NULL AND rental_date + film.rental_duration DAYS < CURRENT DATE ORDER BY title LIMIT 5 Optimized Statement: ------------------- SELECT Q6.$C0 AS "CUSTOMER", Q6.PHONE AS "PHONE", Q6.TITLE AS "TITLE" FROM (SELECT (Q1.LAST_NAME || ', ' || Q1.FIRST_NAME), Q3.PHONE, Q5.TITLE FROM DB2INST1.CUSTOMER AS Q1, DB2INST1.RENTAL AS Q2, DB2INST1.ADDRESS AS Q3, DB2INST1.INVENTORY AS Q4, DB2INST1.FILM AS Q5 WHERE (Q2.CUSTOMER_ID = Q1.CUSTOMER_ID) AND (Q1.ADDRESS_ID = Q3.ADDRESS_ID) AND (Q2.INVENTORY_ID = Q4.INVENTORY_ID) AND (Q4.FILM_ID = Q5.FILM_ID) AND Q2.RETURN_DATE IS NULL AND ((Q2.RENTAL_DATE + Q5.RENTAL_DURATION DAYS) < CURRENT DATE /* SPREG_EXPR VALUE='11/08/2022'*/) ORDER BY Q5.TITLE ) AS Q6 ORDER BY Q6.TITLE Access Plan: ----------- Total Cost: 450.146 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 5 ^NLJOIN ( 2) 450.146 262.976 /-----------------+-----------------\ 5 1 ^NLJOIN FETCH ( 3) ( 14) 389.413 13.5336 254 2 /------+-------\ /---+----\ 5 1 1 603 TBSCAN FETCH IXSCAN TABLE: DB2INST1 ( 4) ( 12) ( 15) ADDRESS 328.517 13.5336 6.76789 Q3 245 2 1 | /---+----\ | 5 1 599 603 SORT IXSCAN TABLE: DB2INST1 INDEX: DB2INST1 ( 5) ( 13) CUSTOMER PK_ADDRESS 328.517 6.76789 Q1 Q3 245 1 | | 61 599 HSJOIN^ INDEX: DB2INST1 ( 6) PK_CUSTOMER 328.515 Q1 245 /---------+----------\ 1000 183 TBSCAN HSJOIN^ ( 7) ( 8) 49.3022 279.192 53 192 | /---------+---------\ 1000 4581 183 TABLE: DB2INST1 FETCH TBSCAN FILM ( 9) ( 11) Q5 143.584 135.564 43 149 /---+----\ | 4581 4581 16044 IXSCAN TABLE: DB2INST1 TABLE: DB2INST1 ( 10) INVENTORY RENTAL 81.5935 Q4 Q2 12 | 4581 INDEX: DB2INST1 IDX_FK_FILM_ID Q4 Operator Symbols : ------------------ Symbol Description --------- ------------------------------------------ >JOIN : Left outer join JOIN< : Right outer join >JOIN< : Full outer join xJOIN : Left antijoin JOINx : Right antijoin ^JOIN : Left early out JOIN^ : Right early out Extended Diagnostic Information: -------------------------------- No extended Diagnostic Information for this statement. Plan Details: ------------- 1) RETURN: (Return Result) Cumulative Total Cost: 450.146 Cumulative CPU Cost: 5.20638e+07 Cumulative I/O Cost: 262.976 Cumulative Re-Total Cost: 328.53 Cumulative Re-CPU Cost: 5.18867e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 355.585 Estimated Bufferpool Buffers: 17.9759 Arguments: --------- BLDLEVEL: (Build level) DB2 v11.5.8.0 : s2209201700 CPUCACHE: (Per-thread CPU cache size) 8388608 HEAPUSE : (Maximum Statement Heap Usage) 208 Pages MAX CARD: (Maximum Cardinality) 5 PLANID : (Access plan identifier) 73cde0f674a082c5 PREPTIME: (Statement prepare time) 102 milliseconds SEMEVID : (Semantic environment identifier) 431f78d03d9bb07e STMTHEAP: (Statement heap size) 8192 STMTID : (Normalized statement identifier) 31285ab675a536c6 TENANTID: (Compiled In Tenant ID) 0 TENANTNM: (Compiled In Tenant Name) SYSTEM Input Streams: ------------- 22) From Operator #2 Estimated number of rows: 5 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q7.TITLE(A)+Q7.PHONE+Q7.CUSTOMER 2) NLJOIN: (Nested Loop Join) Cumulative Total Cost: 450.146 Cumulative CPU Cost: 5.20611e+07 Cumulative I/O Cost: 262.976 Cumulative Re-Total Cost: 328.53 Cumulative Re-CPU Cost: 5.1884e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 355.585 Estimated Bufferpool Buffers: 17.9759 Arguments: --------- EARLYOUT: (Early Out flag) LEFT FETCHMAX: (Override for FETCH MAXPAGES) IGNORE ISCANMAX: (Override for ISCAN MAXPAGES) IGNORE Predicates: ---------- 3) Predicate used in Join, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.00165837 Predicate Text: -------------- (Q1.ADDRESS_ID = Q3.ADDRESS_ID) Input Streams: ------------- 17) From Operator #3 Estimated number of rows: 5 Number of columns: 5 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE(A)+Q1.FIRST_NAME+Q1.LAST_NAME +Q1.ADDRESS_ID+Q2.CUSTOMER_ID 21) From Operator #14 Estimated number of rows: 1 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.ADDRESS_ID(A)+Q3.PHONE Output Streams: -------------- 22) To Operator #1 Estimated number of rows: 5 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q7.TITLE(A)+Q7.PHONE+Q7.CUSTOMER 3) NLJOIN: (Nested Loop Join) Cumulative Total Cost: 389.413 Cumulative CPU Cost: 5.18501e+07 Cumulative I/O Cost: 254 Cumulative Re-Total Cost: 328.523 Cumulative Re-CPU Cost: 5.1739e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 342.051 Estimated Bufferpool Buffers: 9 Arguments: --------- EARLYOUT: (Early Out flag) LEFT FETCHMAX: (Override for FETCH MAXPAGES) IGNORE ISCANMAX: (Override for ISCAN MAXPAGES) IGNORE JN INPUT: (Join input leg) OUTER Predicates: ---------- 2) Predicate used in Join, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.00166945 Predicate Text: -------------- (Q2.CUSTOMER_ID = Q1.CUSTOMER_ID) Input Streams: ------------- 12) From Operator #4 Estimated number of rows: 5 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE(A)+Q2.CUSTOMER_ID 16) From Operator #12 Estimated number of rows: 1 Number of columns: 4 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.CUSTOMER_ID(A)+Q1.FIRST_NAME+Q1.LAST_NAME +Q1.ADDRESS_ID Output Streams: -------------- 17) To Operator #2 Estimated number of rows: 5 Number of columns: 5 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE(A)+Q1.FIRST_NAME+Q1.LAST_NAME +Q1.ADDRESS_ID+Q2.CUSTOMER_ID 4) TBSCAN: (Table Scan) Cumulative Total Cost: 328.517 Cumulative CPU Cost: 5.16391e+07 Cumulative I/O Cost: 245 Cumulative Re-Total Cost: 328.515 Cumulative Re-CPU Cost: 5.15941e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 328.517 Estimated Bufferpool Buffers: 0 Arguments: --------- JN INPUT: (Join input leg) OUTER MAX CARD: (Maximum Cardinality) 5 MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) NONE SCANDIR : (Scan Direction) FORWARD SPEED : (Assumed speed of scan, in sharing structures) SLOW THROTTLE: (Scan may be throttled, for scan sharing) FALSE VISIBLE : (May be included in scan sharing structures) FALSE WRAPPING: (Scan may start anywhere and wrap) FALSE Input Streams: ------------- 11) From Operator #5 Estimated number of rows: 5 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE(A)+Q2.CUSTOMER_ID Output Streams: -------------- 12) To Operator #3 Estimated number of rows: 5 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE(A)+Q2.CUSTOMER_ID 5) SORT : (Sort) Cumulative Total Cost: 328.517 Cumulative CPU Cost: 5.16363e+07 Cumulative I/O Cost: 245 Cumulative Re-Total Cost: 328.515 Cumulative Re-CPU Cost: 5.15913e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 328.517 Estimated Bufferpool Buffers: 53 Arguments: --------- DUPLWARN: (Duplicates Warning flag) FALSE KEYS : (Key cardinality) 5 MAX CARD: (Maximum Cardinality) 5 NUMROWS : (Estimated number of rows) 5 ROWWIDTH: (Estimated width of rows) 28.000000 SORTKEY : (Sort Key column) 1: Q5.TITLE(A) TEMPSIZE: (Temporary Table Page Size) 4096 TRUNCSRT: (Truncated sort (for Fetch First n Rows Only)) TRUE UNIQUE : (Uniqueness required flag) FALSE Input Streams: ------------- 10) From Operator #6 Estimated number of rows: 61 Number of columns: 6 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.RENTAL_DATE+Q2.CUSTOMER_ID+Q4.FILM_ID +Q5.TITLE+Q5.RENTAL_DURATION+Q5.FILM_ID Output Streams: -------------- 11) To Operator #4 Estimated number of rows: 5 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE(A)+Q2.CUSTOMER_ID 6) HSJOIN: (Hash Join) Cumulative Total Cost: 328.515 Cumulative CPU Cost: 5.15913e+07 Cumulative I/O Cost: 245 Cumulative Re-Total Cost: 328.515 Cumulative Re-CPU Cost: 5.15913e+07 Cumulative Re-I/O Cost: 245 Cumulative First Row Cost: 328.515 Estimated Bufferpool Buffers: 53 Arguments: --------- BITFLTR : (Hash Join Bit Filter used) FALSE EARLYOUT: (Early Out flag) RIGHT HASHCODE: (Hash Code Size) 24 BIT HASHTBSZ: (Number of hash table entries) 183 TEMPSIZE: (Temporary Table Page Size) 4096 TUPBLKSZ: (Tuple Block Size (bytes)) 4000 Predicates: ---------- 5) Predicate used in Join, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.001 Predicate Text: -------------- (Q4.FILM_ID = Q5.FILM_ID) 7) Residual Predicate, Comparison Operator: Less Than (<) Subquery Input Required: No Filter Factor: 0.333333 Predicate Text: -------------- ((Q2.RENTAL_DATE + Q5.RENTAL_DURATION DAYS) < CURRENT DATE /* SPREG_EXPR VALUE='11/08/2022'*/) Input Streams: ------------- 2) From Operator #7 Estimated number of rows: 1000 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE+Q5.RENTAL_DURATION+Q5.FILM_ID 9) From Operator #8 Estimated number of rows: 183 Number of columns: 5 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.RENTAL_DATE+Q2.INVENTORY_ID+Q2.CUSTOMER_ID +Q4.FILM_ID+Q4.INVENTORY_ID Output Streams: -------------- 10) To Operator #5 Estimated number of rows: 61 Number of columns: 6 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.RENTAL_DATE+Q2.CUSTOMER_ID+Q4.FILM_ID +Q5.TITLE+Q5.RENTAL_DURATION+Q5.FILM_ID 7) TBSCAN: (Table Scan) Cumulative Total Cost: 49.3022 Cumulative CPU Cost: 2.09576e+06 Cumulative I/O Cost: 53 Cumulative Re-Total Cost: 0.0882039 Cumulative Re-CPU Cost: 1.72372e+06 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 6.76768 Estimated Bufferpool Buffers: 53 Arguments: --------- CUR_COMM: (Currently Committed) TRUE JN INPUT: (Join input leg) OUTER LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) SEQUENTIAL ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE SPEED : (Assumed speed of scan, in sharing structures) FAST TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) TRUE VISIBLE : (May be included in scan sharing structures) TRUE WRAPPING: (Scan may start anywhere and wrap) TRUE Input Streams: ------------- 1) From Object DB2INST1.FILM Estimated number of rows: 1000 Number of columns: 4 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.$RID$+Q5.TITLE+Q5.RENTAL_DURATION +Q5.FILM_ID Output Streams: -------------- 2) To Operator #6 Estimated number of rows: 1000 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.TITLE+Q5.RENTAL_DURATION+Q5.FILM_ID 8) HSJOIN: (Hash Join) Cumulative Total Cost: 279.192 Cumulative CPU Cost: 4.90949e+07 Cumulative I/O Cost: 192 Cumulative Re-Total Cost: 279.192 Cumulative Re-CPU Cost: 4.90949e+07 Cumulative Re-I/O Cost: 192 Cumulative First Row Cost: 279.192 Estimated Bufferpool Buffers: 44 Arguments: --------- BITFLTR : (Hash Join Bit Filter used) FALSE EARLYOUT: (Early Out flag) RIGHT HASHCODE: (Hash Code Size) 24 BIT HASHTBSZ: (Number of hash table entries) 183 JN INPUT: (Join input leg) INNER TEMPSIZE: (Temporary Table Page Size) 4096 TUPBLKSZ: (Tuple Block Size (bytes)) 4000 Predicates: ---------- 4) Predicate used in Join, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.000218293 Predicate Text: -------------- (Q2.INVENTORY_ID = Q4.INVENTORY_ID) Input Streams: ------------- 6) From Operator #9 Estimated number of rows: 4581 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.FILM_ID(A)+Q4.INVENTORY_ID 8) From Operator #11 Estimated number of rows: 183 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.RENTAL_DATE+Q2.INVENTORY_ID+Q2.CUSTOMER_ID Output Streams: -------------- 9) To Operator #6 Estimated number of rows: 183 Number of columns: 5 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.RENTAL_DATE+Q2.INVENTORY_ID+Q2.CUSTOMER_ID +Q4.FILM_ID+Q4.INVENTORY_ID 9) FETCH : (Fetch) Cumulative Total Cost: 143.584 Cumulative CPU Cost: 1.24851e+07 Cumulative I/O Cost: 43 Cumulative Re-Total Cost: 0.617016 Cumulative Re-CPU Cost: 1.2058e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 13.5334 Estimated Bufferpool Buffers: 44 Arguments: --------- CUR_COMM: (Currently Committed) TRUE JN INPUT: (Join input leg) OUTER LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) SEQUENTIAL,READAHEAD ROWLOCK : (Row Lock intent) SHARE (CS/RS) SKIP_INS: (Skip Inserted Rows) TRUE SPEED : (Assumed speed of scan, in sharing structures) SLOW TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) FALSE VISIBLE : (May be included in scan sharing structures) FALSE WRAPPING: (Scan may start anywhere and wrap) FALSE Input Streams: ------------- 4) From Operator #10 Estimated number of rows: 4581 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.FILM_ID(A)+Q4.$RID$ 5) From Object DB2INST1.INVENTORY Estimated number of rows: 4581 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.INVENTORY_ID Output Streams: -------------- 6) To Operator #8 Estimated number of rows: 4581 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.FILM_ID(A)+Q4.INVENTORY_ID 10) IXSCAN: (Index Scan) Cumulative Total Cost: 81.5935 Cumulative CPU Cost: 8.07986e+06 Cumulative I/O Cost: 12 Cumulative Re-Total Cost: 0.403654 Cumulative Re-CPU Cost: 7.8884e+06 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 6.76771 Estimated Bufferpool Buffers: 13 Arguments: --------- CUR_COMM: (Currently Committed) TRUE LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 12 PREFETCH: (Type of Prefetch) SEQUENTIAL,READAHEAD ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Input Streams: ------------- 3) From Object DB2INST1.IDX_FK_FILM_ID Estimated number of rows: 4581 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.FILM_ID(A)+Q4.$RID$ Output Streams: -------------- 4) To Operator #9 Estimated number of rows: 4581 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q4.FILM_ID(A)+Q4.$RID$ 11) TBSCAN: (Table Scan) Cumulative Total Cost: 135.564 Cumulative CPU Cost: 3.57339e+07 Cumulative I/O Cost: 149 Cumulative Re-Total Cost: 1.77916 Cumulative Re-CPU Cost: 3.47691e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 7.46347 Estimated Bufferpool Buffers: 149 Arguments: --------- CUR_COMM: (Currently Committed) TRUE JN INPUT: (Join input leg) INNER LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) SEQUENTIAL ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE SPEED : (Assumed speed of scan, in sharing structures) FAST TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) TRUE VISIBLE : (May be included in scan sharing structures) TRUE WRAPPING: (Scan may start anywhere and wrap) TRUE Predicates: ---------- 6) Sargable Predicate, Comparison Operator: Is Null Subquery Input Required: No Filter Factor: 0.0114061 Predicate Text: -------------- Q2.RETURN_DATE IS NULL Input Streams: ------------- 7) From Object DB2INST1.RENTAL Estimated number of rows: 16044 Number of columns: 5 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.$RID$+Q2.RENTAL_DATE+Q2.RETURN_DATE +Q2.INVENTORY_ID+Q2.CUSTOMER_ID Output Streams: -------------- 8) To Operator #8 Estimated number of rows: 183 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.RENTAL_DATE+Q2.INVENTORY_ID+Q2.CUSTOMER_ID 12) FETCH : (Fetch) Cumulative Total Cost: 13.5336 Cumulative CPU Cost: 71257.6 Cumulative I/O Cost: 2 Cumulative Re-Total Cost: 13.5318 Cumulative Re-CPU Cost: 35796.6 Cumulative Re-I/O Cost: 2 Cumulative First Row Cost: 13.5336 Estimated Bufferpool Buffers: 18 Arguments: --------- CUR_COMM: (Currently Committed) TRUE JN INPUT: (Join input leg) INNER LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 1 PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) SHARE (CS/RS) SKIP_INS: (Skip Inserted Rows) TRUE SPEED : (Assumed speed of scan, in sharing structures) SLOW TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) FALSE VISIBLE : (May be included in scan sharing structures) FALSE WRAPPING: (Scan may start anywhere and wrap) FALSE Input Streams: ------------- 14) From Operator #13 Estimated number of rows: 1 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.CUSTOMER_ID(A)+Q1.$RID$ 15) From Object DB2INST1.CUSTOMER Estimated number of rows: 599 Number of columns: 3 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.FIRST_NAME+Q1.LAST_NAME+Q1.ADDRESS_ID Output Streams: -------------- 16) To Operator #3 Estimated number of rows: 1 Number of columns: 4 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.CUSTOMER_ID(A)+Q1.FIRST_NAME+Q1.LAST_NAME +Q1.ADDRESS_ID 13) IXSCAN: (Index Scan) Cumulative Total Cost: 6.76789 Cumulative CPU Cost: 56437.6 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 6.76607 Cumulative Re-CPU Cost: 20976.6 Cumulative Re-I/O Cost: 1 Cumulative First Row Cost: 6.76789 Estimated Bufferpool Buffers: 4 Arguments: --------- CUR_COMM: (Currently Committed) TRUE LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 1 PREFETCH: (Type of Prefetch) SEQUENTIAL,READAHEAD ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Predicates: ---------- 2) Start Key Predicate, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.00166945 Predicate Text: -------------- (Q2.CUSTOMER_ID = Q1.CUSTOMER_ID) 2) Stop Key Predicate, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.00166945 Predicate Text: -------------- (Q2.CUSTOMER_ID = Q1.CUSTOMER_ID) Input Streams: ------------- 13) From Object DB2INST1.PK_CUSTOMER Estimated number of rows: 599 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.CUSTOMER_ID(A)+Q1.$RID$ Output Streams: -------------- 14) To Operator #12 Estimated number of rows: 1 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.CUSTOMER_ID(A)+Q1.$RID$ 14) FETCH : (Fetch) Cumulative Total Cost: 13.5336 Cumulative CPU Cost: 71259 Cumulative I/O Cost: 2 Cumulative Re-Total Cost: 13.5318 Cumulative Re-CPU Cost: 35798 Cumulative Re-I/O Cost: 2 Cumulative First Row Cost: 13.5336 Estimated Bufferpool Buffers: 14.8875 Arguments: --------- CUR_COMM: (Currently Committed) TRUE JN INPUT: (Join input leg) INNER LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 1 PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) SHARE (CS/RS) SKIP_INS: (Skip Inserted Rows) TRUE SPEED : (Assumed speed of scan, in sharing structures) SLOW TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) FALSE VISIBLE : (May be included in scan sharing structures) FALSE WRAPPING: (Scan may start anywhere and wrap) FALSE Input Streams: ------------- 19) From Operator #15 Estimated number of rows: 1 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.ADDRESS_ID(A)+Q3.$RID$ 20) From Object DB2INST1.ADDRESS Estimated number of rows: 603 Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.PHONE Output Streams: -------------- 21) To Operator #2 Estimated number of rows: 1 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.ADDRESS_ID(A)+Q3.PHONE 15) IXSCAN: (Index Scan) Cumulative Total Cost: 6.76789 Cumulative CPU Cost: 56439 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 6.76607 Cumulative Re-CPU Cost: 20978 Cumulative Re-I/O Cost: 1 Cumulative First Row Cost: 6.76789 Estimated Bufferpool Buffers: 3.9759 Arguments: --------- CUR_COMM: (Currently Committed) TRUE LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 1 PREFETCH: (Type of Prefetch) SEQUENTIAL,READAHEAD ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Predicates: ---------- 3) Start Key Predicate, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.00165837 Predicate Text: -------------- (Q1.ADDRESS_ID = Q3.ADDRESS_ID) 3) Stop Key Predicate, Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.00165837 Predicate Text: -------------- (Q1.ADDRESS_ID = Q3.ADDRESS_ID) Input Streams: ------------- 18) From Object DB2INST1.PK_ADDRESS Estimated number of rows: 603 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.ADDRESS_ID(A)+Q3.$RID$ Output Streams: -------------- 19) To Operator #14 Estimated number of rows: 1 Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.ADDRESS_ID(A)+Q3.$RID$ Objects Used in Access Plan: --------------------------- Schema: DB2INST1 Name: IDX_FK_FILM_ID Type: Index Time of creation: 2022-10-21-17.56.33.112570 Last statistics update: 2022-10-21-18.36.08.196783 Number of columns: 1 Number of rows: 4581 Width of rows: -1 Number of buffer pool pages: 31 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Index clustering statistic: 1.000000 Index leaf pages: 12 Index tree levels: 2 Index full key cardinality: 958 Index first key cardinality: 958 Index first 2 keys cardinality: -1 Index first 3 keys cardinality: -1 Index first 4 keys cardinality: -1 Index sequential pages: 0 Index page density: 0 Index avg sequential pages: 0 Index avg gap between sequences:0 Index avg random pages: 12 Fetch avg sequential pages: -1 Fetch avg gap between sequences:-1 Fetch avg random pages: -1 Index RID count: 4581 Index deleted RID count: 0 Index empty leaf pages: 0 Base Table Schema: DB2INST1 Base Table Name: INVENTORY Columns in index: FILM_ID(A) Null keys: Yes Schema: DB2INST1 Name: PK_ADDRESS Type: Index Time of creation: 2022-10-21-17.56.30.438268 Last statistics update: 2022-10-21-18.36.03.980067 Number of columns: 1 Number of rows: 603 Width of rows: -1 Number of buffer pool pages: 11 Distinct row values: Yes Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Index clustering statistic: 100.000000 Index leaf pages: 3 Index tree levels: 2 Index full key cardinality: 603 Index first key cardinality: 603 Index first 2 keys cardinality: -1 Index first 3 keys cardinality: -1 Index first 4 keys cardinality: -1 Index sequential pages: 0 Index page density: 0 Index avg sequential pages: 0 Index avg gap between sequences:0 Index avg random pages: 3 Fetch avg sequential pages: -1 Fetch avg gap between sequences:-1 Fetch avg random pages: -1 Index RID count: 603 Index deleted RID count: 0 Index empty leaf pages: 0 Base Table Schema: DB2INST1 Base Table Name: ADDRESS Columns in index: ADDRESS_ID(A) Null keys: Yes Schema: DB2INST1 Name: PK_CUSTOMER Type: Index Time of creation: 2022-10-21-17.56.31.071028 Last statistics update: 2022-10-21-18.36.05.527937 Number of columns: 1 Number of rows: 599 Width of rows: -1 Number of buffer pool pages: 14 Distinct row values: Yes Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Index clustering statistic: 100.000000 Index leaf pages: 3 Index tree levels: 2 Index full key cardinality: 599 Index first key cardinality: 599 Index first 2 keys cardinality: -1 Index first 3 keys cardinality: -1 Index first 4 keys cardinality: -1 Index sequential pages: 0 Index page density: 0 Index avg sequential pages: 0 Index avg gap between sequences:0 Index avg random pages: 3 Fetch avg sequential pages: -1 Fetch avg gap between sequences:-1 Fetch avg random pages: -1 Index RID count: 599 Index deleted RID count: 0 Index empty leaf pages: 0 Base Table Schema: DB2INST1 Base Table Name: CUSTOMER Columns in index: CUSTOMER_ID(A) Null keys: Yes Schema: DB2INST1 Name: ADDRESS Type: Table Time of creation: 2022-10-21-17.56.30.438268 Last statistics update: 2022-10-21-18.36.03.980067 Number of columns: 8 Number of rows: 603 Width of rows: 39 Number of buffer pool pages: 11 Number of data partitions: 1 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Schema: DB2INST1 Name: CUSTOMER Type: Table Time of creation: 2022-10-21-17.56.31.071028 Last statistics update: 2022-10-21-18.36.05.527937 Number of columns: 9 Number of rows: 599 Width of rows: 58 Number of buffer pool pages: 14 Number of data partitions: 1 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Schema: DB2INST1 Name: FILM Type: Table Time of creation: 2022-10-21-17.56.31.383222 Last statistics update: 2022-10-21-18.36.06.257174 Number of columns: 13 Number of rows: 1000 Width of rows: 54 Number of buffer pool pages: 53 Number of data partitions: 1 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Schema: DB2INST1 Name: INVENTORY Type: Table Time of creation: 2022-10-21-17.56.32.934136 Last statistics update: 2022-10-21-18.36.08.196783 Number of columns: 4 Number of rows: 4581 Width of rows: 38 Number of buffer pool pages: 31 Number of data partitions: 1 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Schema: DB2INST1 Name: RENTAL Type: Table Time of creation: 2022-10-21-17.56.34.224860 Last statistics update: 2022-11-01-01.21.40.379522 Number of columns: 7 Number of rows: 16044 Width of rows: 47 Number of buffer pool pages: 149 Number of data partitions: 1 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0