RE: Index not used

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Mark.Bobak@xxxxxxxxxxxxxxx
  • Date: Tue, 16 May 2006 19:26:20 -0600

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


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


Other related posts: