pctfree对索引的影响：

最新推荐文章于 2022-03-23 22:23:40 发布

cqcj2023

最新推荐文章于 2022-03-23 22:23:40 发布

阅读量328

点赞数

Only applies to CREATE INDEX statement.
Not used for subsequent inserts
（先建索引，再插入数据（不受pctfree的影响）。与先插入数据，再建索引（受pctfree的影响）。这两种情况对空间使用完全不同）
例如：先插入数据，再建索引
CREATE TABLE t1 (c01 NUMBER);
Insert 100000 rows
CREATE INDEX i1 ON t1 (c01)
PCTFREE 50
先建索引，再插入数据
CREATE TABLE t1 (c01 NUMBER);
CREATE INDEX i1 ON t1 (c01)
PCTFREE 50;
Insert 100000 rows

试验：先插入数据，再建索引

SQL> create table t6(c1 number);

Table created.
1 declare
2 c number;
3 begin
4 for i in 1..100000 loop
5 c:=i;
6 insert into t6 values(c);
7 end loop;
8 commit;
9* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> create index t6_ind on t6(c1)
2 pctfree 50;

Index created.

SQL> analyze table t6 compute statistics for all indexes;

Table analyzed.

SQL> select * from dba_indexes
2 where table_name='T6';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------ ----------- -------------
1 408 100000
先建索引，再插入数据
SQL> create table t7(c1 number);
SQL> create index t7_ind on t6(c1)
2 pctfree 50;
1 declare
2 c number;
3 begin
4 for i in 1..100000 loop
5 c:=i;
6 insert into t6 values(c);
7 end loop;
8 commit;
9* end;
SQL> /
analyze table t7 compute statistics for all indexes;
SQL> select * from dba_indexes
2 where table_name='T7';