oracle建索引 pctfree,pctfree对索引的影响：

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

BLEVEL LEAF_BLOCKS DISTINCT_KEYS

------- ----------- -------------

1 199 100000

可以看到第二种情况，LEAF_BLOCKS 减少了一半还要多。