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 execution plan?
Exhibit:
A. Step 2 is performing nested operation on JOB_ID column of the JOBS table, which is the driven table and the EMPLOYEES table is the driven table.
B. In step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed.
C. Step 1 is performing nested loop operation on the DEPARTMENT_ID column of the DEPARTMENTS table, which is the driven table and results returned by step 2 in the driving resultset.
D. The performance of the query can be improved by creating bitmap index on the JOB_ID column of the EMPLOYEES table.
E. The performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID column of the EMPLOYEES table.
Correct Answer: BE
Explanation/Reference:
As per exhibit:
B, not A, Not C: First isline 5executed, 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.
Note:
*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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
