| density of the index [message #484308] |
Mon, 29 November 2010 09:43  |
 |
crocuta
Messages: 8
Registered: November 2010
Location: muenchen
|
Junior Member |
|
|
im newbie in oracle db so I have one question, can anybody clarify to me what exactly mean density of the index in
db meanings ? I mean when you configure rebuilt of the index and how do you determine the value of density?
for e.g when I have density 40 what does it mean ?
next question : how shall I determine which tables are included in index ?
thanks
|
|
|
|
|
|
| Re: density of the index [message #484311 is a reply to message #484310] |
Mon, 29 November 2010 09:53  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
DENSITY does NOT exist in V11.2
SQL> desc dba_indexes
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
VISIBILITY VARCHAR2(9)
DOMIDX_MANAGEMENT VARCHAR2(14)
SEGMENT_CREATED VARCHAR2(3)
SQL> select density from dba_indexes;
select density from dba_indexes
*
ERROR at line 1:
ORA-00904: "DENSITY": invalid identifier
SQL>
[Updated on: Mon, 29 November 2010 09:53] Report message to a moderator |
|
|
|
| Re: density of the index [message #484322 is a reply to message #484311] |
Mon, 29 November 2010 11:28 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:how do you determine the value of density?
The column density is shown in the DBA_TAB_COL_STATISTICS data dictionary view. Generally speaking, the density should be related to the reciprocal of the number of distinct values, but skewed histograms and the AUTO_SAMPLE_SIZE can distort it.
|
|
|
|
|
|
| Re: density of the index [message #484888 is a reply to message #484868] |
Fri, 03 December 2010 09:34 |
 |
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:so shall I understand that its like a feature ? its installed by deafult ?
It is not a feature it is not required in 99% of the cases.
Density in a b*tree index always converge to a natural value that depends on the usage of the table values (but in very specific cases).
When you rebuilt an index, it will go back to its natural density and during this time you will pay the cost of this internal restructuration during your DML.
So cost to rebuild, cost to go back to natural density, cost to rebuild, cost to... Just waste.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
