A table in one of your database schemas contains only varchar, number, and date data types for
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?
Sort merge join 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
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.