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?
A.
Column filtering
B.
Sort merge join filtering
C.
Predicate filtering
D.
Nested loop Join filtering
E.
Hash join filtering
F.
Virtual column filtering
Explanation:
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 FilteringExadata 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.
E, not B,D.
Hash join will benefit from smart scan offloading.
A hash join builds a hash table in memory from the smallest row source in the join.
Because the hash table can fit into the memory of the storage server, it benefits from
the bloom filter.
Nested loops requires looping over all the rows the smallest table in the join and using an index
to join to the second table. Because the data typically cannot all fit into memory, it cannot benefit
from the bloom filter.
Sort-merge joins are generally an alternative to hash joins but require a pre-ordering of at
least one of the row sources. Since it requires database indexes and table statistics to
determine whether this is so, this case cannot be offloaded to the storage server.
Reference: “Drilling Deep Into Exadata Performance.” by Tanel Poder
Should be A, C, and E. Hash joins can be done on storage server and are considered part of smart scan.
a c e
a c e
a c e
ACE is the correct answer.
E because :
Offloaded joins are accomplished by creating what is called a bloom filter
and bloom filter works only with hash joins.
A is right. Smart Scan can do column filtering
B is wrong. Only hash join can be offloaded using smart scan
C is right. Smart scan can do predicate flitering
D is wrong. Only hash join can be offloaded using smart scan
E is right. Only hash join can be offloaded using smart scan
F is wrong. No virtual column on smart scan
So A.C.E. are correct