Suppose that the DEPARTMENT relation DEPARTMENT (dnumber, dname, mgr_ssn, start_date) has 1000 record stored in 200 blocks. The DEPARTMENT has: a clustering index on dnumber with 4 levels with the number of blocks in the first level is 50. A hash index on dnumber with a hash function f(x)=x mod 10 A secondary index on dnumber with 4 levels with the number of blocks in the first level is 50. Analyze the cost that the optimizer will compute, and determine which cost is the most efficient: 1. odno>5 (DEPARTMENT) 2. odno=1 (DEPARTMENT) 1 S1: Linear search Csa=b, For equality condition on a key attribute, C516= S2: Binary search Cs2=log2b+bx]-1, Reduces to logzb if equality condition is on a key attribute 1 S3a: Using a primary index to retrieve a single record Cs3a = x + 1 S3b: Using a hash key to retrieve a single record Cs36 = 1 I S4: Using an ordering index to retrieve multiple records Cs4 = x + b bfr - b 2 - S5: Using a clustering index to retrieve multiple records Cs5 = x + 1 = S6: Using a secondary (B+ tree) index Cs6a = x + 1 +5 (worst case) CS66 = x + + box + (for range queries) 2 + 2 Suppose that the DEPARTMENT relation DEPARTMENT (dnumber, dname, mgr_ssn, start_date) has 1000 record stored in 200 blocks. The DEPARTMENT has: a clustering index on dnumber with 4 levels with the number of blocks in the first level is 50. A hash index on dnumber with a hash function f(x)=x mod 10 A secondary index on dnumber with 4 levels with the number of blocks in the first level is 50. Analyze the cost that the optimizer will compute, and determine which cost is the most efficient: 1. odno>5 (DEPARTMENT) 2. odno=1 (DEPARTMENT) 1 S1: Linear search Csa=b, For equality condition on a key attribute, C516= S2: Binary search Cs2=log2b+bx]-1, Reduces to logzb if equality condition is on a key attribute 1 S3a: Using a primary index to retrieve a single record Cs3a = x + 1 S3b: Using a hash key to retrieve a single record Cs36 = 1 I S4: Using an ordering index to retrieve multiple records Cs4 = x + b bfr - b 2 - S5: Using a clustering index to retrieve multiple records Cs5 = x + 1 = S6: Using a secondary (B+ tree) index Cs6a = x + 1 +5 (worst case) CS66 = x + + box + (for range queries) 2 + 2