Mega Code Archive
Analyze table compute statistics for table for all indexes for all indexed columns
<![CDATA[
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> 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> 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>
]]>