从底向上第七篇--超255列表的存储

sundog315 · 发表于 2010-7-1 19:56

用存储过程测试，依然是读两次：

SQL> truncate table t_256;

表被截断。

SQL> BEGIN
  2 EXECUTE IMMEDIATE 'INSERT INTO T_256 VALUES (' || LPAD('1,', 510, '1,') || '1)';
  3 COMMIT;
  4  END;
  5  /

PL/SQL 过程已成功完成。

SQL> analyze table t_256 compute statistics;

表已分析。

SQL> select num_rows,blocks,empty_blocks,avg_space,avg_row_len,chain_cnt from us
er_tables where table_name='T_256';

  NUM_ROWS    BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ------------ ---------- ----------- ----------
      1       5          3    7912       777       0

SQL> select rowid from t_256;

ROWID
------------------
AAAQ09AAEAAAACOAAB

SQL> set serveroutput on
SQL> declare
  2 -- Local variables here
  3 i integer;
  4 j integer;
  5 tbl_tmp  t_256%rowtype;
  6  begin
  7 -- Test statements here
  8 select t.VALUE
  9    into i
10    from v$mystat t, v$statname n
11    where t.STATISTIC# = n.STATISTIC#
12    and n.NAME = 'consistent gets';
13
14 select * into tbl_tmp from t_256 where rowid='AAAQ09AAEAAAACOAAB';
15
16 select t.VALUE
17    into j
18    from v$mystat t, v$statname n
19    where t.STATISTIC# = n.STATISTIC#
20    and n.NAME = 'consistent gets';
21
22 dbms_output.put_line(j-i);
23  end;
24  /
2

PL/SQL 过程已成功完成。

SQL> /
2

PL/SQL 过程已成功完成。

SQL> /
2

PL/SQL 过程已成功完成。

SQL> select t.VALUE
  2    from v$mystat t, v$statname n
  3    where t.STATISTIC# = n.STATISTIC#
  4    and n.NAME = 'consistent gets';

   VALUE
----------
362032

SQL> select t.VALUE
  2    from v$mystat t, v$statname n
  3    where t.STATISTIC# = n.STATISTIC#
  4    and n.NAME = 'consistent gets';

   VALUE
----------
362032