Oracle 1z0-054 Exam Practice Questions (P. 4)
- Full Access (192 questions)
- Six months of Premium Access
- Access to one million comments
- Seamless ChatGPT Integration
- Ability to download PDF files
- Anki Flashcard files for revision
- No Captcha & No AdSense
- Advanced Exam Configuration
Question #17
View Exhibit1 to examine the description of the CUSTOMERS table.
You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.
The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table. What can you do to make the optimizer detect the actual number of rows?
You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.
The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table. What can you do to make the optimizer detect the actual number of rows?
Question #18
View Exhibit1 to examine the description of the CUSTOMERS table.
The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice that estimated rows and the actual number of rows fetched differ greatly. The COUNTRY_ID column has an index.
View Exhibit2 and examine the query execution plan.
What would you recommend to improve the optimizer's estimation?
The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice that estimated rows and the actual number of rows fetched differ greatly. The COUNTRY_ID column has an index.
View Exhibit2 and examine the query execution plan.
What would you recommend to improve the optimizer's estimation?
Question #19
You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.
Why is the query using a full table scan instead of an index scan?
Why is the query using a full table scan instead of an index scan?
Question #20
View Exhibit1 and examine the indexes on the CUSTOMERS table.
The statistics for the CUSTOMERS table have been updated recently by using the following command:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL
INDEXED COLUMNS SIZE AUTO');
View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan.
What could be the reason?
The statistics for the CUSTOMERS table have been updated recently by using the following command:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL
INDEXED COLUMNS SIZE AUTO');
View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan.
What could be the reason?
All Pages