Oracle 1Z0-117 問題集

質問 1：
You execute the following query:

Which statement is true about the usage of these hints in the query?
A. The hint will have no effect because one of the join resultsets is an inline view.
B. The optimizer evaluates the subquery as a first and then filters out rows.
C. The optimizer pushes the join predicate into the inline view.
D. The optimizer performs a join operation first and then filters out the rows.
正解：C
解説: (Topexam メンバーにのみ表示されます)

質問 2：
View the code sequence:

Examine the Exhibit to view the execution plan.


Which two statements are true about the query execution?
A. The optimizer has chosen hash join as an access method as the OPTIMIZER_MODE parameter is set to FIRST_ROWS.
B. The CUSTOMERS and ORDERS tables are joined first and the resultant is then joined with rows returned by the ORDER_ITEMS table.
C. The CUSTOMERS and ORDER_ITEMS tables are joined first the resultant is then joined with rows by the ORDERS table.
D. The optimizer joins specified tables in the order as they appear in the FROM clause.
正解：A,C
解説: (Topexam メンバーにのみ表示されます)

質問 3：
Which three statements are true about the usage of optimizer hints?
A. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.
B. Whenever a query uses table aliases, the hints in the query must use the aliases.
C. The optimizer uses the execution plan with lower cost even if a hint is specified.
D. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.
E. A schema name for the table must be used in the hint if the table us qualified in the FROM clause.
F. A statement block can have only one hint, and that hint must be immediately after SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.
正解：A,B,D
解説: (Topexam メンバーにのみ表示されます)

質問 4：
View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.

A SWL plan baseline already exists for the execution plan.
What two methods can you use to ensure that an alternate plan becomes an accepted plan?
A. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.
B. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.
C. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.
D. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.
正解：C,D
解説: (Topexam メンバーにのみ表示されます)

質問 5：
Examine the initializing parameters:

An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows.
View the exhibit and examine the query and its execution plan.

What can be the two reasons for full table scan?
A. The OPTIMIZER_MODE parameter is set to ALL_ROWS.
B. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
C. The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.
D. Histogram statistics for CUST_CITY_ID are missing.
E. The statistics for the CUSTOMERS table and the indexes stale.
F. Average number of rows per block for the CUSTOMERS table is low.
正解：D,E
解説: (Topexam メンバーにのみ表示されます)