Prev Question
Next Question

A table in one of your database schemas contains only varchar, number, and date data types for
the columns.
Which three operations can be offloaded to the Exadata storage servers when doing a smart scan
against this table, if no other situations arise that prevent Smart Scan from occurring?

Column filtering

Sort merge join filtering

Predicate filtering

Nested loop Join filtering

Hash join filtering

Virtual column filtering

Exadata 11.1 Smart Scan operations include:
– Restriction (filtering of rows)
– Projection (filtering of columns)
– Join Filters (Bloom Filters) commonly used in Fact/Dimension joins found in Star Schemas. This
is different than Star Transformation.
Other join operations such as HASH(not E), SORT, SORT-MERGE(not B), NESTED LOOP(not
D), etc. are done by the Oracle Database Grid.
A:Smart Scan Column Filtering
Exadata provides column filtering, also called column projection, for table scans. Only the columns
requested are returned to the database server rather than all columns in a table. For example,
when the following SQL is issued, only the employee_name and employee_number columns are
returned from Exadata to the database kernel.
SELECT employee_name, employee_number FROM employee_table.
For tables with many columns, or columns containing LOBs (Large Objects), the I/O bandwidth
saved can be very large. Using both predicate and column filtering dramatically improves
performance and reduces I/O bandwidth consumption. In addition, column filtering also applies to
indexes, allowing for even faster query performance.
C:Smart Scan Predicate Filtering

Exadata enables predicate filtering for table scans. Only the rows requested are returned to the
database server rather than all rows in a table. For example, when the following SQL is issued
only rows where the employees’ hire date is after the specified date are sent from Exadata to the
database instance.
SELECT * FROM employee_table WHERE hire_date > ‘1-Jan-2003’.
This ability to return only relevant rows to the server greatly improves database performance. This
performance enhancement also applies as queries become more complicated, so the same
benefits also apply to complex queries, including those with subqueries.

Prev Question
Next Question

Leave a Reply

Your email address will not be published. Required fields are marked *