analyze table students compute statistics : analyze « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. analyze

SQL>
SQL> create table students
  2  ( studentID number constraint students_pk primary key,
  3    name varchar2(10) );

Table created.

SQL>
SQL> create table documentMaster
  2  ( documentId number constraint document_pk primary key,
  3    description varchar2(10) );

Table created.

SQL>
SQL> create table admission_docs
  2  ( studentID references students,
  3    documentId references documentMaster,
  4    dt date,
  5    constraint admission_pk primary key(studentID, documentId));

Table created.

SQL>
SQL>
SQL> insert into students
  2  select object_id, object_name
  3    from all_objects;


SQL>
SQL>
SQL> insert into documentMaster
  2  select ROWNUM, 'doc ' || ROWNUM
  3    from all_users
  4   where ROWNUM <= 5;

5 rows created.

SQL>
SQL> insert into admission_docs
  2  select object_id, mod(ROWNUM,3)+1, created
  3    from all_objects, (select 1 from all_users where ROWNUM <= 3);


SQL>
SQL>
SQL> analyze table students compute statistics
  2  for table for all indexes for all indexed columns;

Table analyzed.

SQL>
SQL> analyze table documentMaster compute statistics
  2  for table for all indexes for all indexed columns;

Table analyzed.

SQL>
SQL> analyze table admission_docs compute statistics
  2  for table for all indexes for all indexed columns;

Table analyzed.

SQL>
SQL>
SQL> set autotrace on
SQL> variable bv number
SQL> exec :bv := 1234

PL/SQL procedure successfully completed.

SQL>
SQL> select a.* , decode(b.dt,null,'No','Yes') submitted, b.dt
  2   from (
  3  select *
  4    from students, documentMaster
  5   where students.studentID = :bv
  6        ) a, admission_docs b
  7   where a.studentID = b.studentID(+)
  8     and a.documentId = b.documentId (+)
  9  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3775454522

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

| Id  | Operation                      | Name           | Rows  | Bytes | Cost
|

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

|   0 | SELECT STATEMENT               |                |     5 |   550 |     2
|

|   1 |  NESTED LOOPS OUTER            |                |     5 |   550 |     2
|

|   2 |   VIEW                         |                |     5 |   330 |     2
|

|   3 |    NESTED LOOPS                |                |     5 |   225 |     2
|

|   4 |     TABLE ACCESS BY INDEX ROWID| STUDENTS       |     1 |    33 |     1
|

|*  5 |      INDEX UNIQUE SCAN         | STUDENTS_PK    |     1 |       |
|

|   6 |     TABLE ACCESS FULL          | DOCUMENTMASTER |     5 |    60 |     1
|

|   7 |   TABLE ACCESS BY INDEX ROWID  | ADMISSION_DOCS |     1 |    44 |
|

|*  8 |    INDEX UNIQUE SCAN           | ADMISSION_PK   |     1 |       |
|

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


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("STUDENTS"."STUDENTID"=TO_NUMBER(:BV))
   8 - access("A"."STUDENTID"="B"."STUDENTID"(+) AND
              "A"."DOCUMENTID"="B"."DOCUMENTID"(+))

Note
-----
   - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        369  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> drop table students cascade constraints;

Table dropped.

SQL>
SQL> drop table documentMaster cascade constraints;

Table dropped.

SQL>
SQL> drop table admission_docs;

Table dropped.