analyze table students compute statistics : Analyze « SQL Plus « Oracle PL / SQL






analyze table students compute statistics

  

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.

   
    
  








Related examples in the same category

1.analyze materialized view
2.analyze index
3.Analyze table with primary key and foreign key
4.Demonstrate IN versus EXISTS performance
5.analyze table tableName compute statistics for table, for all indexes, for all indexed columns;
6.ANALYZE TABLE employee COMPUTE STATISTICS
7.analyze index validate structure
8.analyze table compute statistics
9.analyze table compute statistics for all indexes
10.analyze table compute statistics for a table with two indexes
11.create or replace outline and analyze table compute statistics
12.Analyze a table with/without index
13.Anayze all objects in the APPOWNER schema.
14.Analyze index on varchar2 value and date type value
15.analyze and autotrace full outer join and union
16.analyze and autotrace single column key and multi-column key
17.analyze and autotrace table with primary key
18.analyze index t_idx validate structure
19.analyze table TABLENAME compute statistics;
20.analyze table after creating index
21.analyze table estimate statistics
22.analyze table t compute statistics for table for columns id;
23.analyze table t compute statistics for table, for all indexes, for all indexed columns