Set - 7

Question 11 :

What is a Cartesian product?

Answer :

Level: Low
Expected answer: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.


Question 12 :

You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?

Answer :

Level: High
Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.


Question 13 :

What is the default ordering of an ORDER BY clause in a SELECT statement?

Answer :

Level: Low
Expected answer: Ascending


Question 14 :

What is tkprof and how is it used?

Answer :

Level: Intermediate to high
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.


Question 15 :

What is explain plan and how is it used?

Answer :

Level: Intermediate to high
Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.