analyze table after creating index : analyze « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

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

SQL>
SQL>
SQL>
SQL> create table myTable1 as select *
  2    from all_objects where ROWNUM <=000;

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> create table myTable2 as select *
  2   from all_objects where ROWNUM <= 9950;

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> create index myTable2_idx on myTable2(object_id);

Index created.

SQL>
SQL>
SQL> select count(*) from myTable1 rbo
  2   where object_id not in ( select object_id from myTable2 )
  3   /

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

SQL>
SQL>
SQL>
SQL> select count(*) from myTable1 rbo
  2    where NOT EXISTS (select null from myTable2 where myTable2.object_id = rbo.object_id )
  3   /

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

SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from myTable1, myTable2 rbo
  2    where myTable1.object_id = rbo.object_id(+) and rbo.object_id IS NULL
  3   /

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

SQL>
SQL>
SQL>
SQL>
SQL> analyze table myTable1 compute statistics;

Table analyzed.

SQL>
SQL>
SQL>
SQL> analyze table myTable2 compute statistics;

Table analyzed.

SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from myTable1 cbo
  2   where object_id not in ( select object_id from myTable2 )
  3   /

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

SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from myTable1 cbo
  2   where object_id not in ( select object_id from myTable2 )
  3   /

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

SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from myTable1 cbo
  2   where object_id not in ( select object_id from myTable2 )
  3   /

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

SQL>
SQL>
SQL>
SQL>
SQL> alter table myTable2 modify object_id null;

Table altered.

SQL>
SQL>
SQL>
SQL> select count(*)
  2   from
  3    myTable1 cbo where object_id not in ( select object_id from myTable2 )
  4   /

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

SQL>
SQL>
SQL>
SQL>
SQL> drop table myTable1;

Table dropped.

SQL>
SQL>
SQL>
SQL> drop table myTable2;

Table dropped.

SQL>
SQL>
SQL>
SQL>