Re: del_lf_rows_len =0 in index_stats

On Jun 19, 4:38 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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/s...
>
> 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',CAS­CADE=>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.

thanks for the post charles! had a new learning today Received on Wed Jun 20 2007 - 03:22:39 CDT