SQL Reference
Data Definition Language (DDL)...

Query Analysis

You can analyze queries by commands that instruct to return plan-related information for a database administrator or analyst to evaluate. The results of these commands are vital in performance optimization activities on an Ocient system.

EXPLAIN

EXPLAIN shows the optimized query plan. The database optimizes the query normally but it does not execute. Instead, the database returns the query plan in the specified format.

Syntax

SQL


Parameter

Data type

Description

query

string

A valid SELECT statement on a database table.

This table defines the explain plan formats.

Format

Description

PROTO

Includes the same information as the JSON format, but it is presented in a human-readable presentation.

JSON

The default option if unspecified. Returns the optimized query plan in JSON format.

DEBUG

The DEBUG format is more compact, but does not include some information about each operator.

Examples

This example shows the optimized plan for the query SELECT * FROM names in JSON format.

SQL


This EXPLAIN example generates a lengthy output showing the execution plan for the query. This execution plan shows how the database scans the tables referenced by the statement and calculates the memory usage and total execution cost.



This example shows the optimized plan for the query SELECT * FROM names in DEBUG format, which produces a more compact execution plan.

SQL


Output

Text


EXPLAIN PIPELINE

EXPLAIN PIPELINE shows the pipeline that the system uses for compiling a query. The system compiles the pipeline for the given segments, and if no segment is specified, it will pick random segments that have the tables.

To explain a pipeline, you must be a system-level user or possess the SELECT TABLE privilege for the table used in the query.

Syntax

SQL


Parameter

Data type

Description

query

string

A valid SELECT statement on a database table.

storage_id

string

A valid segment storage identifier.

Examples

This example shows the pipeline on the query SELECT * FROM names for a random segment.

SQL


This example shows the pipeline for the segment with the identifier 529a2e9d-d06c-46cd-a93b-624d3bed1c08.

SQL


This example shows the pipeline for segments with these identifiers: 529a2e9d-d06c-46cd-a93b-624d3bed1c08, 378eac93-2e3d-4ce6-bf1f-d8b7a78fe585.

SQL


All the examples generate lengthy output that includes the execution plan for the query for the specified segments. This execution plan shows how the database scans the tables referenced by the statement and calculates the memory usage and total execution cost.

Related Links

SQL Reference