RE: Index not used

Mark, and all

At 03:52 PM 5/16/2006, Bobak, Mark wrote:
>Hmm....those seem to be high clustering factors, but, to be sure, we
>need to know how many rows are in t2, and how many blocks in t2 are
>below the HWM. The clustering factor will always range between the
>number of blocks below the HWM for the table (good) to number of
>rows in the table (bad).

Slight correction. While I can't imagine a scenario where the clustering factor will be higher than the number of (indexed) rows, I can easily create a scenario where it is less than the number of blocks below the HWM:

SQL> create table mark( c1 number, c2 varchar2(4000)); SQL> insert into mark select rownum, rpad('A',3500,'*') from dba_objects where rownum <= 5000;
SQL> delete from mark;
SQL> insert into mark select rownum, rpad('A',3500,'*') from dba_objects where rownum <= 1;

SQL> commit;
SQL> create index mark on mark(c1);
SQL> @gather_table_stats mark 100

SQL> exec print_table('select * from user_tables where table_name = ''MARK''');
TABLE_NAME                    : MARK
TABLESPACE_NAME               : USERS
CLUSTER_NAME                  :
IOT_NAME                      :
PCT_FREE                      : 10
PCT_USED                      :
INI_TRANS                     : 1
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
LOGGING                       : YES
BACKED_UP                     : N
NUM_ROWS                      : 1
BLOCKS                        : 2512
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 3504
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
DEGREE                        :          1
INSTANCES                     :          1
CACHE                         :     N
TABLE_LOCK                    : ENABLED
SAMPLE_SIZE                   : 1
LAST_ANALYZED                 : 2006-05-16 19:16:33
PARTITIONED                   : NO
IOT_TYPE                      :
TEMPORARY                     : N
SECONDARY                     : N
NESTED                        : NO
BUFFER_POOL                   : DEFAULT
ROW_MOVEMENT                  : DISABLED
GLOBAL_STATS                  : YES
USER_STATS                    : NO
DURATION                      :
SKIP_CORRUPT                  : DISABLED
MONITORING                    : YES
CLUSTER_OWNER                 :
DEPENDENCIES                  : DISABLED
COMPRESSION                   : DISABLED
DROPPED                       : NO
-----------------

SQL> exec print_table('select * from user_indexes where table_name = ''MARK''');

INDEX_NAME                    : MARK
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : SCOTT
TABLE_NAME                    : MARK
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 0
LEAF_BLOCKS                   : 1
DISTINCT_KEYS                 : 1
AVG_LEAF_BLOCKS_PER_KEY       : 1
AVG_DATA_BLOCKS_PER_KEY       : 1
CLUSTERING_FACTOR             : 1
STATUS                        : VALID
NUM_ROWS                      : 1
SAMPLE_SIZE                   : 1
LAST_ANALYZED                 : 2006-05-16 19:16:33
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
IOT_REDUNDANT_PKEY_ELIM       : NO
DROPPED                       : NO
-----------------

Not that it matters in this case, but for the record, this was on a 10.1.0.4 system with an 8K blocksize and the tablespace users has the standard blocksize (8K) and is freelist space managed.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l