connect system/manager
grant dba to scott;
connect scott/tiger



create table indextest
as
select *
from dba_objects
where owner in ('OUTLN', 'PUBLIC', 'SCOTT', 'SYS', 'SYSTEM');

 


analyze table indextest 
compute statistics;
set autotrace trace explain



select owner, object_name
from indextest
where object_name = 'DBA_INDEXES';



create index indextest_objname_idx
on indextest(object_name);
select owner, object_name
from indextest
where object_name = 'DBA_INDEXES';





set autotrace off
select owner, count(*)
from indextest
group by owner;



create index indextest_owner_idx
on indextest(owner)
/



set autotrace trace explain
select owner, object_name
from indextest
where owner = 'SYS';
select owner, object_name
from indextest
where owner = 'SCOTT';




analyze table indextest compute statistics for columns owner;
select owner, object_name
from indextest
where owner = 'SYS';
select owner, object_name
from indextest
where owner = 'SCOTT';






set autotrace off
drop table indextest;
create table indextest
as
select *
from dba_objects
where owner in ('OUTLN', 'PUBLIC', 'SCOTT', 'SYS', 'SYSTEM');
create index indextest_objname_idx
on indextest(object_name)
pctfree 0;
analyze table indextest compute statistics;


analyze index indextest_objname_idx validate structure;
select name, height, lf_blks, pct_used
from index_stats;



insert into indextest(owner, object_name)
values('AAAAAAAAAA', 'AAAAAAAAAAAAAAAAAAAAA');
commit;



analyze index indextest_objname_idx validate structure;
select name, height, lf_blks, pct_used
from index_stats;







insert into indextest(owner, object_name)
values('ZZZZZZZZZZ', 'ZZZZZZZZZZZZZZZZZZZZZ');
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_blks, pct_used
from index_stats;




alter index indextest_objname_idx
rebuild pctfree 10;
analyze index indextest_objname_idx validate structure;
select name, height, lf_blks, pct_used
from index_stats;








insert into indextest(owner, object_name)
values('AAAAAAAAAA', 'AAAAAAAAAAAAAAAAAAAAA');
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_blks, pct_used
from index_stats;









insert into indextest(owner, object_name)
values('ZZZZZZZZZZ', 'ZZZZZZZZZZZZZZZZZZZZZ');
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_blks, pct_used
from index_stats;



set autotrace off
drop table indextest;
create table indextest
as
select *
from dba_objects
where owner in ('OUTLN', 'PUBLIC', 'SCOTT', 'SYS', 'SYSTEM');
create index indextest_objname_idx
on indextest(object_name)
pctfree 10;


analyze table indextest compute statistics;





analyze index indextest_objname_idx validate structure;
select name, height, lf_rows, del_lf_rows, pct_used
from index_stats;
update indextest
set object_name = 'DBA_INDEXES2'
where object_name = 'DBA_INDEXES';
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_rows, del_lf_rows, pct_used
from index_stats;




delete from indextest
where object_name like 'ALL_T%';
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_rows, del_lf_rows, pct_used
from index_stats;



insert into indextest(owner, object_name)
values('ZZZZZ', 'ZZZ_INSERT');
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_rows, del_lf_rows, pct_used
from index_stats;




insert into indextest(owner, object_name)
values('ZZZZZ', 'ALL_TESTINSERT');
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_rows, del_lf_rows, pct_used
from index_stats;





insert into indextest(owner, object_name)
values('ZZZZZ', 'DBA_INDEX');
commit;
analyze index indextest_objname_idx validate structure;
select name, height, lf_rows, del_lf_rows, pct_used
from index_stats;