| 1: | Your users are trying to create a domain index (dom_col_key) on a column in a table that has been defined as a VARRAY column. The table already has two other indexes on it. They are getting an error saying that you cannot create an index on an expression with data type. What is the possible reason for the error? A. The table already has too many indexes. B. Domain indexes can't be built on columns that are VARRAY type. C. The users don't have the proper authority to create the index. D. They are using an invalid name for the index.
|
| A1: | Answer B is correct; domain indexes cannot be built on VARRAY columns. Answer A is incorrect; two indexes on a table is not an excessive number. Answer C is incorrect; improper authority would not give you an error concerning the data type. Answer D is incorrect because the error does not indicate that there is an issue with the name. |
| 2: | What kind of index does the following syntax create? CREATE INDEX address_state_idx ON address PCTFREE 10 TABLESPACE mydb1idx NOLOGGING;
A. Bitmap B. B-tree C. Function based D. Reverse key
|
| A2: | Answer B is correct; the syntax stated creates a B-tree index. Answer A is incorrect; to create a bitmap index you need to provide the BITMAP keyword in the CREATE INDEX statement. Answer C is incorrect; there is no function in the CREATE INDEX script. Answer D is incorrect; to create a reverse key index, you need to include the keyword REVERSE. |
| 3: | With your users and developers, you have determined that you need to add the State index to the addresses table. The index has been determined to have the following criteria: The index will be called address_state_idx. Because you are only dealing with US addresses and the table is planned to have a million rows, the column's cardinality will be limited. The index should be created in the indexes1 tablespace. All extents should be 100KB. The index is nonunique. No redo information should be generated for the index creation. 15% of the block should be free for future entries.
Which of the following commands meets all these criteria and will build the desired index? A. CREATE UNIQUE INDEX address_state_idx ON addresses(state) TABLESPACE indexes1 PCTFREE 15 STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) NOLOGGING;
B. CREATE BITMAP INDEX address_state_idx ON addresses(state) TABLESPACE indexes1 PCTFREE 15 STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
C. CREATE BITMAP INDEX address_state_idx ON addresses(state) TABLESPACE indexes1 PCTFREE 15 STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) NOLOGGING;
D. CREATE BITMAP INDEX address_state_idx ON addresses(state) REVERSE TABLESPACE indexes1 PCTFREE 15 STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) NOLOGGING;
|
| A3: | Answer C is correct; this will create a bitmap index (low cardinality on the indexed column) that meets all the other requirements. Answer A is incorrect; that will create a unique index, and the specifications stated that it should be nonunique. Answer B is incorrect; the specifications stated that there should be no redo logged on index creation. Answer D is incorrect; the specifications did not include a reverse key index. |
| 4: | After running ANALYZE INDEX claim_id_idx VALIDATE STRUCTURE, you query the INDEX_STATS view and learn that you have a rather high ratio of deleted leaf rows (DEL_LF_ROWS) to leaf rows (LF_ROWS); in fact, the ratio ends up being almost 40%. You decide that you need to reorganize the index to free up some of the extra space, but the space should remain allocated to the index so that it can be reused for new entries. Which command or commands would allow you to perform this task while maintaining the minimum impact on the users who are running queries on the system? [select all that apply] |
| A4: | Answer C is correct; you will need to run the ALTER INDEX COALESCE command to avoid impacting users who are running queries that will need to access the index while the reorganization is in process. Answer A is incorrect; dropping the index and re-creating it would have detrimental effects on the users on the system. Answer B is incorrect; ALTER INDEX REBUILD would eliminate the gaps in the leaf blocks, but Oracle locks the base table whose index is about to be rebuilt, and no changes to the data in the base table can occur. Answer D is incorrect; you would have a detrimental effect on users and would not eliminate the holes in the leaf blocks. |
| 5: | You have been monitoring the usage of an index that you believe to be underused on a table. You find that the index is used extensively but only during the first week of the month, when Accounting is running month-end processing. The rest of the month, the index is used only rarely despite the fact that it is an actively updated table. What could you do to speed up processing during the majority of the month, yet allow queries to run as rapidly as possible during the month-end processing? A. Drop the index; it is obviously used only 25% of the time. B. Move the index to another tablespace. C. Drop the index after the month-end processing and rebuild it right before the next month-end processing. D. Rebuild the index immediately before the month-end processing.
|
| A5: | Answer C is correct; you can drop the index right after month-end processing is complete so that the inserts and updates won't have to update the indexes when they aren't being used and then rebuild the index right before the period when the index is heavily used. Answer A is incorrect; the index is heavily used for that one week of the month, and the index, at that point, is very advantageous. Answer B is incorrect; moving the index won't have any effect on the use of the index. Answer D is incorrect; rebuilding the index won't cause it to be used more or less, only use space more efficiently. |
| 6: | Which statements are true in relationship to the number of indexes on a table? [select all that apply] A. Every column in a table that is subject to update will see update performance improve with the addition of at least one index on the column. B. Every column that participates in a query should have an index. C. There is a limit of 32 indexes on a table. D. There is a limit of 32 columns in an index. E. Columns that are often queried as part of a WHERE clause are good candidates for an index.
|
| A6: | Answers D and E are correct. Columns that are in a WHERE clause are good candidates for indexes because they are limiting factors in queries, and there is a hard limit in Oracle that an index can have only 32 participating columns. Answer A is incorrect; indexes don't help insert, update, and delete performance. Answer B is incorrect; any column can participate in a query, but not every column should have an index. Answer C is incorrect; there is no limit on the number of indexes on a table. |
| 7: | You need to determine the names and types of indexes as well as the owners of those indexes in the database. In which data dictionary view would you find this information? A. DBA_INDEXES B. DBA_IND_COLUMNS C. V$INDEXES D. DBA_IND_EXPRESSIONS
|
| A7: | Answer A is correct; DBA_INDEXES provides the needed information. Answer B is incorrect; DBA_IND_COLUMNS does not contain the index type. Answer C is incorrect; V$INDEXES view does not exist. Answer D is incorrect; DBA_IND_EXPRESSIONS does not include the type of index and only tells you about function-based indexes. |
| 8: | Which two statements are true about rebuilding indexes? [choose two] A. The resulting index contains all the deleted entries from the original index. B. The new index is built using the table data as its source. C. The new index is built using the existing index data as its data source. D. Queries cannot continue using the existing index until the new index is built. E. During the online rebuild, Oracle requires that there be sufficient space for two copies of the indexes to exist in their respective tablespaces.
|
| A8: | Answers C and E are correct; the index is built off the data in the existing indexes, and sufficient space needs to exist for two copies of the indexes for the duration of the rebuild. Answer A is incorrect; the new index will not include the deleted entries. Answer B is incorrect; the new index is built from the old index not from the table data. Answer D is incorrect; queries can continue throughout the rebuild if the rebuild is an online rebuild. |
| 9: | Which of the following statements is true about bitmap indexes? A. They are useful when a column contains few unique values that are changed frequently. B. They are useful when a column contains few unique values that change infrequently. C. They are useful when a column contains many unique values that change frequently. D. They are useful when a column contains many unique values that change infrequently.
|
| A9: | Answer B is correct; bitmap indexes are primarily useful when a column contains few unique values (brand name of sodas, genders, or makes of automobiles), and those values rarely change. All the others are conditions that would imply that bitmap indexes would not be an optimum choice. |
| 10: | Which of the following is not a view that would provide information on indexes? A. DBA_INDEXES B. USER_IND_COLUMNS C. ALL_IND_COLUMNS D. V$INDEXES
|
| A10: | Answer D is correct; the only view in the list that would not provide information on indexes in the database would be V$INDEXES, and that is because it is not a valid data dictionary view. |
