Create two indexes on one table and check the space : Table space « Table « Oracle PL / SQL

1. Oracle PL / SQL
2. Table
3. Table space

Create two indexes on one table and check the space

  
SQL>
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> create table t
  2  nologging
  3  as
  4  select * from all_objects;

Table created.

SQL>
SQL> create index t_idx_1 on t(owner,object_type,object_name)
  2  nologging pctfree 0;

Index created.

SQL>
SQL> create index t_idx_2 on t(object_name,object_type,owner)
  2  nologging pctfree 0;

Index created.

SQL>
SQL> exec show_space( 'T_IDX_1', user, 'INDEX' );
Free Blocks
0
Total Blocks
72
Total Bytes
589824
Unused Blocks
6
Unused Bytes
49152
Last Used Ext FileId
1
Last Used Ext BlockId
42897
Last Used Block
2

PL/SQL procedure successfully completed.

SQL> exec show_space( 'T_IDX_2', user, 'INDEX' );
Free Blocks
0
Total Blocks
72
Total Bytes
589824
Unused Blocks
6
Unused Bytes
49152
Last Used Ext FileId
1
Last Used Ext BlockId
42969
Last Used Block
2

PL/SQL procedure successfully completed.

SQL>
SQL> alter session set sql_trace=true;

Session altered.

SQL>
SQL> declare
  2     cnt int;
  3  begin
  4     for x in ( select owner, object_type, object_name from t )
  5     loop
  6             select count(*) into cnt
  7               from t
  8              where object_name = x.object_name
  9                and object_type = x.object_type
 10                and owner = x.owner;
 11
 12             select count(*) into cnt
 13               from t
 14              where object_name = x.object_name
 15                and object_type = x.object_type
 16                and owner = x.owner;
 17     end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table t;

Table dropped.

SQL>
SQL> --

   
  

Related examples in the same category