Examine the Exhibit to view the structure of and indexes for the EMPLOYEES and DEPARTMENTS tables: EXAMINE the SQL statement and its execution plan: Which two statements are correct regarding the…

Question

Exhibits

Options

• AStep 2 is performing nested operation on JOB_ID column of the JOBS table, which is the driven table
• BIn step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed.
• CStep 1 is performing nested loop operation on the DEPARTMENT_ID column of the DEPARTMENTS
• DThe performance of the query can be improved by creating bitmap index on the JOB_ID column of the
• EThe performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID

• A
  7% (3)
• B
  79% (33)
• C
  2% (1)
• D
  12% (5)

Explanation

B, not A, Not C: First is line 5 executed, followed by line 4, followed by line 3. Step 2 is line 4. E: The Department_ID column has lower cardinality compared to the JOB_ID column, so it is better suited for a bitmapped index. * Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two- dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table. At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information. * The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries. * Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two- dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table. At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

No community discussion yet for this question.