数据库索引结构与数据访问原理培训.pdfVIP

Indexes

Indexstructure

Attheendofthisunityouwillbeabletodescribe:

Whatanindexis

HowanIndexisstructured

Howdataisaccessedusinganindex

R

◼Onceyouhavecompletedthisunit,youwillknow:

Whatanindexis

Howanindexisstructured

Howdataisaccessedusinganindex

HowtheOraclerule-basedoptimizerchoosesanindex

Tablestatistics

____

AUSP____1

R

◼Todeterminewhichaccessstrategyandindexesareused,acostbasedoptimizerusestheinformationfromthe:

Requesteddata

WHEREclause

Selectivityoftheindexedfields

◼Thedatabaseadministratormustensurethatthetablestatisticsfortheselectivitiesofindexedcolumnsandsizes

oftabandindexesareup-to-date.

◼Thedevelopermustdesignthequeriessothattheyaresupportedbytheexistingindexes.

Primaryindex

Secondaryindex

Uniquesecondaryindex

R

Tableblock

Root

blocks

Branch

blocks

Leaf

blocks

R

◼Anindexconsistsofoneormorefieldsofatable.

◼EveryrowinthetableisidentifiedbytheROWID,whichisalsothe