--
-- By analyzing the tables, you are collecting the statistics.
-- Before analyze the stats are not collected.
-- after analyze you udpate the stats (if it is already collected).
-- These stats are used by the CBO to identify and lay a shortest path 
-- or 'route' to fetch the data.
-- So by course of time, the data changes in base table.(deletes,updates,inserts).
-- But the CBO still works on the stats you have collected BEFORE.
-- So if you will update the stats, the CBO will understand the changes that has happened.
-- PLEASE follow the session

--
-- Lets create a small table
--

mag@mutation_mutation > create table mytable as select * from dba_segments;

Table created.

--
-- set the tracing options to seeting plan
-- You can see 1322 rows are returned by sql
-- Plan show you nothing about rows returned...
--

mag@mutation_mutation > set autotrace on explain
mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
      1322

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE'

--
-- Lets analyze the table
--

mag@mutation_mutation > analyze table mytable compute statistics;

Table analyzed.

--
-- run the same sql again
-- Now the plan shows  something called COST and CARD
-- card=1322 means, CBO got these stats based on prior analyze.
-- To fetch 1322 rows the cost is 3.
 

mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
      1322

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=3 Card=1322)

--
-- Now, lets truncate the table.
-- run the same query.
-- You will find something intresting.
-- even though all records are gone, the CBO still thinks there are some records in table.
-- SO the cost is generated based on that,
-- and THE plan is generated based on that.
-- THis is becuase the STATISTICS was outdated .!!!

mag@mutation_mutation > truncate table mytable;

Table truncated.

mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=3 Card=1322)

--
-- Now lets analyze the table and run the same query.
-- with updated statistics
-- The CARD is low and Is the COST.
-- NOTE: This example doesnt deal with real world. SO the plan does not change much.
--

mag@mutation_mutation > analyze table mytable compute statistics;

Table analyzed.

mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=2 Card=1)

SCOTT>
SCOTT>drop table tst
  2  /
drop table tst
           *
ERROR at line 1:
ORA-00942: table or view does not exist 

SCOTT>-- Create table with only 1 value for ID
SCOTT>create table tst as select 1 ID, object_name from all_objects
  2  /

Table created.

SCOTT>
SCOTT>-- insert 1 other value for ID
SCOTT>insert into tst values(2, 'TESTING')
  2  /

1 row created.

Execution Plan
----------------------------------------------------------                      
   0      INSERT STATEMENT Optimizer=CHOOSE                                     

SCOTT>
SCOTT>-- Create index on ID
SCOTT>create index tst_i on tst(id);

Index created.

SCOTT>
SCOTT>-- Make sure we use CBO
SCOTT>alter session set optimizer_mode=choose
  2  /

Session altered.

SCOTT>
SCOTT>-- Autotrace on with explain plan, not interested in results
SCOTT>-- so trace_only
SCOTT>set autotrace trace explain
SCOTT>
SCOTT>-- Select the odd value; should use the index
SCOTT>select * from tst where id = 2
  2  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE                                     
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TST' (TABLE)                      
   2    1     INDEX (RANGE SCAN) OF 'TST_I' (INDEX)                             

SCOTT>
SCOTT>-- Select with ID = 1 should NOT use index; Full table scan is
SCOTT>-- much more efficient
SCOTT>select * from tst where id = 1
  2  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE                                     
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TST' (TABLE)                      
   2    1     INDEX (RANGE SCAN) OF 'TST_I' (INDEX)                             

SCOTT>
SCOTT>-- CBO does not know the skewness of the data. Analyze will help this
SCOTT>analyze table tst compute statistics for table for all indexes for all indexed columns
  2  /

Table analyzed.

SCOTT>
SCOTT>-- And again our two selections
SCOTT>select * from tst where id = 2
  2  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=19)            
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TST' (TABLE) (Cost=2 Car          
          d=1 Bytes=19)                                                         
                                                                                
   2    1     INDEX (RANGE SCAN) OF 'TST_I' (INDEX) (Cost=1 Card=1)             

SCOTT>
SCOTT>select * from tst where id = 1
  2  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=58 Card=39042 Bytes=          
          741798)                                                               
                                                                                
   1    0   TABLE ACCESS (FULL) OF 'TST' (TABLE) (Cost=58 Card=39042 B          
          ytes=741798)                                                          
                                                                                

SCOTT>
SCOTT>spool off