SQL Plus Input Ouput

Input to sqlplus

set verify off
set serveroutput on
accept new_dept_name char prompt 'Enter new department name '
declare
dept_name varchar2(100);
begin
dept_name := &new_dept_name; -- assign the value taken from user
end;
/

SET AUTOPRINT ON
variable maximum_department_number number
declare
max_dept_num number;
begin
select max(deptno) into max_dept_num from dept;
:maximum_department_number := max_dept_num; -- pl/sql value assigned to sqlplus variable
end;
/

set autoprint print the sqlplus variable at the end of the execution
to explicitly print

print maximum_department_number

SQL Design Patterns

Same table refered twice in a multi level subquery.

SELECT calc_scheme.scheme_id
FROM calc_scheme
WHERE NOT EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id)
OR EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id
   AND 'FLT' = calc_rule_product.code
   AND 'FLT' IS NOT NULL)

Becomes

SELECT calc_scheme.scheme_id
FROM calc_scheme,
    (SELECT scheme_id,
     code
   FROM calc_rule_product)
x
WHERE(calc_scheme.scheme_id = x.scheme_id(+)
 AND x.rowid IS NULL) OR(x.scheme_id = calc_scheme.scheme_id
 AND 'FLT' = x.code
 AND 'FLT' IS NOT NULL) 

For WHERE EXISTS single table sub-queries, try a Semi-Join. If this doesn't work, try rewriting the query as a join

SELECT *
FROM   table_a
WHERE  EXISTS (
       SELECT 1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
       AND    b.key1 = a.key1);

becomes

SELECT a.*
FROM   table_a a
, (    SELECT DISTINCT b.key1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
  ) d
WHERE  a.key1 = d.key1

For WHERE NOT EXISTS sub-queries, try re-writing the SQL as an outer join with a b.ROWID IS NULL clause. eg.

SELECT a.*
FROM   table_a a
WHERE  NOT EXISTS (
       SELECT 1
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a , table_b b
WHERE  a.col1 = b.col1 (+)
AND    b.rowid IS NULL

For IN and NOT IN sub-queries, eliminate correlated sub-queries, they must use a Nested Loops join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 IN (
       SELECT col2
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a
WHERE  (col1, col2) IN (
       SELECT col2
       FROM   table_b)

For IN sub-queries that are not correlated, try an ORDERED hint and a USE_HASH or USE_MERGE hint. eg:

SELECT /*+ ORDERED USE_HASH(a)*/ *
FROM table_a a
WHERE col1 IN (
      SELECT col1
      FROM   table_b b)

In this way, Oracle can run the outer query and the sub-query independently and perform a hash join.


For correlated col = (sub-query) sub-queries, Oracle must use a Filter. Try to re-write as a table join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 = (
       SELECT max(col2)
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a,
(      SELECT col1, max(col2)
       FROM   table_b
       GROUP  BY col1
) b
WHERE  a.col1 = b.col1

NOLOGGING

NOLOGGING tablespaces
For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo
generation can improve performance, and may be appropriate for easily recoverable operations.

An example of such an operation is a CREATE TABLE...AS SELECT statement, which can be repeated in case of database or instance failure. Specify the NOLOGGING clause in the CREATE TABLESPACE statement if you wish to suppress redo when these operations are performed for objects within the tablespace.

If you do not include this clause, or if you specify LOGGING instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute. The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING or NOLOGGING at the schema object level--for example, in a CREATE TABLE statement.

If you have a standby database, NOLOGGING mode causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify FORCE LOGGING mode. When you include the FORCE LOGGING clause in the CREATE TABLESPACE statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.

If you transport a tablespace that is in FORCE LOGGING mode to another database, the new tablespace will not maintain the FORCE LOGGING mode.

Consider Using NOLOGGING When Creating Tables
To create a table most efficiently use the NOLOGGING clause in the CREATE TABLE...AS SELECT statement. The NOLOGGING clause causes minimal redo information to be generated during the table creation. This has the following benefits:

* Space is saved in the redo log files.
* The time it takes to create the table is decreased.
* Performance improves for parallel creation of large tables.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader
and direct load INSERT operations are not logged. Subsequent DML statements
(UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

If you cannot afford to lose the table after you have created it (for example, you will no longer have access to the data used to create the table) you should take a backup immediately after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

In general, the relative performance improvement of specifying NOLOGGING is greater for larger tables than for smaller tables. For small tables, NOLOGGING has little effect on the time it takes to create a table. However, for larger tables the performance improvement can be significant, especially when you are also parallelizing the table creation.