Setting Up AUTOTRACE in SQL*Plus

AUTOTRACE in SQL*Plus:

SQL*Plus provides an AUTOTRACE facility that allows us to see the execution plans of the queries we’ve executed and the resources they used. The report is generated after successful SQL DML.There is more than one way to configure the AUTOTRACE facility,

But the following is a recommended route:

1. Access cd $ORACLE_HOME/rdbms/admin.

2. Log into SQL*Plus as any user with CREATE TABLE and CREATE PUBLIC

SYNONYM privileges.

3. Run @UTLXPLAN to create a PLAN_TABLE for use by AUTOTRACE.

4. Run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE, so that everyone

can access this table without specifying a schema.

5. Run GRANT ALL ON PLAN_TABLE TO PUBLIC, so that everyone can use this

table.

6. Exit SQL*Plus and change directories as follows:

cd $ORACLE_HOME/sqlplus/admin.

7. Log into SQL*Plus as a SYSDBA.

8. Run @PLUSTRCE.

9. Run GRANT PLUSTRACE TO PUBLIC.

You can test your setup by enabling AUTOTRACE and executing a simple query:

SQL> set AUTOTRACE traceonly

SQL> select * from emp, dept

2 where emp.deptno=dept.deptno;

14 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 MERGE JOIN

Setting Up

xxii

2 1 SORT (JOIN)

3 2 TABLE ACCESS (FULL) OF 'DEPT'

4 1 SORT (JOIN)

5 4 TABLE ACCESS (FULL) OF 'EMP'

Statistics

----------------------------------------------------------

0 recursive calls

8 db block gets

2 consistent gets

0 physical reads

0 redo size

2144 bytes sent via SQL*Net to client

425 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> set AUTOTRACE off

Arithmetic Expressions in SQL

=> What is Arithmetic in SQL?

  

   Arithmetic in SQL includes

    + Addition

    -  Subtraction

    * Multiplication

    / Division

 We use arithmetic operators in any clause of a SQL statement except the from clause 

=> Using arithmetic expressions in select statement

 For example assume if we want to calculate salary increase by 400 you will write below select statement

SELECT EMPNO, DEPT, SAL, SAL+400

FROM EMP;

EMPNO    SAL     SAL+400

-------    ---------- ----------

   7369        800        1200

   7499       1600       2000

   7521       1250       1650

   7566       2975       3375

   7654       1250       1650

   7698       2850       3250

   7782       2450       2850

=>Rules of Precedence:

In SQL statements, arithmetic expressions are evaluated according to a standard order of operations.

Multiplication and division take priority over addition and subtraction.

If operators within an expression are of the same priority then the expression is evaluated from left to right.

=> Using Parentheses:

We use parentheses to change the order of evolution.    

EXAMPLE:

4*(5+5)=40

(5+5) Evaluate first

4*     Evaluate second

=>Arithmetic Expression with NULL Values

If we perform arithmetic on null values, the result is NULL

 EXAMPLE

10+NULL=NULL

10- NULL=NULL

NULL*10=NULL

NULL/10=NULL

10+0=10

10-0=10

0*10=10

0/10=10

Constraints in Oracle Databases

Constraints are used to prevent invalid data entry into a table. We add and remove constraints after creating a table. Constraints ensure that data in a table adhere to the recognized business rules.

=>Referential integrity

Referential integrity is to establish a relationship between the data in the foreign key column in the child table with data in the primary key column in the parent table.

=>Constraint types:

1)    NOT NULL: ensures the column always contains a value.

2)    UNIQUE: ensures that a column contains unique values.

3)    PRIMARY KEY: column serves as the unique identifier for the table and is similar to a unique key, except that it cannot contain a NULL value. Table can have only one primary key.

4)    FOREIGN KEY: it establishes a relationship between the child column and parent column.

5)    CHECK: defines a condition that each row in a table must satisfy.

=>COLUMN –LEVEL CONSTRAINT:

It references a single column and is defined within the specifications of the column that it constrains.

Not null constrain is must be defined at the column level.

=>Table Level Constraint:

Defined after the definitions of the columns and references one or more columns. All constraint can be defined to a table level exclude NOT NULL constrains defined at a column level. You can disable a constraint without removing it when you create constraint is important to name the constraint

at the time you create it otherwise the server assign it a name.

=>How to Add Constraints:

By using alter table with ADD CONSTRAINT clause you can add constraint to an offered table.

For example

ALTER TABLE NEWEMP

ADD CONSTRAINT newemp_abc_pk

PRIMARY KEY (ABC);

-------------------------------------------------------------------------------------------------------------------

=>Drop Constraint

By using ALTER TABLE statement you can drop constraint, For example

ALTER TABLE NEWEMP

DROP CONSTRAINT newemp_abc_pk;

If you want drop primary key you don’t need to determine constraint name you can write

PRIMARY KEY only.

Cascade:

The optional cascade disables all dependent integrity constraints. that option applies to

PRIMARY KEY constraints.

It causes the dependent FOREIGN. For example

ALTER TABLE DEPT

Disable CONSTRAINT DEPTNO

Cascade;

If you enable the primary key, the foreign key is not automatic r-enabled you must re-enable the foreign key in 

a separate alter table statement.

--------------------------------------------------------------------------------------------------------------------------

=>How to Enable Constraint:

You can enable constraint after disabled it by ALTER TABLE statement. For example

ALTER TABLE DEPT

ENABLE CONSTRAINT DEPTNO;