soluciones en la nube

La nube en sus términos de SQL Server 2012

 

Cree y escale soluciones de negocios rápido, en sus términos, a lo largo del servidor, la nube privada o pública

La siguiente ola de inversiones en SQL Server ofrecerá a las organizaciones la agilidad para crear y escalar soluciones rápidamente que resuelven retos y ofrecen nuevas oportunidades de negocios del servidor a la nube privada o pública, vinculados con herramientas comunes para una productividad optimizada y tecnologías de punta para los desarrolladores -desarrolle una vez, implemente y administre cuando quiera-.

Capacidades claves

• Escale la solicitud desde los dispositivos, al centro de datos y hasta la Nube
• Rápido tiempo de solución con aparatos y ofertas de la Nube
• Productividad optimizada con las herramientas de datos de SQL Server -escriba una vez, ejecute en cualquier lugar-
• Extienda cualquier dato, en cualquier lugar, con DataSync y Odata

http://www.microsoft.com/es-xl/sqlserver/future-editions/SQL-Server-2012-cloud-on-your-terms.aspx

LINK con información:
http://ecastrom.blogspot.mx/2010/06/bases-de-datos-y-computacion-en-la-nube.HTML


Soluciones de nube privada para asegurar la custodia de los datos

Una de las cuestiones en solfa con el concepto de computación en la nube es dónde y cómo se guardan los datos. Las empresas tienen miedo a perder el control de los mismos y muchas veces recurren a soluciones de nube privada para asegurar la custodia de los datos en las empresas. Para ello en muchos casos se alojan los servicios en las propias empresas.
De esta forma se pierde uno de los principales atractivos que tiene la nube en el despliegue o lanzamiento de una nueva empresa. Los costes iniciales se multiplican si tenemos que comprar servidores, administrarlos o buscar a terceros que lo hagan para montar toda la infraestructura que nos asegure la movilidad que resulta tan atractiva para muchos en la nube.

Creacion de Indices

 

 

mysql> create database veterinaria;

Query OK, 1 row affected (0.05 sec)

 

mysql> use veterinaria;

Database changed

mysql> create table datos(ID varchar(20),nombre varchar(30),profesion varchar(30),direccion varchar(30),telefono int(15));

Query OK, 0 rows affected (0.28 sec)

 

mysql> DESCRIBE datos;

+-----------+-------------+------+-----+---------+-------+

| Field     | Type        | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| ID        | varchar(20) | YES  |     | NULL    |       |

| nombre    | varchar(30) | YES  |     | NULL    |       |

| profesion | varchar(30) | YES  |     | NULL    |       |

| direccion | varchar(30) | YES  |     | NULL    |       |

| telefono  | int(15)     | YES  |     | NULL    |       |

+-----------+-------------+------+-----+---------+-------+

5 rows in set (0.10 sec)

 

mysql> insert datos values(0001,'kevin','ingeniero','horiz.sur','6566831212');

Query OK, 1 row affected, 1 warning (0.13 sec)

 

mysql> insert datos  values(0002,'brayan','barrendero','juarez nvo','6561111111');

Query OK, 1 row affected, 1 warning (0.03 sec)

 

mysql> insert datos  values(0003,'sergio','doctora','centro','6562222222');

Query OK, 1 row affected, 1 warning (0.05 sec)

 

mysql> insert datos values(0004,'pedro','chef','misiones','6563333333');

Query OK, 1 row affected, 1 warning (0.04 sec)

 

mysql> insert datos values(0005,'sergio','licenciado','zaragosa','6564444444');

Query OK, 1 row affected, 1 warning (0.04 sec)

 

mysql> Select*FROM datos;

+------+----------+------------+------------+----------+

| ID   | nombre   | profesion  | direccion  | telefono |

+------+----------+------------+------------+----------+

| 1  | kevin  | ingeniero  |  horiz.sur   |      6566831212 |

| 2 | brayan   | barrendero  | juarez nvo.  |      6561111111 |

| 3  | sergio | doctora  | centro |      6562222222 |

| 4   | pedro   | chef | misiones   |      6563333333 |

| 5  | sergio   | licenciado| zaragosa   |      6564444444 |

+------+----------+------------+------------+----------+

5 rows in set (0.03 sec)

 

mysql> create table visita(nombre char(30)not null,razon CHAR(60),fecha varchar(

15));

Query OK, 0 rows affected (0.12 sec)

 

mysql> show tables;

+---------------------+

| Tables_in_veterinaria|

+---------------------+

| datos   |

| visita              |

+---------------------+

2 rows in set (0.04 sec)

 

mysql> insert into visita values('federico','intoxicacion','23/11/13');

Query OK, 1 row affected (0.04 sec)

 

mysql> insert into visita values('irasel','vacunas','26/05/13');

Query OK, 1 row affected (0.05 sec)

 

mysql> insert into visita values('hector','chequeo','20/06/13');

Query OK, 1 row affected (0.05 sec)

 

mysql> insert into visita values('ariel','baño','14/02/13');

Query OK, 1 row affected (0.05 sec)

 

mysql> insert into visita values('saul','estomago','08/09/13');

Query OK, 1 row affected (0.05 sec)

 

mysql> select *from visita;

+---------+-----------+----------+

| nombre  | razon     | fecha    |

+---------+-----------+----------+

| federico  | intoxicacion | 23/11/13 |

| irasel  | vacunas   | 26/05/13 |

| hector |chequeo     | 20/06/13|

|ariel | baño | 14/02/13  |

| saul  |estomago    | 08/09/13 |

+---------+-----------+----------+

6 rows in set (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| agregar            |

| alumnos            |

| borrado            |

| cdcol              |

| clinica            |

| dorrado            |

| indices            |

| mysql              |

| performance_schema |

| phpmyadmin         |

| renombrar          |

| test               |

| veterinaria        |

| webauth            |

+--------------------+

16 rows in set (0.07 sec)

 

mysql> use veterinaria;

Database changed

mysql> show tables;

+---------------------+

| Tables_in_veterinaria |

+---------------------+

| datos   |

| visita              |

+---------------------+

2 rows in set (0.01 sec)

 

mysql> create user 'veterinario'@'localhost'identified by'veterinario_pass';

Query OK, 0 rows affected (0.09 sec)

 

mysql> create user 'asistente'@'localhost'identified by'asistente_pass';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant select,update on veterinaria.*to eder identified by'veterinario_pa

ss';

Query OK, 0 rows affected (0.03 sec)

 

mysql> grant select,insert on veterinaria.*to eder identified by'veterinario_pa

ss';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant select,delete on veterinaria.*to eder identified by'veterinario_pa

ss';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant select,update on veterinaria.*to eder identified by'asistente_pass

';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant select,create on veterinaria.*to eder identified by'asistente_pass

';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant select,delete on veterinaria.*to eder identified by'asistente_pass

';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

1.-status de la tabla

2.-creacion del index

3.-obtener el tamaño de la base de datos

 

 

 

 

Particion

Particionado de tablas en Oracle

En una entrada anterior del blog vimos los conceptos básicos del particionado de tablas y como se podian llevar a la práctica utilizando MySql. Incluso hicimos una comparativa de tiempos de respuesta con una tabla de 1 millón de registros con y sin particionado. Vamos a ver ahora como implementa Oracle el particionado y algunos ejemplos prácticos de creación de tablas particionadas. Como ya vimos, el particionado es una técnica de optimización que pretende mejorar los tiempos de respuesta de las consultas, y que puede ser especialmente útil en un sistema DW donde las tablas de hechos pueden ser muy grandes.

Tipos de Particionado en Oracle

El particionado fue introducido por primera vez en la versión 8 de Oracle, como una nueva característica DW para la gestión de grandes cantidades de información, y para facilitar la tarea de los administradores de bases de datos. Dependiendo de la versión de Oracle en la que estemos, tenemos diferentes tipos de particionado disponibles:

• Oracle 8.0: particionado Range.
   
• Oracle 8i: además del particionado Range se añaden los tipos Hash y Composite.
• Oracle 9iR2/10g: se amplian con el tipo List y se permiten nuevas combinaciones de tipos en el particionado Composite.
• Oracle 11g: se introducen las columnas virtuales para particionar(que no existen fisicamente en la tabla), así como el particionado de Sistema (donde podemos gestionar directamente en que partición de la tabla se insertan los registros) y el particionado por Intervalos.

Particionado de Tablas en Oracle

Basicamente, el particionado se realiza utilizando una clave de particionado (partitioning key), que determina en que partición de las existentes en la tabla van a residir los datos que se insertan. Oracle también permite realizar el particionado de indices y de tablas organizadas por indices. Cada partición ademas puede tener sus propias propiedades de almacenamiento. Las tablas particionadas aparecen en el sistema como una única tabla, realizando el sistema la gestión automatica de lectura y escritura en cada una de las particiones (excepto  para el caso de la partición de Sistema introducida en la versión 11g). La definición de las particiones se indica en la sentencia de creación de las tablas, con la sintaxis oportuna para cada uno de los tipos.

• Particionado Range: la clave de particionado viene determinada por un rango de valores, que determina la partición donde se almacenara un valor.
• Particionado Hash: la clave de particionado es una función hash, aplicada sobre una columna, que tiene como objetivo realizar una distribución equitativa de los registros sobre las diferentes particiones. Es útil para particionar tablas donde no hay unos criterios de particionado claros, pero en la que se quiere mejor el rendimiento.
• Particionado List: la clave de particionado es una lista de valores, que determina cada una de las particiones.
• Particionado Composite: los particionados anteriores eran del tipo simples (single o one-level), pues utilizamos un unico método de  particionado sobre una o mas columnas. Oracle nos permite utilizar metodos de particionado compuestos, utilizando un primer particionado de un tipo determinado, y luego para cada particion, realizar un segundo nivel de particionado utilizando otro metodo. Las combinaciones son las siguientes (se han ido ampliando conforme han ido avanzando las versiones): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).
• Particionado Interval: tipo de particionado introducido igualmente en la versión 11g. En lugar de indicar los rangos de valores que van a determinar como se realiza el particionado, el sistema automáticamente creara las particiones cuando se inserte un nuevo registro en la b.d. Las técnicas de este tipo disponible son Interval, Interval List, Interval Range e Interval Hash (por lo que el particionado Interval es complementario a las técnicas de particionado vistas anteriormente).
• Particionado System: se define la tabla particionada indicando las particiones deseadas, pero no se indica una clave de particionamiento. En este tipo de particionado, se delega la gestión del particionado a las aplicaciones que utilicen la base de datos (por ejemplo, en las sentencias sql de inserción deberemos de indicar en que partición insertamos los datos).

Referente al particionado, y como característica interesante, Oracle nos permite definir sentencias SQL del tipo DML haciendo referencia a las particiones. Es lo que llaman nombres de tabla con extension de partición (partition-extended table names). Por ejemplo, podremos hacer un select sobre una tabla particionada indicando en la sintaxis la partición de la queremos que se haga lectura. Por ejemplo:

SELECT * FROM schema.table PARTITION(part_name);

Esto es igualmente válido para las sentencias INSERT, UPDATE, DELETE, LOCK TABLE. Esta sintaxis nos proporciona una forma simple de acceder a las particiones individuales como si fueran tablas, y utilizarlas, por ejemplo, para la creación de vistas (utilizando la vista en lugar de la tabla), lo que nos puede ser util en muchas situaciones.
Vamos a ver un ejemplo de construcción de cada uno de los tipos de particionado.
Particionado Range
Esta forma de particionamiento requiere que los registros estén identificado por un “partition key”  relacionado por un predefinido rango de valores. El valor de las columnas “partition key” determina la partición a la cual pertenecerá el registro.

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))     TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))    TABLESPACE tsd
 );

Este tipo de particionamiento esta mejor situado cuando se tiene datos que tienen rango lógicos y que pueden ser distribuidos por este. Ej. Mes del Año o un valor numérico.
Particionado Hash
Los registros de la tabla tienen su localización física determinada aplicando un valor hash a la columna del partition key. La funcion hash devuelve un valor automatico que determina a que partición irá el registro. Es una forma automática de balancear el particionado. Hay varias formas de construir este particionado. En el ejemplo siguiente vemos una definición sin indicar los nombres de las particiones (solo el número de particiones):

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     PARTITION BY HASH(deptno) PARTITIONS 16;

Igualmente, se pueden indicar los nombres de cada particion individual o los tablespaces donde se localizaran cada una de ellas:

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     STORAGE (INITIAL 10K)
     PARTITION BY HASH(deptno)
       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

Particionado List
Este tipo de particionado fue añadido por Oracle en la versión 9, permitiendo determinar el particionado según una lista de valores definidos sobre el valor de una columna especifica.

CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);

Este particionado tiene algunas limitaciones, como que no soporta múltiples columnas en la clave de particionado (como en los otros tipos), los valores literales deben ser únicos en la lista, permitiendo el uso del valor NULL (aunque no el valor MAXVALUE, que si puede ser utilizado en particiones del tipo Range). El valor DEFAULT sirve para definir la partición donde iran el resto de registros que no cumplen ninguna condición de las diferentes particiones.
Particionado Composite
Este tipo de particionado es compuesto, pues se conjuga el uso de dos particionados a la vez. Veamos un ejemplo utilizando el tipo RANGE y el HASH. En primer lugar, hace un particionado del tipo RANGE utilizando rangos de años. En segundo lugar, para cada partición definida por cada año, hacemos un segundo particionado (subparticion) del tipo aleatorio (HASH) por el valor de otra columna:

  
  CREATE TABLE TAB2 (ord_id     NUMBER(10),
     ord_day    NUMBER(2),
     ord_month  NUMBER(2),
     ord_year   NUMBER(4)
     )
  PARTITION BY RANGE(ord_year)
  SUBPARTITION BY HASH(ord_id)
  SUBPARTITIONS 8
   ( PARTITION q1 VALUES LESS THAN(2001)
     ( SUBPARTITION q1_h1 TABLESPACE TBS1,
       SUBPARTITION q1_h2 TABLESPACE TBS2,
       SUBPARTITION q1_h3 TABLESPACE TBS3,
       SUBPARTITION q1_h4 TABLESPACE TBS4
     ),
     PARTITION q2 VALUES LESS THAN(2002) 
     ( SUBPARTITION q2_h5 TABLESPACE TBS5,
       SUBPARTITION q2_h6 TABLESPACE TBS6,
       SUBPARTITION q2_h7 TABLESPACE TBS7,
       SUBPARTITION q2_h8 TABLESPACE TBS8
     ),
     PARTITION q3 VALUES LESS THAN(2003) 
     ( SUBPARTITION q3_h1 TABLESPACE TBS1,
       SUBPARTITION q3_h2 TABLESPACE TBS2,
       SUBPARTITION q3_h3 TABLESPACE TBS3,
       SUBPARTITION q3_h4 TABLESPACE TBS4
     ),
     PARTITION q4 VALUES LESS THAN(2004)
     ( SUBPARTITION q4_h5 TABLESPACE TBS5,
       SUBPARTITION q4_h6 TABLESPACE TBS6,
       SUBPARTITION q4_h7 TABLESPACE TBS7,
       SUBPARTITION q4_h8 TABLESPACE TBS8
     )
   )

Las combinaciones permitidas son las siguientes (se han ido ampliando conforme han ido avanzando las versiones de Oracle): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).

Particionado Composite en Oracle

Particionado Interval
El particionado Interval ha sido introducido en la versión 11g para habilitar un mantenimiento de particiones desasistido. Normalmente, cuando realizamos un particionado sobre una tabla, indicamos una lista de valores o rangos para crear de antemano las particiones.  Posteriormente, ajustamos la definición de las particiones para incluir nuevas para nuevos rangos o valores. Con las particiones Interval, preparamos  para que Oracle cree las particiones de forma automática cuando lo necesite. Básicamente, se define un intervalo y una directiva para decirle a Oracle como se tiene que comportar. Veamos un ejemplo:

 CREATE TABLE T_11G(C1 NUMBER(38,0),
 C2 VARCHAR2(10),
 C3 DATE)
 PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

Hemos creado una partición base, y con lo especificado en Interval definimos como gestionar la creación automática de nuevas particiones. La posibilidad de definir un intevalo y que Oracle se encargue de crear las particiones a medida que se vayan necesitando resulta muy interesante para facilitar el mantenimiento y administración de particiones.
Particionado System
Una de las nuevas funcionalidades introducida en la version 11g es el denominado partitioning interno o de sistema. En este particionado Oracle no realiza la gestión del lugar donde se almacenaran los registros, sino que seremos nosotros los que tendremos que indicar en que partición se hacen las inserciones.

create table t (c1 int,
                c2 varchar2(10),
                c3 date)
     partition by system
    (partition p1,
     partition p2,
     partition p3);

Si hicieramos un insert sobre la tabla (por ejemplo, insert into t values (1,’A',sysdate);), daría error, siendo la instrucción a ejecutar correcta la siguiente:
insert into t partition (p3) values (1,’A',sysdate);
Puede ser util este particionado para aplicaciones donde nos interesa ser nosotros lo que gestionamos la forma en la que se realiza el particionado (lógica de aplicación).
Uso de columnas virtuales para particionar
En la versión 11g se pueden definir en las tablas columnas virtuales (no existen físicamente). Ademas estas columnas se pueden utilizar para realizar particionado sobre ellas. La forma de crear una tabla con columnas de este tipo sería la siguiente:

create table t (c1 int,
                c2 varchar2(10),
                c3 date,
                c3_v char(1)
                generated always as
                (to_char(c3,'d')) virtual
                )
 partition by list (c3_v)
  (partition p1 values ('1'),
   partition p2 values ('2'),
   partition p3 values ('3'),
   partition p4 values ('4'),
   partition p5 values ('5'),
   partition p6 values ('6'),
   partition p7 values ('7') );

Os recomiendo la lectura de la entrada del blog OraMDQ donde Pablo Rovedo habla en profundidad sobre las nuevas funcionalidades de particionado de la version 11g de Oracle, incluyendo unos completos ejemplos prácticos.
Gestión del particionado.
La gestión del particionado es totalmente dinámica, de forma que se podrán añadir particiones a una tabla particionada existente, juntar o borrar particiones, convertir una particion en una tabla no particionada, partir una partición en dos (Splitting), hacer un truncate (borra los datos de la partición pero deja la estructura). También podemos mover una partición de un tablespace a otro, renombrarla, etc. Os recomiendo la lectura de blog Bases de Datos y Tecnología donde se explican en detalle algunas de estas operaciones, así como el  blog Database Design que también habla sobre el tema).
El particionado en Oracle tiene muchas mas funcionalidades de las que podeis ampliar información en la propia documentación online del fabricante (Oracle 10g y Oracle 11g).

Particiones en MySQL

Cuando alguna de las tablas de tu base de datos llega a crecer tanto que el rendimiento empieza a ser un problema, es hora de empezar a leer algo sobre optimización. Índices, el comando EXPLAIN, el registro de consultas lentas, … estas son herramientas básicas que todo el mundo debería conocer. Una característica algo menos conocida, aunque se introdujo en la versión 5.1 de MySQL, son las particiones.
En el hospital en que trabajo la mayor tabla con la que tenemos que lidiar es la que almacena todos y cada uno de los contratos de todos los trabajadores que alguna pasaron por el hospital desde que se fundó en los años 50. Esto supone sólo un par de cientos de miles de tuplas, lo cuál no debería dar muchos dolores de cabeza con una base de datos bien optimizada, consultas razonables, y un hardware decente. Sin embargo, hay personas que tienen que tratar con cantidades de datos realmente obscenas, que multiplican estos números por 10 veces 10.
Una solución que nos puede venir a la cabeza, sobre todo si la mayor parte de la información se almacena a modo de histórico y no se accede a ella frecuentemente, es dividir la tabla en varias porciones. Podríamos mantener una tabla para el año en curso y otra para el resto de años, por ejemplo; o una para cada uno de los años; una por lustro; por década… dependiendo de cómo se trabaje con los datos.
El particionado es un concepto parecido, aunque automatizado, que puede ahorrarnos muchos quebraderos de cabeza. Consiste en dividir los datos en particiones más pequeñas (hasta 1024) procurando, porque de otra forma sería absurdo, que sólo haya que acceder a una partición a la hora de buscar una tupla.
Se puede particionar una tabla de 5 maneras diferentes:

• Por rango: para construir nuestras particiones especificamos rangos de valores. Por ejemplo, podríamos segmentar los datos en 12 particiones: una para los contratos de 1950 a 1960, otra para los años 60, los 70, 80, 90, la década del 2000 y la década actual
  
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY RANGE(YEAR(fechaInicio)) (  
  3.     PARTITION partDecada50 VALUES LESS THAN (1960),  
  4.     PARTITION partDecada60 VALUES LESS THAN (1970),  
  5.     PARTITION partDecada70 VALUES LESS THAN (1980),  
  6.     PARTITION partDecada80 VALUES LESS THAN (1990),  
  7.     PARTITION partDecada90 VALUES LESS THAN (2000),  
  8.     PARTITION partDecada00 VALUES LESS THAN (2010),  
  9.     PARTITION partDecada10 VALUES LESS THAN MAXVALUE  
  10. );  

  ALTER TABLE contratos
  PARTITION BY RANGE(YEAR(fechaInicio)) (
   PARTITION partDecada50 VALUES LESS THAN (1960),
   PARTITION partDecada60 VALUES LESS THAN (1970),
   PARTITION partDecada70 VALUES LESS THAN (1980),
   PARTITION partDecada80 VALUES LESS THAN (1990),
   PARTITION partDecada90 VALUES LESS THAN (2000),
   PARTITION partDecada00 VALUES LESS THAN (2010),
   PARTITION partDecada10 VALUES LESS THAN MAXVALUE
  );

• Por listas: para construir nuestras particiones especificamos listas de valores concretos.
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY LIST(YEAR(fechaInicio)) (  
  3.     PARTITION partDecada50 VALUES IN (1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959),  
  4.     PARTITION partDecada60 VALUES IN (1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969),  
  5.     PARTITION partDecada70 VALUES IN (1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979),  
  6.     PARTITION partDecada80 VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),  
  7.     PARTITION partDecada90 VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),  
  8.     PARTITION partDecada00 VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,  
  9. 2007, 2008, 2009),  
  10.     PARTITION partDecada10 VALUES IN (2010, 2011, 2012, 2013, 2014, 2015, 2016,  
  11. 2017, 2018, 2019)  
  12. );  

  ALTER TABLE contratos
  PARTITION BY LIST(YEAR(fechaInicio)) (
   PARTITION partDecada50 VALUES IN (1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959),
   PARTITION partDecada60 VALUES IN (1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969),
   PARTITION partDecada70 VALUES IN (1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979),
   PARTITION partDecada80 VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),
   PARTITION partDecada90 VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),
   PARTITION partDecada00 VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,
  2007, 2008, 2009),
   PARTITION partDecada10 VALUES IN (2010, 2011, 2012, 2013, 2014, 2015, 2016,
  2017, 2018, 2019)
  );

• Por hash: MySQL se encarga de distribuir las tuplas automáticamente usando una operación de módulo. Sólo hay que pasarle una columna o expresión que resulte en un entero (el hash) y el número de particiones que queramos crear.
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY HASH(YEAR(fechaInicio))  
  3. PARTITIONS 7;  

  ALTER TABLE contratos
  PARTITION BY HASH(YEAR(fechaInicio))
  PARTITIONS 7;

• Por clave: similar a la partición por hash, pero en este caso no necesitamos pasarle un entero; MySQL utilizará su propia función de hash para generarlo. Si no se indica ninguna columna a partir de la que generar el hash, se utiliza la clave primaria por defecto.
  view plaincopy to clipboardprint?

  1. ALTER TABLE contratos  
  2. PARTITION BY KEY()  
  3. PARTITIONS 7;  

  ALTER TABLE contratos
  PARTITION BY KEY()
  PARTITIONS 7;

• Compuesta: podemos combinar los distintos métodos de particionado y crear particiones de particiones
Por último, un pequeño ejemplo de cómo afectaría el particionado a una consulta sencilla como obtener el número total de tuplas que cumplen una condición. Estas son las estadísticas de la consulta sin particionado (ni índices)

view plaincopy to clipboardprint?
1. EXPLAIN SELECT COUNT(*)  
2. FROM contratos  
3. WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'  

EXPLAIN SELECT COUNT(*)
FROM contratos
WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'

select_type	table	type	key	rows	Extra
SIMPLE	contratos	ALL		239796	Using where

Y este el resultado de añadir las particiones (nótese la palabra clave PARTITIONS para que nos muestre también la información relativa a las particiones)

view plaincopy to clipboardprint?
1. EXPLAIN PARTITIONS SELECT COUNT(*)  
2. FROM contratos  
3. WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'  

EXPLAIN PARTITIONS SELECT COUNT(*)
FROM contratos
WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'

select_type	table	partitions	type	key	rows	Extra
SIMPLE	contratos	partDecada50	ALL		8640	Using where

Como véis, el número de tuplas que MySQL tiene que comprobar se ve disminunido en 2 órdenes de magnitud.

QUE ES UNA BITACORA

BITACORAS DE BASES DE DATOS

 

La estructura más ampliamente usada para grabar las modificaciones de la base de datos es la Bitácora.

Cada registro de la bitácora escribe una única escritura de base de datos y tiene lo siguiente :

1. Nombre de la transacción : Nombre de la transacción que realizó la operación de escritura.
2. Nombre del dato : El nombre único del dato escrito.
3. Valor antiguo : El valor del dato antes de la escritura.
4. Valor nuevo : El valor que tendrá el dato después de  la escritura.

Existen otros registros de bitácora especiales para grabar sucesos importantes durante el proceso de transacción tales como :

< T1, inicio >
< T1, x, v1, v2 >
< T1, commit >

Es fundamental que siempre se cree un registro en la bitácora cuando se realice una escritura antes de que se modifique la base de datos.
También tenemos la posibilidad de deshacer una modificación que ya se ha escrito en la base de datos, esto se realizará usando el campo del valor antiguo de los registros de la bitácora.
Los registros de la bitácora deben residir en memoria estable como resultado el volumen de datos en la bitácora puede ser exageradamente grande.

 

 

Crear una bitácora en MySQL

 

La importancia de las bitácoras es la de recuperar información ante incidentes de seguridad, detección de comportamiento inusual, información para resolver problemas, evidencia legal, es de gran ayuda en las tareas de cómputo forense.

Enseguida plantearé un ejemplo de una bitácora desarrollada para la siguiente base de datos de MySQL, llamada proyecto, que tiene las tablas carrera, departamento y maestros.

CREATE DATABASE proyecto;

USE proyecto

CREATE TABLE IF NOT EXISTS `carrera` (`clave_carrera` int(11) NOT NULL, `nom_carrera` varchar(20) NOT NULL, `num_depto` int(11) NOT NULL, PRIMARY KEY (`clave_carrera`), KEY `num_depto` (`num_depto`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `departamento` ( `num_departamento` int(11) NOT NULL,`nombre_dept` varchar(20) NOT NULL, `jefe_num_tarjet` int(11) NOT NULL, PRIMARY KEY (`num_departamento`), KEY `jefe_num_tarjet` (`jefe_num_tarjet`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `maestros` (`num_tarjeta` int(11) NOT NULL DEFAULT ’0′,`nombre` varchar(50) DEFAULT NULL, PRIMARY KEY (`num_tarjeta`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

La estructura de la tabla bitácora sería la siguiente:

CREATE TABLE IF NOT EXISTS `bitacora` (`id` int(11) NOT NULL AUTO_INCREMENT, `operacion` varchar(10) DEFAULT NULL, `usuario` varchar(40) DEFAULT NULL, `host` varchar(30) NOT NULL, `modificado` datetime DEFAULT NULL, `tabla` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

La bitácora debe registrar todos los movimientos (insertar, eliminar y modificar) que se realicen en las tablas de la base de datos. Para lograr lo anterior es necesario crear un trigger para que se ejecute después de la operación de insertar, otro para después de eliminar y el último para después de modificar para cada una de las 3 tablas de la base de datos. Los nueve triggers necesarios para que funcione la bitácora son los siguientes:

DROP TRIGGER IF EXISTS `bit_carr_ins`;
DELIMITER //
CREATE TRIGGER `bitacora` AFTER INSERT ON `carrera`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “INSERTAR”, NOW(), “CARRERA”)
//

DROP TRIGGER IF EXISTS `bit_carr_upd`;
CREATE TRIGGER `bit_carr_upd` AFTER UPDATE ON `carrera`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “ACTUALIZAR”, NOW(), “CARRERA”)
//
DROP TRIGGER IF EXISTS `bit_carr_del`;
CREATE TRIGGER `bit_carr_del` AFTER DELETE ON `carrera`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “ELIMINAR”, NOW(), “CARRERA”)
//

DROP TRIGGER IF EXISTS `bit_depto_ins`;
CREATE TRIGGER `bit_depto_ins` AFTER INSERT ON `departamento`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “INSERTAR”, NOW(), “DEPARTAMENTO”)
//

DROP TRIGGER IF EXISTS `bit_depto_upd`;
CREATE TRIGGER `bit_depto_upd` AFTER UPDATE ON `departamento`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “ACTUALIZAR”, NOW(), “DEPARTAMENTO”)
//
DROP TRIGGER IF EXISTS `bit_depto_del`;
CREATE TRIGGER `bit_depto_del` AFTER DELETE ON `departamento`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “ELIMINAR”, NOW(), “DEPARTAMENTO”)
//

DROP TRIGGER IF EXISTS `bit_mae_ins`;
CREATE TRIGGER `bit_mae_ins` AFTER INSERT ON `maestros`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “INSERTAR”, NOW(), “MAESTROS”)
//

DROP TRIGGER IF EXISTS `bit_mae_upd`;
CREATE TRIGGER `bit_mae_upd` AFTER UPDATE ON `maestros`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “ACTUALIZAR”, NOW(), “MAESTROS”)
//
DROP TRIGGER IF EXISTS `bit_mae_del`;
CREATE TRIGGER `bit_mae_del` AFTER DELETE ON `maestros`
FOR EACH ROW INSERT INTO bitacora(host, usuario, operacion, modificado, tabla) VALUES (SUBSTRING(USER(), (INSTR(USER(),’@')+1)), SUBSTRING(USER(),1,(instr(user(),’@')-1)), “ELIMINAR”, NOW(), “MAESTROS”)
//

El resultado que se espera de la bitácora se muestra en la siguiente imagen.

 

 

Cómo activar la auditoría de una base de datos Oracle

 

Se pretende mediante un sencillo ejemplo práctico para ver cómo se puede auditar las conexiónes a una base de datos ORACLE o auditar los intentos de modificación a las tablas de un usuario.

Teniendo en cuenta que el parámetro que habilita la posibilidad de auditar la base de datos ORACLE en el init.ora es audit_trail que el comando sql que activa la auditoría sobre algo es AUDIT ( para desactivar NOAUDIT ) y que la tabla para mirar ( usuario sys ) el seguimiento de auditoría es dba_audit_trail vamos a realizar este sencillo ejemplo.

1. Activar la auditoria de intento de conexiones fallidas para todos los usuarios.
   Miramos que actualmente no está activada la auditoria en la base de datos
   SQL> select name , value from v$parameter where name like ‘audit_trail’;
   audit_trail NONE
   Activamos la auditoría de la base de datos
   SQL> alter system set audit_trail = DB scope = spfile;
   Reiniciamos la base de datos ( shutdown immediate, startup ) y comprobamos que la auditoría se ha activado.
   SQL> select name , value from v$parameter where name like ‘audit_trail’;
   audit_trail DB
   Activamos la auditoría para ver la conexión y desconexión de los usuarios a la base de datos, se hace con la siguiente sentencia
   SQL> audit connect;

2. Visualizar las tablas de auditoría para comprobar que se insertan datos cuando intentamos conectarnos sin lograrlo.
   En el apartado anterior hemos activado la auditoría para ver como se conectan los usuarios a la base de datos, vamos a realizar varias pruebas y mostrar dónde se puede comprobar que los usuarios se han conectado a la base de datos.
   Nos conectamos con varios usuarios a la base de datos ( en nuestro caso con system y el usuario user9 que está creado )
   SQL> connect user9/user9;
   SQL> connect system/system;
   Tras habernos conectado a la base de datos miramos la tabla dba_audit_trail para ver que datos contiene.
   SQL> select username , action_name , priv_used , returncode from dba_audit_trail ;
   “SYSTEM” “LOGON” 1017
   “SYSTEM” “LOGON” 1017
   “USER9″ “LOGON” 1017
   “USER9″ “LOGON” “CREATE SESSION” 0
   “USER9″ “LOGON” 1017
   “USER9″ “LOGON” 1017
   Observarmos que en esta tabla se registran los intentos de conexión de los usuarios, por lo tanto podemos saber quien se ha conectado a la base de datos

3. Activar la auditoria sobre la modificación de tablas del usuario Scott.
   Ahora vamos a activar la auditoría sobre la modificación de las tablas sobre el usuario Scott, de esta forma cualquier modificación realizada en una tabla que pertenezca a este usuario será registrada en las tablas y podremos ver quien ha realizado esa modifiación.
   SQL>audit insert,update on scott . bonus by access;
   SQL>audit insert,update on scott . emp by access;
   SQL>audit insert,update on scott .dept by access;
   SQL>audit insert,update on scott . salgrade by access;
   En este caso estamos auditando cada una de las tablas que pertenencen al usuario scott ( bonus, emp, dept, salgrade ) en caso de que alguien inserte algo en ellas o realice alguna actualización. ( si queremos auditar el borrado o la lectura de alguna fila, solo hay que añadir los permisos de select y delete detrás del comando audit).Al ponerlo by access se guardará un registro en la tabla de auditoría por cada intento de insert o update que se realice sobre cada una de las tablas nombradas. ( exite también el registro by session, en el cual se registra por sesión única el intento de insert o update sobre las tablas ).
   Miramos la tabla user_obj_audit_opts ( con el usuario scott )
   SQL>select * from user_obj_audit_opts;
   “BONUS” “TABLE” “-/-” “-/-” “-/-” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “-/-” “-/-”
   “DEPT” “TABLE” “-/-” “-/-” “-/-” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “-/-” “-/-”
   “EMP” “TABLE” “-/-” “-/-” “-/-” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “-/-” “-/-”
   “SALGRADE” “TABLE” “-/-” “-/-” “-/-” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “A/A” “-/-” “-/-” “-/-” “-/-” “-/-”
   Y observamos que es lo que estamos auditando del usuario scott, en este caso se vería que eta activada para cada una de las tablas la auditoría para update e insert.
   (A/A) –> activado / por acceso
   La prueba que se puede realizar es conectarse con otro usuario que tenga permisos de insert y update sobre estas tablas y realizar una serie de inserciones y actualizaciones en esas tablas. En este caso suponemos que un usuario, user9 que tiene permisos de inserción y actualización sobre las tablas del usuario scott ha realizado una serie de inserciones y actualizaciones sobre estas tablas. La forma de ver si las ha realizado o no ( teniendo activada la auditoría es la siguiente ).
   SQL>select * from sys . dba_audit_trail where ( action_name = ‘INSERT’ ) or ( action_name = ‘UPDATE’ ) ;
   El resultado es el siguiente:
   “ERIN-0S2WXM4BDG\Erin” “USER9″ “ERIN-0S2WXM4BDG” 19/04/2006 15:38:56 “SCOTT” “BONUS” 2 “INSERT” 267 2 47 0
   “ERIN-0S2WXM4BDG\Erin” “USER9″ “ERIN-0S2WXM4BDG” 19/04/2006 15:39:09 “SCOTT” “BONUS” 2 “INSERT” 267 3 50 0
   “ERIN-0S2WXM4BDG\Erin” “USER9″ “ERIN-0S2WXM4BDG” 19/04/2006 15:39:19 “SCOTT” “BONUS” 6 “UPDATE” 267 4 55 0
   Observamos que se han registrado los intentos de inserción y de modificación sobre la tabla BONUS.