Setting Output Options
Setting Output Options#
Getting the Elapsed Time#
This function displays the time it took to execute the SQL statement.
iSQL> SET TIMING ON; -- Output the execution time in the last line after the command is executed.
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
.
.
.
20 rows selected.
elapsed time : 0.01
iSQL> SET TIMING OFF; -- Execution time is not displayed.
Setting Execution Time Units for Output#
This function sets the units with which to output SQL statement execution time. Can be set to the following units:
- Seconds
- Milliseconds
- Microseconds
- Nanoseconds
iSQL> SET TIMING ON
iSQL> SET TIMESCALE SEC;
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
20 rows selected.
elapsed time : 0.00
iSQL> SET TIMESCALE MILSEC;
iSQL> SELECT * FROM employee;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
...
20 rows selected.
elapsed time : 0.72
iSQL> SET TIMESCALE MICSEC;
iSQL> SELECT * FROM employee;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
20 rows selected.
elapsed time : 966.00
iSQL> SET TIMESCALE NANSEC;
iSQL> SELECT * FROM employee;
NO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
20 rows selected.
elapsed time : 681000.00
Describing Foreign Key Information#
This function displays information on foreign keys when the DESC command is used to view the table structure.
iSQL> SET FOREIGNKEYS ON; -- The foreign key information will be output.
iSQL> DESC employees;
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
ENO
[ FOREIGN KEYS ]
----------------------------------------------------------------------
iSQL> SET FOREIGNKEYS OFF; -- The foreign key information will not be output.
iSQL> DESC employees;
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
----------------------------------------------------------------------
ENO
Describing CHECK constraints Information#
This function displays information on CHECK constraints when the DESC command is used to view the table structure.
iSQL> SET CHKCONSTRAINTS ON; -- Check Constraint information is output.
iSQL> DESC employees;
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
ENO
[ CHECK CONSTRAINTS ]
------------------------------------------------------------------------------
NAME : EMP_CHECK_SEX1
CONDITION : SEX in ('M', 'F')
iSQL> SET CHKCONSTRAINTS OFF; -- Check Constraint information is not output.
iSQL> DESC employees;
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
ENO
Outputting the partition information#
This function allows to view partition information when viewing the table structure with the DESC command.
iSQL> create table t1_range(
c1 integer,
c2 integer,
c3 varchar(4))
PARTITION BY RANGE(c3)
(
PARTITION P_2000 VALUES LESS THAN ('2001') TABLESPACE sys_tbs_disk_data,
PARTITION P_2001 VALUES LESS THAN ('2002') TABLESPACE sys_tbs_mem_data,
PARTITION P_DEFAULT VALUES DEFAULT
) tablespace SYS_TBS_DISK_DATA;
iSQL> SET PARTITIONS ON; -- This command outputs the partition information.
iSQL> DESC t1_range
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
----------------------------------------------------
NAME TYPE IS NULL ----------------------------------------------------
C1 INTEGER
C2 INTEGER
C3 VARCHAR(4)
T1_RANGE has no index
T1_RANGE has no primary key
[ PARTITIONS ]
----------------------------------------------------
Method: Range
Key column(s)
----------------------------------------
NAME
----------------------------------------
C3
Values
----------------------------------------------------
PARTITION NAME MIN VALUE MAX VALUE
----------------------------------------------------
P_2000 '2001'
P_2001 '2001' '2002'
P_DEFAULT '2002'
Tablespace
---------------------------------------------------
PARTITION NAME TABLESPACE NAME
---------------------------------------------------
P_2000 SYS_TBS_DISK_DATA
P_2001 SYS_TBS_MEM_DATA
P_DEFAULT SYS_TBS_DISK_DATA
iSQL> SET PARTITIONS OFF; -- This command does not output the partition information.
iSQL> DESC t1_range
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
----------------------------------------------------
NAME TYPE IS NULL
----------------------------------------------------
C1 INTEGER
C2 INTEGER
C3 VARCHAR(4)
T1_RANGE has no index
T1_RANGE has no primary key
Outputting the Execution Results and Commands of Script Files#
The SET TERM and SET ECHO commands determine whether or not to output the execution results and commands of script files to the screen.
Script execution results are output(TERM ON) by default. If the TERM option is set to OFF, the commands which are executed and the results that are generated when the script file is executed in iSQL are not output to the screen. Even if the TERM option is set to OFF, however, query results are output to the screen if queries are manually input(e.g., iSQL> select * from t1;). Only when script commands are used(e.g., iSQL> @t.sql ), are the results not output to the screen.
Even if the TERM option is set to OFF, the commands executed in the script can be output by setting the ECHO command to ON.
The following example outputs the execution results of a script file.
iSQL> SET TERM ON; -- Outputs the script execution result.
iSQL> @schema.sql
iSQL> ALTER SESSION SET AUTOCOMMIT = TRUE; -- Beginning of the result.
Alter success.
iSQL> DROP TABLE ORDERS;
Drop success.
elapsed time : 0.00
iSQL> DROP TABLE EMPLOYEES;
Drop success.
elapsed time : 0.00
.
.
.
iSQL> CREATE INDEX ODR_IDX3 ON ORDERS (GNO ASC);
Create success.
elapsed time : 0.00 -- End of the result.
The following example demonstrates how the commands in the script that is executed with @ can be output, although the TERM option is set to OFF, by setting the ECHO option to ON.
iSQL> SET TERM OFF; -- The script execution results are not output.
iSQL> @schema.sql
iSQL> SELECT eno, e_firstname, e_lastname FROM employees;
-- The result is output when the query is manually input.
ENO E_FIRSTNAME E_LASTNAME
------------------------------------------------------------
1 Chan-seung Moon
2 Susan Davenport
3 Ken Kobain
4 Aaron Foster
5 Farhad Ghorbani
.
.
.
iSQL> SET ECHO ON; -- Only the commands in the script that is executed with @ are output.
iSQL> @schema.sql
ALTER SESSION SET AUTOCOMMIT = TRUE;
DROP TABLE ORDERS;
DROP TABLE EMPLOYEES;
.
.
.
iSQL> CREATE INDEX ODR_IDX3 ON ORDERS (GNO ASC);
Create success.
elapsed time : 0.00 -- End of the result
Outputting an Execution Plan#
In iSQL, an execution plan can be output to fine-tune SQL statements. Using an execution plan, DML statements such as SELECT, INSERT, UPDATE and DELETE can be checked.
In order to accomplish this, the following command must be executed before a statement such as a SELECT statement is executed.
ALTER SESSION SET EXPLAIN PLAN = option;
This option can be set to ON, OFF, or ONLY. The default is OFF.
-
ON
After the SELECT statement is executed, the execution plan information is displayed along with the resultant records. -
ONLY
The SELECT statement is prepared but not executed, and only the execution plan information is output.This can be used to check the execution plan for a SELECT statement that involves host variable binding, or to quickly check the execution plan for queries that take a long time to execute. -
OFF
After the SELECT statement is executed, only the resultant records are displayed.
The following command is used to obtain detailed information about how conditions included in WHERE clauses written by the user will be execute:
ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 1;
If this property is set to 1, signifying "ON", as in the above statement, the execution plan's WHERE clause conditions, including FIXED KEY RANGE, VARIBALE KEY RANGE and FILTER, are classified and displayed in detail. Therefore, if the WHERE clause is complicated, the user can check which predicates will be executed by scanning the sorted indexes. However, this information may not be output if queries are changed to optimize them in some way.
The following example shows the output when the given SQL statement is executed:
-
When TRCLOG_DETAIL_PREDICATE has been set to 1 (=on), and EXPLAIN PLAN = ON, the following is output in addition to the results.
iSQL> alter system set trclog_detail_predicate = 1; Alter success. iSQL> alter session set explain plan = on; Alter success. iSQL> SELECT eno, e_lastname, e_firstname FROM employees WHERE eno = 1; ENO E_LASTNAME E_FIRSTNAME ------------------------------------------------------------ 1 Moon Chan-seung 1 row selected. ------------------------------------------------------------ PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 ) SCAN ( TABLE: EMPLOYEES, INDEX: __SYS_IDX_ID_238, ACCESS: 1, SELF_ID: 2 ) [ FIXED KEY ] AND OR ENO = 1 ------------------------------------------------------------ -
When TRCLOG_DETAIL_PREDICATE is not set to 1, and EXPLAIN PLAN = ON, the following is output in addition to the results.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 0; Alter success. iSQL> ALTER SESSION SET EXPLAIN PLAN = ON; Alter success. iSQL> SELECT eno, e_lastname, e_firstname FROM employees WHERE eno = 1; ENO E_LASTNAME E_FIRSTNAME ------------------------------------------------------------ 1 Moon Chan-seung 1 row selected. ------------------------------------------------------------ PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 ) SCAN ( TABLE: EMPLOYEES, INDEX: __SYS_IDX_ID_238, ACCESS: 1, SELF_ID: 2 ) ------------------------------------------------------------ -
When TRCLOG_DETAIL_PREDICATE is not set to 1, and EXPLAIN PLAN = ONLY, only the following is output.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 0; Alter success. iSQL> ALTER SESSION SET EXPLAIN PLAN = ONLY; Alter success. iSQL> SELECT eno, e_lastname, e_firstname FROM employees WHERE eno = 1; ENO E_LASTNAME E_FIRSTNAME ------------------------------------------------------------ No rows selected. ------------------------------------------------------------ PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 ) SCAN ( TABLE: EMPLOYEES, INDEX: __SYS_IDX_ID_238, ACCESS: ??, SELF_ID: 2 ) ------------------------------------------------------------
If EXPLAIN PLAN = ONLY, because only an execution plan is created and the query is not executed, values that would be determined after actual execution are indicated using question marks ("??"), like an ACCESS clause.
Setting Result Output Orientation#
When querying data using a SELECT statement in iSQL, the results can be displayed either horizontally or vertically.
This function is suitable for outputting results that comprise a small number of rows and many columns. If such a result set is output horizontally, as is usually the case, it is difficult to compare columns and check the values. However, it is easy to see when output vertically.
iSQL>SET VERTICAL ON; -- This sets the print direction vertically.
iSQL> SELECT * FROM employees WHERE eno = 2;
ENO : 2
E_LASTNAME : Davenport
E_FIRSTNAME : Susan
EMP_JOB : designer
EMP_TEL : 0113654540
DNO :
SALARY : 1500
SEX : F
BIRTH : 721219
JOIN_DATE : 18-NOV-2009
STATUS : H
1 row selected.