analyze table after creating index

  
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>

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 estimate statistics
21.	analyze table students compute 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

analyze table after creating index : Analyze « SQL Plus « Oracle PL / SQL

Related examples in the same category