Bases de Datos y Datos

-- PostgreSQL
CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id  UUID NOT NULL DEFAULT gen_random_uuid(),
    -- ...
    CONSTRAINT uq_orders_public_id UNIQUE (public_id)
);

-- SQL Server
CREATE TABLE orders (
    id        BIGINT IDENTITY(1,1) PRIMARY KEY,
    public_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    -- ...
    CONSTRAINT uq_orders_public_id UNIQUE (public_id)
);

-- Ejemplo de identificadores aleatorios
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
    -- Mide el patrón de inserciones, las divisiones de página y el tamaño del índice
);

-- Alternativa: clave interna secuencial y UUID público
CREATE TABLE customers (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id UUID NOT NULL DEFAULT gen_random_uuid(),
    name      VARCHAR(200) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_customers_public_id UNIQUE (public_id)
);

-- Ejemplo de esquema
CREATE TABLE products (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id    UUID NOT NULL DEFAULT gen_random_uuid(),
    name         VARCHAR(300) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at   TIMESTAMPTZ NULL, -- NULL = activo, NOT NULL = eliminado
    CONSTRAINT uq_products_public_id UNIQUE (public_id)
);

-- Índice parcial para registros activos (PostgreSQL)
-- Excluye los registros eliminados de este índice
CREATE INDEX ix_products_name_active
    ON products (name)
    WHERE deleted_at IS NULL;

-- Consulta habitual: incluye siempre el filtro
SELECT * FROM products WHERE deleted_at IS NULL AND name ILIKE '%notebook%';

-- Un índice simple puede exigir búsquedas para otras columnas
CREATE INDEX ix_orders_customer_id ON orders (customer_id);

-- Índice de cobertura: incluye las columnas que necesita esta consulta
CREATE INDEX ix_orders_customer_id_cover
    ON orders (customer_id)
    INCLUDE (status, total_amount, created_at);

-- Comprueba si el plan de ejecución utiliza el índice de cobertura:
SELECT status, total_amount, created_at
FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;

-- Índice compuesto: importa el orden de las columnas
-- Elige el orden según los predicados y el plan de ejecución
CREATE INDEX ix_orders_status_created
    ON orders (status, created_at DESC)
    WHERE deleted_at IS NULL;

-- Ejemplos de mantenimiento manual: evalúa antes de ejecutar
-- Programa según la carga y las necesidades de mantenimiento
VACUUM (VERBOSE, ANALYZE) orders;

-- Consulta las tuplas muertas por tabla
SELECT
    schemaname,
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

-- Revisa el uso de los índices antes de eliminarlos
SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY relname;

-- Consulta la fragmentación por índice
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent AS fragmentation_pct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganizar: evalúa antes la carga, la densidad de páginas y los beneficios
ALTER INDEX ix_orders_customer_id ON orders REORGANIZE;

-- Reconstruir: evalúa costos y bloqueos para esta edición
ALTER INDEX ix_orders_customer_id ON orders REBUILD WITH (ONLINE = ON);

-- Actualizar estadísticas de una tabla
UPDATE STATISTICS orders WITH FULLSCAN;

-- Consultar las consultas más costosas (10 primeras por CPU)
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_ms DESC;