demo08.sql

Expert Oracle Database Architecture 9i and 10g sql源码

create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
alter table dept add constraint dept_pk primary key(deptno);

create bitmap index emp_bm_idx
on emp( d.dname )
from emp e, dept d
where e.deptno = d.deptno
/
begin
dbms_stats.set_table_stats( user, 'EMP', 
                           numrows => 1000000, numblks => 300000 );
dbms_stats.set_table_stats( user, 'DEPT',
                           numrows => 100000, numblks => 30000 );
end;
/

set autotrace traceonly explain
select count(*)
from emp, dept
where emp.deptno = dept.deptno
and dept.dname = 'SALES'
/