Log out Go to App
Go to App

Learning Materials

Features

Discover

Chapter 14: Problem 3

Why can we have at most one primary or clustering index on a file, but several secondary indexes?

Short Answer

Expert verified
We can only have one primary or clustering index on a file because it uniquely orders the records based on one field. Multiple primary indices would mean several unique ordering, which is not possible on a single file. However, secondary indexes don't uniquely order the file, but provide a fast search capability on various non-ordering fields, hence we can have several secondary indexes.

Step by step solution

01

Understanding Primary or Clustering index

A primary or clustering index is used to order the records in a file based on one field, which is the indexing field. This field has to be unique; thus we can only have one primary or clustering index on a file, because only one field can play this role. This type of index is also best used for range queries, because all the tuples with values within a certain range will be stored together.
02

Understanding Secondary Indexes

On the other hand, a secondary index, as the name indicates, is also an index, but it can be used on fields other than the one used for the primary index. It can be created for any field that we want to be searchable in a quick way, whether it is unique or not. Consequently, we can have more than one secondary index on a file, as there can be multiple fields in each record that we want to have fast access to. Secondary indexes provide pointers to the blocks, and within blocks, to the tuples.

Unlock Step-by-Step Solutions & Ace Your Exams!

  • Full Textbook Solutions

    Get detailed explanations and key concepts

  • Unlimited Al creation

    Al flashcards, explanations, exams and more...

  • Ads-free access

    To over 500 millions flashcards

  • Money-back guarantee

    We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!

One App. One Place for Learning.

All the tools & learning materials you need for study success - in one app.

Get started for free

Most popular questions from this chapter

as key field includes records with the following Part# values: 23,65,37,60,46,92,48, 71,56,59,18,21,10,74,78,15,16,20,24,28,39, 43,47,50,69,75,8,… # A PARTS file with Part# as key field includes records with the following Part# values: 23,65,37,60,46,92,48, 71,56,59,18,21,10,74,78,15,16,20,24,28,39, 43,47,50,69,75,8,49,33,38. Suppose that the search field values are inserted in the given order in a W -tree of order p = 4 and Pleaf = 3; show how the tree will expand and what the final tree will look like.

Explain what alternative choices exist for accessing a file based on multiple search keys.

What is partitioned hashing? How does it work? What are its limitations?

Consider a disk with block size B "" 512 bytes. A block pointer is P "" 6 bytes long, and a record pointer is PR "" 7 bytes long. A file has r "" 30,000 EMPLOYEE records of fixed length. Each record has the following fields: NAME (30 bytes), SSN (9 bytes), DEPARTMENTCODE (9 bytes), ADDRESS (40 bytes), PHONE (9 bytes), BIRTHDATE (8 bytes), SEX (l byte), JOBCODE (4 bytes), SALARY (4 bytes, real number). An additional byte is used as a deletion marker. a. Calculate the record size R in bytes. b. Calculate the blocking factor bfr and the number of file blocks b, assuming an unspanned organization. c. Suppose that the file is ordered by the key field SSN and we want to construct a primary index on SSN. Calculate (i) the index blocking factor bfri (which is also the index fan-out fa); (ii) the number of first-level index entries and the number of first-level index blocks; (iii) the number of levels needed if we make it into a multilevel index; (iv) the total number of blocks required by the multilevel index; and (v) the number of block accesses needed to search for and retrieve a record from the file-given its SSN value-using the primary index. d. Suppose that the file is not ordered by the key field SSN and we want to construct a secondary index on SSN. Repeat the previous exercise (part c) for the secondary index and compare with the primary index. e. Suppose that the file is not ordered by the nonkey field DEPARTMENTCODE and we want to construct a secondary index on DEPARTMENTCODE, using option 3 of Section 14.1.3, with an extra level of indirection that stores record pointers. Assume there are 1000 distinct values of DEPARTMENTCODE and that the EMPLOYEE records are evenly distributed among these values. Calculate (0 the index blocking factor bfr, (which is also the index fan-out fa); (ii) the number of blocks needed by the level of indirection that stores record pointers; (iii) the number of firstlevel index entries and the number of first-level index blocks; (iv) the number of levels needed if we make it into a multilevel index; (v) the total number of blocks required by the multilevel index and the blocks used in the extra level of indirection; and (vi) the approximate number of block accesses needed to search for and retrieve all records in the file that have a specific DEPARTMENTCODE value, using the index. f. Suppose that the file is ordered by the nonkey field DEPARTMENTCODE and we want to construct a clustering index on DEPARTMENTCODE that uses block anchors (every new value of DEPARTMENTCODE starts at the beginning of a new block). Assume there are 1000 distinct values of DEPARTMENTCODE and that the EMPLOYEE records are evenly distributed among these values. Calculate (i) the index blocking factor bfr, (which is also the index fan-out fa); (ii) the number of first-level index entries and the number of first-level index blocks; (iii) the number of levels needed if we make it into a multilevel index; (iv) the total number of blocks required by the multilevel index; and (v) the number of block accesses needed to search for and retrieve all records in the file that have a specific DEPARTMENT CODE value, using the clustering index (assume that multiple blocks in a cluster are contiguous). g. Suppose that the file is not ordered by y the key field SSN and we want to construct a B+-tree access structure (index) on SSN. Calculate (i) the orders p and Pleof of the W -tree: (ii) the number of leaf-level blocks needed if blocks are approximately 69 percent full (rounded up for convenience); (iii) the number of levels needed if internal nodes are also 69 percent full (rounded up for convenience); (iv) the total number of blocks required by the W-tree; and (v) the number of block accesses needed to search for and retrieve a record from the file-given its SSN value-using the B+-tree. h. Repeat part g, but for a B-tree rather than for a B+-tree. Compare your results for the B-tree and for the W-tree.

How does multilevel indexing improve the efficiency of searching an index file?
See all solutions

Recommended explanations on Computer Science Textbooks

Computer Organisation and Architecture

Read Explanation

Functional Programming

Read Explanation

Game Design in Computer Science

Read Explanation

Issues in Computer Science

Read Explanation

Databases

Read Explanation

Problem Solving Techniques

Read Explanation
View all explanations

What do you think about this solution?

We value your feedback to improve our textbook solutions.

Study anywhere. Anytime. Across all devices.

Sign-up for free