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> --