Demonstrate IN versus EXISTS performance : Analyze « SQL Plus « Oracle PL / SQL






  1. Oracle PL / SQL
  2. SQL Plus
  3. Analyze

Demonstrate IN versus EXISTS performance

  
SQL>
SQL> create table big as select * from all_objects;

Table created.

SQL>
SQL> insert into big select * from all_objects;

12217 rows created.

SQL> insert into big select * from all_objects;

12217 rows created.

SQL> insert into big select * from all_objects;

12217 rows created.

SQL> insert into big select * from all_objects;

12217 rows created.

SQL> insert into big select * from all_objects;

12217 rows created.

SQL> insert into big select * from all_objects;

12217 rows created.

SQL>
SQL> create index big_idx on big(object_id);

Index created.

SQL>
SQL> create table small as select * from all_objects where rownum < 100;

Table created.

SQL> create index small_idx on small(object_id);

Index created.

SQL>
SQL> analyze table big compute statistics
  2   for table
  3   for all indexes
  4   for all indexed columns;

Table analyzed.

SQL>
SQL> analyze table small compute statistics
  2   for table
  3   for all indexes
  4   for all indexed columns;

Table analyzed.

SQL>
SQL> drop table big;

Table dropped.

SQL> drop table small;

Table dropped.

SQL>








Related examples in the same category

1.analyze materialized view
2.analyze index
3.Analyze table with primary key and foreign key
4.analyze table tableName compute statistics for table, for all indexes, for all indexed columns;
5.ANALYZE TABLE employee COMPUTE STATISTICS
6.analyze index validate structure
7.analyze table compute statistics
8.analyze table compute statistics for all indexes
9.analyze table compute statistics for a table with two indexes
10.create or replace outline and analyze table compute statistics
11.Analyze a table with/without index
12.Anayze all objects in the APPOWNER schema.
13.Analyze index on varchar2 value and date type value
14.analyze and autotrace full outer join and union
15.analyze and autotrace single column key and multi-column key
16.analyze and autotrace table with primary key
17.analyze index t_idx validate structure
18.analyze table TABLENAME compute statistics;
19.analyze table after creating index
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