Re: del_lf_rows_len =0 in index

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 19 Jun 2007 04:38:34 -0700
Message-ID: <1182253114.266513.199310@c77g2000hse.googlegroups.com>

On Jun 19, 5:27 am, sd2611 <sd2..._at_gmail.com> wrote:
> What does the value of 0 in the del_lf_rows_len in index_stats signify
> or say what does this value mean.

A quick Google search finds these articles:

  http://www.jlcomp.demon.co.uk/indexes.doc
  http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf
  http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4214.htm

The last of the above links includes this information: "The ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE statement must be used in order to collect statistics."

A quick test:
Create a test table:
CREATE TABLE T1 (

  C1 VARCHAR2(40),
  C2 VARCHAR2(40),
  PRIMARY KEY(C1));

Insert 100,000 rows with random data:
INSERT INTO T1
SELECT
  DBMS_RANDOM.STRING('A',40),
  DBMS_RANDOM.STRING('A',10)
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT; Gather table and index stats:
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'table_owner_here',TABNAME=>'T1',CASCADE=>TRUE);

Find the primary key index name:
SELECT
  *
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1';

SYS_C0035927 Check the INDEX_STATS view:
SELECT
  *
FROM
  INDEX_STATS
WHERE
  NAME='SYS_C0035927'; 0 Rows Selected

Use the ANALYZE command against the index: ANALYZE INDEX SYS_C0035927 VALIDATE STRUCTURE; Check again:
SELECT
  LF_ROWS,
  DEL_LF_ROWS,
  DEL_LF_ROWS_LEN,
  DISTINCT_KEYS
FROM
  INDEX_STATS
WHERE
  NAME='SYS_C0035927';    LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS ---------- ----------- --------------- -------------

100000 0 0 100000

Delete a row from the table:
DELETE FROM
T1
WHERE
C1='mZTKmbUaiBkZuJpQYTkAeUxFVkVoywAzwaQASHMz';

COMMIT; ANALYZE INDEX SYS_C0035927 VALIDATE STRUCTURE; SELECT
  LF_ROWS,
  DEL_LF_ROWS,
  DEL_LF_ROWS_LEN,
  DISTINCT_KEYS
FROM
  INDEX_STATS
WHERE
  NAME='SYS_C0035927';    LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS ---------- ----------- --------------- -------------

100000 1 51 100000

DELETE FROM
  T1
WHERE
  C1 LIKE '%IT%'; 1,479 ROWS DELETED COMMIT; Check again:
SELECT
  LF_ROWS,
  DEL_LF_ROWS,
  DEL_LF_ROWS_LEN,
  DISTINCT_KEYS
FROM
  INDEX_STATS
WHERE
  NAME='SYS_C0035927';    LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS ---------- ----------- --------------- -------------

100000 1 51 100000

Note that there was no change!

ANALYZE INDEX SYS_C0035927 VALIDATE STRUCTURE; Check again:
SELECT
  LF_ROWS,
  DEL_LF_ROWS,
  DEL_LF_ROWS_LEN,
  DISTINCT_KEYS
FROM
  INDEX_STATS
WHERE
  NAME='SYS_C0035927';    LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS ---------- ----------- --------------- -------------

99999 1479 75429 99999

Hopefully, the above will allow you to draw your own conclusion about the purpose of the DEL_LF_ROWS_LEN column.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 19 2007 - 06:38:34 CDT

Re: del_lf_rows_len =0 in index_stats