CRÈDIT: Bases de Dades. UNITAT 2: Administració d'Oracle
Tema: Creació d'objectes índex

Contingut:

1. Introducció teòrica

Índexs: per a què serveixen?
Accelerar l'accés a les dades amb un índex
Els índexs són llistes classificades
Els índexs poden garantir l'unicitat de les dades
Un índex per varies columnes: índexs concatenats
Disseny d'índexs: orientacions

2. Pràctica de creació d'un índex

Índexs: per a què serveixen?

• De forma semblant a com l'índex en un llibre ajuda a buscar ràpidament la informació, l'índex associat a una taula accelera la seva consulta.

• Les situacions en les que serà útil definir un índex per a una taula són les següents:

• Quan accedim freqüentment a una columna d'una taula molt gran
• Quan interesa recuperar les dades de la taula classificades segons una columna
• Quan volem garantir la unicitat de les dades en una columna

Accelerar l'accés a les dades amb un índex
 

• Suposeu que teniu una taula amb 30 columnes i 10.000 files, de forma que cada fila ocupa un promig de 600 bytes (20 caracters per columna).  Suposem tambés que hi ha una columna que es la més consultada, per exemple un número de la seguretat social o un NIF. Aleshores cada cop que volem trobar una dada en aquest camp Oracle ha de llegir tota la taula, fila per fila, de forma sequencial. Si per exemple tenim definit un buffer de lectura per cada taula de 4 Kb (un valor raonable) aleshores llegirem 4 x 1024 / 600 = 6 files en cada acces a disc. Com a mitjana, per trobar la dada que volem, necesitarem (10.000/2) / 6 = 834 accesos al disc.

• Si tenim creat un índex per aquesta columna, Oracle crea una especie de "minitaula" que només conté el camp que ens interessa i un punter que apunta a la fila de la taula on estan la resta de les dades. Aleshores si el camp ocupa 20 bytes, en cada accés a disc recuperarem 4 x 1024 / 20 = 204 files, i per tant el nombre d'accesos a disc en búsqueda sequencial serà de (10.000/2) /204 = 24 accesos. Aixó representa un factor de 834/24 = 34 més ràpid que sense índex.

• De fet, com l'índex està organitzat en forma d'arbre B, la recerca no será sequencial i encara guanyem més velocitat. Amb 10.000 files és habitual trobar la dada que volem en només de 4 a 10 accesos a disc, independentment del tamany de la taula!

• La "minitaula" de l'índex i la taula original quedan automàticament vinculades, de forma que allò que li pasi a un (modificacions, supresions, etc.) també li pasarà a l'altre.

• Quan efectuem una consulta SQL sobre un camp indexat d'una taula Oracle utilitza automàticament l'índex per accelerar la consulta. No cal especificar res especial en SQL.

Els índexs són llistes classificades
 

• Quan inserim una fila en una taula, les dades s'enmagatzemen en la següent posició física disponible, sense respectar cap ordre lògic.

• En canvi un índex sempre está classificat per la columna que indexa. Aquest fet a més a més d'accelerar la recerca de dades ens permet d'obtenir llistats de la taula classificats segons el camp indexat sense necessitat de cap algorisme d'ordenació.

Els índexs poden garantir l'unicitat de les dades

• Quan creem un índex podem especificar que sigui del tipus unique amb la qual cosa Oracle no ens deixarà inserir dades que tinguin el camp que indexem duplicat. Aixó estalvia al programador la feina de comprovació de l'unicitat.

Un índex per varies columnes

• Ens podem trobar en el cas de que sempre que consultem una columna A, també consultem una columna B. Podem crear un índex que inclou les dues columnes simultàniament: un índex concatenat. Fins i tot podem incloure diverses columnes.

• Oracle tracta els índexs concatenats de la mateixa forma que si només tingues un sola columna.

• Evidentment si abusem d'aquesta característica l'índex perd la seva efectivitat i fins i tot podem arribar a ralentitzar les actualitzacions de les dades fent-les més lentes que sense índex, ja que Oracle ha de mantenir simultàniament dues còpies de les dades: la de la taula original i les columnes que hem copiat en l'índex.

Disseny d'índexs: orientacions

• Quan utilitzarem un índex sobre una taula? Quans índexs crearem sobre una mateixa taula?
• Seràn unics o permetrem duplicats? Quan utilitzarem índex concatenats? Administradors i dissenyadors de la base de dades s'han de fer aquestes preguntes quan implementen la base de dades. Algunes orientacions:

• Crearem un índex quan el tamany de la taula sigui considerable i estem en algún dels casos explicats en el primer apartat: un camp de recerca habitual, accés a les dades per ordre alfabètic o garantir l'unicitat.

• Podem crear tots els índexs per a una mateixa taula que necessitem. Ara bé, hem de tenir en compte l'observació anterior sobre excés de dades indexades: les actualitzacions es fan més lentes com més índexs hi tinguem.

• La mateixa observació val per als índexs concatenats.

Per crear un índex podem fer-ho de dues formes:

• a) cliquem sobre l'icona d'afegir objectes i triem en el desplegable el tipus d'objecte índex.
• b) despleguem el detalls de la taula en la que volem crear un índex, i en la carpeta indexes fem clic amb el botó dret, Create...

En el que segueix soposarem que utilitzem aquesta segona forma.

En la targeta "General" donarem nom de l'índex, esquema, taula, i triarem en la llista de columnes de la taula el o els camps que anem a indexar. La columna "Order" indica en quin ordre actúa cada camp en l'índex.

Per exemple, en la imatge anterior tenim definit un index en els camps ALUMNE1, ALUMNE2 i DESCRIPCIO amb dades úniques. Si la taula té les següents dades:

Columna ALUMNE1	Columna ALUMNE2	Columna DESCRIPCIO	resta de columnes...
PERE	MARC	ROUTERS CISCO	resta de dades fila 1
ISABEL	MARC	SERVIDORS WEB	resta de dades fila 2.
PERE	DAVID	XARXES NOVELL	resta de dades fila 3
PERE	MARC	INTRANET WINDOWS	resta de dades fila 4

Aleshores l'índex tendria la següent organització:

Dades indexades	punter a la fila de la taula
ISABEL, MARC, SERVIDORS WEB	2
PERE, DAVID, XARXES NOVELL	3
PERE, MARC, INTRANET WINDOWS	4
PERE, MARC, ROUTERS CISCO	1

Les "options" en la targeta general són:

• Unique : l'índex verificarà l'unicitat de les dades
• Bitmap: Per defecte els índexs utilitzen com a estructura interna els arbres B que és l'organització "de tota la vida" per als índexs. Com a novetat des de la versió 9i s'fereix també l'organització "mapa de bits" o bitmap que en certes circuntstàncies pot ser més eficaç. Aquesta opció no es pot usar conjuntament amb els índexs globals particionats.
• Unsorted : no classificat alfabèticament. Per defecte els índexs estan classificats
• Reverse: En els índexs classificats, la classificació és de més gran a més petit, com ara Z, Y, ..., C,B,A

Les dues icones situades a la part de sota de la llista de camps serveixen per afegir o eliminar una "Column Expression", que permet crear noves columnes en l'índex que evalúen expressions que són funcions dels camps de la taula. L'usuari pot escriure les seves pròpies funcions PL/SQL i utilitzar-les aquí.

Targeta Partition

• La característica de particionament està pensada per gestionar taules i índexs molt grans (cents de milers o milions de registres).
• Permeten partir-les en peçes més petites anomenades particions
• Les instruccions SQL poden accedir a les particions igual que a les taules senceres
• Aquesta opció està disponible a partir de la versió 8 Empresarial

Targeta Storage

Conté els mateixos elements que la targeta Storage dels objectes Taula, no cal repetir l'explicació.

Targeta Parallel

Útil si el servidor és multiprocessador i volem fer procés paral·lel de l'índex.
 
 

Crear els índexs de les taules Publicacions i Temes que vàreu crear en la pràctica de creació de taules
segons les especificacions que consten en la pràctica.