Solutions Chapter 12: Views, Indexes, and Sequences Test Your Thinking

  1. Who can update the SALARY column through the MY_EMPLOYEE view? Hint: The USER function returns the name of the currently logged in user.

    SELECT employee_id, employee_name, salary, manager
      FROM employee
     WHERE manager = USER
      WITH CHECK OPTION CONSTRAINT my_employee_ck_manager


    Only managers can update their respective employee's salaries. The WITH CHECK OPTION constraint ensures that DML statements satisfy the condition in the WHERE clause. This condition enforces that only records are displayed, updated, inserted, and deleted where the value in the MANAGER column is equal to the user currently logged in. A SELECT statement against the MY_EMPLOYEE view for the user with the login ID of JONES could look like this:

    ----------- ------------------------- --------- -----
            150 Gates                         11787 JONES
            251 Sheppard                      11106 JONES
            552 Edwards                        7036 JONES
            353 Philpotts                     11373 JONES
  2. Which columns in a table should you consider indexing?


    Columns frequently used in the WHERE clause of SQL statements are good candidates for indexes. Be sure to consider the selectivity of the values of the columns, that is, how many distinct values there are in the column. Sometimes it is useful to combine several columns with a low selectivity in a concatenated index. Make sure you properly access the index. You also see more examples on indexes and their impact in Chapter 16, "SQL Optimization." In addition, consider indexing foreign key columns, because they not only are frequently referenced in the WHERE clause of joins, but also improve the locking of records on the child table.

  3. Explain the purpose of the Oracle SQL command below.

    ALTER INDEX crse_crse_fk_i rebuild


    This command rebuilds an existing index named CRSE_CRSE_FK_I without having to drop the old index first and then re-create it.

  4. Are NULLs stored in an index? Explain.


    NULLs are not stored in an index. The exception is if it is a concatenated index and the leading column of the index does not contain a NULL value. Another exception is a bitmapped index, which stores null values.

Select Another Chapter

  1. SQL and Data
  2. SQL: The Basics
  3. Character and Number Functions
  4. Date, Conversion, and Miscellaneous Functions
  5. Aggregate Functions, GROUP BY, and HAVING
  6. Equijoins
  7. Subqueries
  8. Set Operators
  9. Complex Joins
  10. Insert, Update, and Delete
  11. Create, Alter, and Drop Tables
  12. Views, Indexes, and Sequences
  13. The Data Dictionary and Dynamic SQL Scripts
  14. Security
  15. Advanced SQL Queries
  16. SQL Optimization