Functions Part1

EXPLAIN Command :

The EXPLAIN function looks at a SQL request and responds in English how the optimizer plans to execute it. It does not actually execute the SQL statement however it is a good way to see what database resources will be used in processing your request.

For instance, if you see that your request will force a full-table scan on a very large table or cause a Cartesian Product Join, you may decide to re-write a request so that it executes more efficiently.

EXPLAIN provides a wealth of information, including the following:

• Which indexes if any will be used in the query.
• Whether individual steps within the query may execute concurrently (i.e. parallel steps).
• An estimate of the number of rows which will be processed.
• An estimate of the cost of the query (in time increments).

EXPLAIN SELECT * FROM department;
*** Query completed. Ten rows found. One column returned.

  

Explanation

1.First, we lock a distinct CUSTOMER_SERVICE."pseudo table" for read on a RowHash to prevent global deadlock for CUSTOMER_SERVICE.department.

2.Next, we lock CUSTOMER_SERVICE.department for read.

3.We do an all-AMPs RETRIEVE step from CUSTOMER_SERVICE.department by way of an all-rows scan with no residual conditions into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 4 rows. The estimated time for this step is 0.15 seconds.

4.Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

 The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.15 seconds.

HELP Command:

HELP Command is used to display information about database objects such as

• Databases and Users
• Tables
• Views
• Macros

HELP retrieves information about these objects from the Data Dictionary.

Below are the syntactical options for various forms of the HELP command:

HELP                         Command
HELP                         DATABASE databasename; 

HELP                         USER username;

HELP                         TABLE tablename;

HELP                         VIEW viewname;

HELP                         MACRO macroname;

HELP                         COLUMN table or viewname.*;  (all columns)

HELP                         COLUMN table or viewname.colname . . ., colname; 

 

SHOW Command :

The SHOW command displays the current Data Definition Language (DDL) of a database object (e.g., Table, View, Macro, Trigger, Join Index or Stored Procedure).

The SHOW command is used primarily to see how an object was created.

 Sample Show Commands

Command       Returns

SHOW            TABLE tablename;   CREATE TABLE statement

SHOW            VIEW viewname;   CREATE VIEW statement

SHOW            MACRO macroname;   CREATE MACRO statement