Indexing Using the relational table ORDERS of the sample database TPCHR and the following index created,...

Transcribed Image Text:

Indexing Using the relational table ORDERS of the sample database TPCHR and the following index created, construct an appropriate SQL statement and satisfies the following requirement: create index A1Task3Idx on orders(o_orderdate, o_clerk, o_totalprice); Find SELECT statements that will use the index in the following ways: a) Execution of the first SELECT statement must traverse the index vertically and it must not access a relational table ORDERS. (0.3 mark) b) Execution of the second SELECT statement must first traverse the index vertically followed by horizontal scan at the leaf level. The retrieval operation must not access a relational table ORDERS. (0.3 mark) c) Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it must not access the relational table ORDERS. (0.3 mark) d) Execution of the fourth SELECT statement must traverse the index vertically and it must access a relational table ORDERS. (0.3 mark) e) Execution of the fifth SELECT statement must first traverse the index vertically followed by horizontal scan at the leaf level. The retrieval operation must access a relational table ORDERS. (0.3 mark) Indexing Using the relational table ORDERS of the sample database TPCHR and the following index created, construct an appropriate SQL statement and satisfies the following requirement: create index A1Task3Idx on orders(o_orderdate, o_clerk, o_totalprice); Find SELECT statements that will use the index in the following ways: a) Execution of the first SELECT statement must traverse the index vertically and it must not access a relational table ORDERS. (0.3 mark) b) Execution of the second SELECT statement must first traverse the index vertically followed by horizontal scan at the leaf level. The retrieval operation must not access a relational table ORDERS. (0.3 mark) c) Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it must not access the relational table ORDERS. (0.3 mark) d) Execution of the fourth SELECT statement must traverse the index vertically and it must access a relational table ORDERS. (0.3 mark) e) Execution of the fifth SELECT statement must first traverse the index vertically followed by horizontal scan at the leaf level. The retrieval operation must access a relational table ORDERS. (0.3 mark)