demo11.sql

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

create global temporary table temp_table_session
on commit preserve rows
as
select * from scott.emp where 1=0
/
create global temporary table temp_table_transaction
on commit delete rows
as
select * from scott.emp where 1=0
/
insert into temp_table_session select * from scott.emp;
insert into temp_table_transaction select * from scott.emp;
select session_cnt, transaction_cnt
  from ( select count(*) session_cnt from temp_table_session ),
       ( select count(*) transaction_cnt from temp_table_transaction );
commit;
select session_cnt, transaction_cnt
  from ( select count(*) session_cnt from temp_table_session ),
       ( select count(*) transaction_cnt from temp_table_transaction );
disconnect
connect /
select session_cnt, transaction_cnt
  from ( select count(*) session_cnt from temp_table_session ),
       ( select count(*) transaction_cnt from temp_table_transaction );
create global temporary table gtt
as
select * from scott.emp where 1=0;
insert into gtt select * from scott.emp;
set autotrace traceonly explain
select /*+ first_rows */ * from gtt;
select /*+ first_rows dynamic_sampling(gtt 2) */ * from gtt;
set autotrace off

disconnect
connect /
insert into gtt select * from scott.emp;
set autotrace traceonly explain
select * from gtt;
set autotrace off
create table emp as select * from scott.emp;
create global temporary table gtt1 ( x number )
on commit preserve rows;
create global temporary table gtt2 ( x number )
on commit delete rows;
insert into gtt1 select user_id from all_users;
insert into gtt2 select user_id from all_users;
exec dbms_stats.gather_schema_stats( user );
select table_name, last_analyzed, num_rows from user_tables;
insert into gtt2 select user_id from all_users;
exec dbms_stats.gather_schema_stats( user, gather_temp=>TRUE );
select table_name, last_analyzed, num_rows from user_tables;
create global temporary table t ( x int, y varchar2(100) );
begin
   dbms_stats.set_table_stats( ownname => USER,
                               tabname => 'T',
                               numrows => 500,
                               numblks => 7,
                               avgrlen => 100 );
end;
/
select table_name, num_rows, blocks, avg_row_len
             from user_tables
            where table_name = 'T';