Índexs i vistes

Índexs

Que és un índex?

Un índex és una estructura de dades auxiliar que el SGBD manté per accelerar les cerques en una taula. Funciona de forma similar a l'índex d'un llibre: en lloc de llegir tot el llibre per trobar un tema (escaneig seqüencial), consultes l'índex i vas directament a la pàgina.

Sense índex, una consulta com SELECT * FROM clients WHERE email = 'joan@example.com' ha de llegir totes les files de la taula (full table scan). Amb un índex sobre la columna email, el SGBD pot localitzar la fila en temps O(log n).

Estructura interna: l'arbre B (B-tree)

La majoria d'índexs relacionals usen un arbre B (B-tree). És un arbre equilibrat on cada node conté un rang de valors ordenats i apuntadors als nodes fills. Una cerca sempre comença al node arrel i baixa cap a les fulles seguint comparacions: si el valor buscat és menor que la clau del node, va a l'esquerra; si és major, va a la dreta.

graph TD
    R["Arrel<br/>[ 30 | 60 ]"]

    N1["Node intern<br/>[ 10 | 20 ]"]
    N2["Node intern<br/>[ 40 | 50 ]"]
    N3["Node intern<br/>[ 70 | 85 ]"]

    L1["Fulla<br/>[ 5, 8, 10 ]"]
    L2["Fulla<br/>[ 15, 20 ]"]
    L3["Fulla<br/>[ 25, 30 ]"]
    L4["Fulla<br/>[ 35, 40 ]"]
    L5["Fulla<br/>[ 45, 50 ]"]
    L6["Fulla<br/>[ 55, 60 ]"]
    L7["Fulla<br/>[ 65, 70 ]"]
    L8["Fulla<br/>[ 75, 85 ]"]
    L9["Fulla<br/>[ 90, 95 ]"]

    R --> N1
    R --> N2
    R --> N3

    N1 --> L1
    N1 --> L2
    N1 --> L3

    N2 --> L4
    N2 --> L5
    N2 --> L6

    N3 --> L7
    N3 --> L8
    N3 --> L9

    style R fill:#2563EB,stroke:#1d4ed8,color:#fff
    style N1 fill:#7C3AED,stroke:#6d28d9,color:#fff
    style N2 fill:#7C3AED,stroke:#6d28d9,color:#fff
    style N3 fill:#7C3AED,stroke:#6d28d9,color:#fff
    style L1 fill:#16A34A,stroke:#15803d,color:#fff
    style L2 fill:#16A34A,stroke:#15803d,color:#fff
    style L3 fill:#16A34A,stroke:#15803d,color:#fff
    style L4 fill:#16A34A,stroke:#15803d,color:#fff
    style L5 fill:#16A34A,stroke:#15803d,color:#fff
    style L6 fill:#16A34A,stroke:#15803d,color:#fff
    style L7 fill:#16A34A,stroke:#15803d,color:#fff
    style L8 fill:#16A34A,stroke:#15803d,color:#fff
    style L9 fill:#16A34A,stroke:#15803d,color:#fff

Cerca de email = 'joan@example.com' (valor = 45) pas a pas:

1. Arrel [30 | 60] → 45 és entre 30 i 60 → baixem al node intern del mig [40 | 50]
2. Node intern [40 | 50] → 45 és entre 40 i 50 → baixem a la fulla [45, 50]
3. Fulla [45, 50] → trobem el valor 45 → retornem el punter a la fila de disc

Amb 9 fulles i 3 nivells, la cerca ha fet 3 comparacions en lloc de llegir les 9 fulles. Amb milions de files, un arbre B de 4 nivells localitza qualsevol valor en 4 lectures de disc, independentment de la mida de la taula.

Per que O(log n)?

En un arbre B amb factor de ramificació k i n files, l'alçada de l'arbre és log_k(n). PostgreSQL i MySQL usen factors de ramificació de centenars d'entrades per node, de manera que una taula de 100 milions de files pot tenir un índex de tan sols 4-5 nivells.

Quan crear un índex:

• Columnes que apareixen freqüentment en clàusules WHERE.
• Columnes usades en JOIN ON.
• Columnes sobre les que és fa ORDER BY freqüentment.
• Columnes FOREIGN KEY (alguns motors els creen automàticament, d'altres no).

Quan NO crear un índex:

• Columnes en taules molt petites (l'escaneig seqüencial és més ràpid).
• Columnes amb molt poca selectivitat (per exemple, una columna actiu BOOLEAN que és TRUE per al 99% de les files).
• Taules on es fan moltes escriptures i poques lectures (cada índex afegeix overhead a INSERT/UPDATE/DELETE).

Tipus d'índexs

Tipus	Motors	Adequat per a
B-tree	Tots	Comparacions (=, <, >, BETWEEN, LIKE 'prefix%')
Hash	PostgreSQL, MySQL	Només igualtats exactes (=)
GIN	PostgreSQL	Cerca en arrays, JSONB, text complet (full-text search)
GiST	PostgreSQL	Dades geogràfiques (PostGIS), rangs
FULLTEXT	MySQL	Cerca de text complet
Columnstore	SQL Server	Consultes OLAP sobre taules molt grans

CREATE índex

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Índex simple en una columna
CREATE índex idx_clients_email ON clients(email);

-- Índex en múltiples columnes (índex compost)
CREATE índex idx_comandes_client_data ON comandes(id_client, data_com);

-- Índex únic (equivalent a UNIQUE constraint)
CREATE UNIQUE índex uq_clients_dni ON clients(dni);

-- Índex parcial (només les files actives)
CREATE índex idx_clients_actius ON clients(email) WHERE actiu = TRUE;

-- Índex sobre expressió
CREATE índex idx_clients_email_lower ON clients(LOWER(email));

-- Veure els índexs d'una taula
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'clients';

-- Índex simple
CREATE índex idx_clients_email ON clients(email);

-- Índex compost
CREATE índex idx_comandes_client_data ON comandes(id_client, data_com);

-- Índex únic
CREATE UNIQUE índex uq_clients_dni ON clients(dni);

-- Full-text índex
CREATE FULLTEXT índex ft_productes_nom ON productes(nom, descripcio);

-- Veure els índexs d'una taula
SHOW índex FROM clients;
-- O:
SELECT index_name, column_name, non_unique
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
  AND table_name = 'clients';

-- Índex simple
CREATE índex idx_clients_email ON clients(email);

-- Índex compost
CREATE índex idx_comandes_client_data ON comandes(id_client, data_com);

-- Índex únic
CREATE UNIQUE índex uq_clients_dni ON clients(dni);

-- Índex amb columnes incloses (covering índex)
CREATE índex idx_comandes_client
ON comandes(id_client)
INCLUDE (data_com, total);

-- Veure els índexs d'una taula
SELECT i.name, i.type_desc, ic.column_id
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('clients');

-- Índex simple
CREATE INDEX idx_clients_email ON clients(email);

-- Índex compost
CREATE INDEX idx_comandes_client_data ON comandes(id_client, data_com);

-- Índex únic
CREATE UNIQUE INDEX uq_clients_dni ON clients(dni);

-- Índex parcial (Oracle 12c+, amb clàusula WHERE virtual via invisible index o function-based)
-- Oracle no suporta WHERE directament; s'usa un índex de funció:
CREATE INDEX idx_clients_actius ON clients(CASE WHEN actiu = 1 THEN email END);

-- Índex sobre expressió (function-based index)
CREATE INDEX idx_clients_email_lower ON clients(LOWER(email));

-- Veure els índexs d'una taula
SELECT index_name, column_name, index_type
FROM user_ind_columns
WHERE table_name = 'CLIENTS'
ORDER BY index_name, column_position;

Eliminar un índex

PostgreSQLMySQL / MariaDBSQL ServerOracle

DROP índex idx_clients_email;
DROP índex IF EXISTS idx_clients_email;

DROP índex idx_clients_email ON clients;
-- O bé:
ALTER TABLE clients DROP índex idx_clients_email;

DROP índex idx_clients_email ON clients;

DROP INDEX idx_clients_email;
-- Oracle no requereix el nom de la taula (els índexs són globals a l'esquema)

Els índexs de clau primaria i UNIQUE

Quan definiu una PRIMARY KEY o una restricció UNIQUE, el SGBD crea automàticament un índex. No cal crear-lo manualment. A MySQL, les FOREIGN KEY no creen índexs automàticament a la taula fill; cal crear-los manualment (recomanat sempre).

AC0372/03/05 — Miniactivitat

RA3 · CA3.5, CA3.6

Analitzeu l'impacte d'un índex:

1. Inseriu 100.000 files a una taula sense índex (podeu usar una funcio generate_series a PostgreSQL).
2. Executeu EXPLAIN ANALYZE SELECT * FROM taula WHERE columna = 'valor' i anoteu el cost.
3. Creeu un índex sobre la columna.
4. Executeu de nou la mateixa consulta i compareu el pla d'execució.

---

Vistes

Què és una vista?

Una vista és una consulta SQL emmagatzemada que és presenta com si fos una taula. No emmagatzema dades pròpies (en les vistes normals), sino que executa la consulta subjacent cada vegada que és consulta la vista.

Usos principals de les vistes:

• Simplificació: Ocultar la complexitat d'una consulta amb JOINs per als usuaris finals.
• Seguretat: Limitar quines columnes o files veu un usuari sense modificar les taules base.
• Abstracció: Canviar l'esquema subjacent sense trencar les aplicacions que usen la vista.
• Reutilització: Evitar repetir consultes complexes en múltiples llocs.

CREATE VIEW

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Vista simple
CREATE VIEW clients_actius AS
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = TRUE;

-- Vista amb JOINs
CREATE VIEW resum_comandes AS
SELECT
    c.nom || ' ' || c.cognoms AS client,
    co.id_comanda,
    co.data_com,
    co.estat,
    COUNT(lc.num_linia) AS num_linies,
    co.total
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
LEFT JOIN linies_comanda lc ON co.id_comanda = lc.id_comanda
GROUP BY c.nom, c.cognoms, co.id_comanda, co.data_com, co.estat, co.total;

-- Consultar una vista
SELECT * FROM clients_actius WHERE cognoms LIKE 'G%';

-- Llistar les vistes de l'esquema
SELECT viewname, definition FROM pg_views
WHERE schemaname = 'public';

-- Vista simple
CREATE VIEW clients_actius AS
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;

-- Vista amb JOINs i WITH CHECK OPTION
CREATE VIEW comandes_pendents AS
SELECT co.id_comanda, cl.nom, cl.email, co.data_com, co.total
FROM comandes co
JOIN clients cl ON co.id_client = cl.id_client
WHERE co.estat = 'pendent';

-- Llistar les vistes
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SELECT table_name, view_definition FROM information_schema.views
WHERE table_schema = DATABASE();

-- Vista simple
CREATE VIEW clients_actius AS
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;
GO

-- Vista amb schema binding (millora el rendiment i evita esborrar taules)
CREATE VIEW resum_productes
WITH SCHEMABINDING AS
SELECT id_producte, nom, preu, estoc
FROM dbo.productes
WHERE actiu = 1;
GO

-- Llistar les vistes
SELECT name, definition FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type = 'V';

-- Vista simple
CREATE VIEW clients_actius AS
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;

-- Vista amb JOINs
CREATE VIEW resum_comandes AS
SELECT
    c.nom || ' ' || c.cognoms AS client,
    co.id_comanda,
    co.data_com,
    co.estat,
    COUNT(lc.num_linia) AS num_linies,
    co.total
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
LEFT JOIN linies_comanda lc ON co.id_comanda = lc.id_comanda
GROUP BY c.nom, c.cognoms, co.id_comanda, co.data_com, co.estat, co.total;

-- Substituir una vista existent (CREATE OR REPLACE)
CREATE OR REPLACE VIEW clients_actius AS
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;

-- Llistar les vistes de l'esquema actual
SELECT view_name, text FROM user_views ORDER BY view_name;

WITH CHECK OPTION

WITH CHECK OPTION garanteix que les operacions DML sobre una vista actualitzable (INSERT, UPDATE) han de complir la condició WHERE de la vista. Sense aquesta opció, es podria inserir/modificar una fila que després no és visible a través de la vista.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE VIEW clients_actius AS
SELECT * FROM clients WHERE actiu = TRUE
WITH CHECK OPTION;

-- Aixo fallara: la fila no compliria la condicio de la vista
INSERT INTO clients_actius (nom, cognoms, email, actiu)
VALUES ('Joan', 'Garcia', 'joan@example.com', FALSE);
-- ERROR: new row violates check option for view "clients_actius"

CREATE VIEW clients_actius AS
SELECT * FROM clients WHERE actiu = 1
WITH CHECK OPTION;

CREATE VIEW clients_actius AS
SELECT * FROM clients WHERE actiu = 1
WITH CHECK OPTION;
GO

CREATE VIEW clients_actius AS
SELECT * FROM clients WHERE actiu = 1
WITH CHECK OPTION;
-- Oracle també suporta WITH CHECK OPTION CONSTRAINT nom_constraint

Eliminar una vista

DROP VIEW clients_actius;
DROP VIEW IF EXISTS clients_actius;

-- Substituir una vista existent
CREATE OR REPLACE VIEW clients_actius AS ...;   -- PostgreSQL, MySQL

Les vistes no són sempre actualitzables

Una vista es pot actualitzar (INSERT/UPDATE/DELETE) només si compleix certes condicions: només conté una taula base, no te DISTINCT, GROUP BY, HAVING, UNION, ni subqueries en el SELECT. Si la vista conté JOINs o agregats, normalment no és pot actualitzar directament.