Mega Code Archive

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