Zend Preparation | PHP Quiz (Learning Mode)

Database and SQL Learning Mode

Qusetions Index 30/75

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

You have a table created as follows:

create table foo (c1 int, c2 char(30), c3 int, c4 char(10))

If column c1 is unique, which of the following indexes would optimize the statement given below?

Select distinct (c1), c3 from foo where c1=10

create index foox on foo (c1)

create index foox on foo (c1,c3)

create unique index foox on foo (c1,c3)

create unique index foox on foo (c1) include (c3)

Explanation:
Answer option D is correct.

The optimal index has column C1 as unique, and includes column C3 using the following statement:

create unique index foox on foo (c1) include (c3)

Since the query has a predicate on column C1, that column should be part of the index. Since column C1 is unique, the index should be a unique index, to minimize any extra work required to enforce the DISTINCT clause on the query. By creating a unique index, the DISTINCT clause will not need to do a sort on that column. Therefore, the two non-unique indexes are not optimal.

Answer option C is incorrect. Since the query also returns column C2, if that is part of the index, DB2 need not access the table at all. The following statement does not enforce uniqueness on C1, rather on the combination of C1 and C3

create unique index foox on foo (c1,c3)

So this is not optimal.