NULLs Value in Index Column.

If Indexed columns (Single Column that contain null values) contain null values then the associated row is not indexed, for that indexed resulting is smaller index structure.

We can analyze schema object such as table, index and cluster to collect and manage statistics, validity of storage format, identify migrated and chained rows.

Oracle always recomand that collect statistics using DBMS_STATS package, so it is better to collect optimizer statistics using DBMS_STATS package and not by analyzing.


SQL> CREATE TABLE ACCOUNT_INFORMATION
  2  (ACC_NUMBER NUMBER,
  3   ACC_TITLE VARCHAR2(100),
  4   ACC_NARATION VARCHAR2(100)
  5   );

Table created.

Elapsed: 00:00:00.02
SQL> CREATE UNIQUE INDEX IND_ACC_INFO_AC_NUMBER ON ACCOUNT_INFORMATION(ACC_NUMBER);

Index created.

Elapsed: 00:00:00.03
SQL> CREATE INDEX IND_ACC_INFO_NARATION  ON ACCOUNT_INFORMATION(ACC_NARATION);

Index created.

Elapsed: 00:00:00.02
SQL> INSERT INTO ACCOUNT_INFORMATION (ACC_NUMBER, ACC_TITLE, ACC_NARATION)
  2  SELECT LEVEL, LEVEL||' ACC NAME TEST '||LEVEL, NULL FROM DUAL CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.34
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> ANALYZE INDEX IND_ACC_INFO_AC_NUMBER VALIDATE STRUCTURE;

Index analyzed.

Elapsed: 00:00:00.04
SQL> SELECT NAME, LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, DEL_LF_ROWS, DISTINCT_KEYS FROM INDEX_STATS;

NAME                              LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS DISTINCT_KEYS
------------------------------ ---------- ---------- ---------- ---------- ----------- -------------
IND_ACC_INFO_AC_NUMBER             100000        187        186          1           0        100000

Elapsed: 00:00:00.02
SQL> ANALYZE INDEX IND_ACC_INFO_NARATION VALIDATE STRUCTURE;

Index analyzed.

Elapsed: 00:00:00.02
SQL> SELECT NAME, LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, DEL_LF_ROWS, DISTINCT_KEYS FROM INDEX_STATS;

NAME                              LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS DISTINCT_KEYS
------------------------------ ---------- ---------- ---------- ---------- ----------- -------------
IND_ACC_INFO_NARATION                   0          1          0          0           0             0

Elapsed: 00:00:00.00
SQL>