analyze table compute statistics for table for all indexes for all indexed columns : analyze « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

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

SQL>
SQL>
SQL> create table myTable as
  2  select decode( mod(rownum,100), 0, 'N', 'Y' ) processed, a.*
  3    from all_objects a;

Table created.

SQL>
SQL> create index processed_idx on myTable(processed);

Index created.

SQL>
SQL> select * from myTable where status = 'N';

no rows selected

SQL>
SQL>
SQL> analyze table myTable compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns
  5  /

Table analyzed.

SQL>
SQL> variable processed varchar2(1);
SQL> exec :processed := 'N'

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select *
  2    from myTable
  3   where processed = 'N';

Execution Plan
----------------------------------------------------------
Plan hash value: 362789735

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
)| Time     |

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

|   0 | SELECT STATEMENT            |               |   126 | 11466 |     4   (0
)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MYTABLE       |   126 | 11466 |     4   (0
)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | PROCESSED_IDX |   126 |       |     1   (0
)| 00:00:01 |

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


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

   2 - access("PROCESSED"='N')

SQL>
SQL>
SQL> select *
  2    from myTable
  3   where processed = :processed;

Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  6328 |   562K|    47   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYTABLE |  6328 |   562K|    47   (3)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter("PROCESSED"=:PROCESSED)

SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL> set autotrace off
SQL>
SQL>