demo08.sql

来自「Expert Oracle Database Architecture 9i a」· SQL 代码 · 共 25 行

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