INDICES SQL ORACLE

INDICES SQL ORACLE

El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla. 

Los índices pueden ser creados usando una o más columnas, preparando la base de datos tanto para búsquedas rápidas al azar como para ordenaciones eficientes de los registros.

El espacio en disco requerido para almacenar el índice es típicamente menor que el espacio de almacenamiento de la tabla, lo que da la posibilidad de almacenar en memoria los índices de tablas que no cabrían en ella.

Se pueden crear indices en tablas nuevas:

CREATE TABLE Cliente(

   DNI VARCHAR(9) not null,

   NOMBRE VARCHAR(20),

   EMAIL VARCHAR(50),

   CONSTRAINT PK_DNI PRIMARY KEY (DNI)

); 

En esta tabla, tras definir los campos se crea un indice automaticamente PK_DNI con el campo DNI.

Otra forma de crear un indice es con una tabla ya existente:

CREATE TABLE Cliente(

   DNI VARCHAR(9) not null PRIMARY KEY,

   NOMBRE VARCHAR(20),

   EMAIL VARCHAR(50)

);

CREATE INDEX CLIENTE_INDEX ON Cliente(DNI);

Se le puede indicar al indice que el campo indexado tenga un valor unico (solo exista un registro con este valor):

CREATE UNIQUE CLIENTE_INDEX ON Cliente(DNI);

Podemos monitorizar los indices que hemos creado. Esto nos permite saber si el index se esta utilizando en alguna de las consultas realizadas.

ALTER INDEX CLIENTE_INDEX MONITORING USAGE;

Si queremos quitar esta opción:

ALTER INDEX CLIENTE_INDEX NOMONITORING USAGE;

Para borrar un indice:

DROP INDEX CLIENTE_INDEX;

CLUSTER ORACLE SQL

CLUSTER ORACLE SQL

Utilice el CREATE cluster instrucción para crear un clúster. Un clúster es un objeto de esquema que contiene datos de una o más tablas, los cuales tienen una o más columnas en común. Oracle Database almacena  todas las filas de todas las tablas que comparten la misma clave de clúster. De esta manera el acceso a estos datos sera mas rápido (en bases de datos con un volumen de datos alto).

Cuando se crea un Cluster de varias tablas también se crea un clave de cluster para acceder a los datos de las tablas.

Crear un Cluster

CREATE CLUSTER CLUSTER_Usuarios

   (ClusterNumber NUMBER(4)) -- Definimos la clave del cluster.

   SIZE 512 -- Definimos el tamaño del cluster.

   STORAGE (initial 100K next 50K); -- Valores de almacenamiento.

 Ya hemos creado un cluster. Ahora tenemos que crear un indice para el cluster creado

CREATE INDEX IDX_Usuarios ON CLUSTER CLUSTER_Usuarios;

Ya tenemos creado el cluster y el indice por lo que ya podemos utilizar el cluster cuando creemos una nueva tabla.

CREATE TABLE Usuarios( -- Creamos la tabla Usuarios

DNI VARCHAR2 (9) PRIMARY KEY, -- DNI Clave principal

NAME VARCHAR2(100))

CLUSTER CLUSTER_Usuario(DNI);

CREATE TABLE Factura(

FacturaID INTEGER PRIMARY KEY,

DESCRIPTION VARCHAR2(20),

PedidoID NUMBER(10),

DNI VARCHAR2 (9))

CLUSTER CLUSTER_Usuario(DNI);

Ya tenemos configuradas las tablas y relacionadas por el campo DNI al cluster. A partir de este momento cada vez que se realice una consulta a estas tablas el acceso sera mas rápido.

Ejercicio 3 empresa logística

Empresa de Logística

Crea un tablespace llamado LOGISTICS

Crea tres usuarios cuyo default tablespace sea ese

• LOGISTICS_USER con rol de connect
• LOGISTICS_DEV con rol de connect,resources
• LOGISTICS_ADM con rol de sysdba

Tablas

Entra con SQLDeveloper con LOGISTICS_DEV y crea las siguientes tablas:

INSURANCE
insuranceid integer pk,
description varchar2(30),
renewal date

• Crear claves primarias
• Crear trigger de autonumérico
• Check donde renewal sea mayor que la fecha de hoy
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table INSURANCE(
insuranceid integer primary key NOT NULL,
description varchar2(30),
renewal date
--constraint fecha_mayor_ck check (renewal>sysdate)
);

CREATE SEQUENCE IncrINSURANCE
  MINVALUE 1
  INCREMENT BY 1
  START WITH 1
  nomaxvalue
;

Create Trigger TriggerINSURANCE
  before insert on INSURANCE
  for each row
  begin
    Select IncrINSURANCE.nextval into:new.insuranceid from dual;
  end;
/

-----------------------------------------------------------------------------------------------------

TRUCK
plate char(10) pk
model varchar2(30),
insuranceid integer not null fk

• Crear claves primarias y extranjeras
• crear trigger de autonumérico --PARA QUE UN TRIGGER EN UN CHAR.... SE HACE Y YA ESTA
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table TRUCK (
plate char(10) primary key not null,
model varchar2(30),
insuranceid integer not null
);

alter table TRUCK
add constraint FK_Truck_insuranceid
foreign key (insuranceID)
references insurance(insuranceid);

-----------------------------------------------------------------------------------------------------

DRIVER
driverid integer primary key,
name varchar2(30),
license char(15),
birthdate date

• Crear claves primarias
• Crear trigger de autonumérico
• Check donde renewal sea menor que la fecha de hoy
• Introduce unos registros de ejemplo.

----------------------------------------------------------------------------------------------------

create table DRIVER(
driverid integer primary key,
name varchar2(30),
license char(15),
birthdate date
--constraint fecha_mayor_ck check (birthdate>sysdate) -- NO FUNCIONA!!!
);
CREATE SEQUENCE Incrdriver
  MINVALUE 1
  INCREMENT BY 1
  START WITH 1
  nomaxvalue
;
Create Trigger TriggerDRIVERID
  before insert on DRIVER
  for each row
  begin
    Select IncrDRIVER.nextval into:new.DRIVERID from dual;
  end;
/

-----------------------------------------------------------------------------------------------------

Tanto DELIVERY COMO PACKAGE deben formar parte de un cluster

-----------------------------------------------------------------------------------------------------

CREATE CLUSTER CLUSTER_TRANSPORTE
 (TRANSCODE NUMBER(4))
SIZE 512
STORAGE (initial 100K next 50K);

-----------------------------------------------------------------------------------------------------

DELIVERY

deliveryid integer primary key,

origin varchar(30),

destiny varchar(40),

departure timestamp,

arrival timestamp,

driverid integer fk,

truckid integer fk


cluster CLUSTER_TRANSPORTE(deliveryid );

• Crear claves primarias y extranjeras
• Check donde arrival sea mayor que la fecha de hoy
• Crear trigger de autonumérico
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table DELIVERY(
deliveryid integer primary key,
origin varchar(30),
destiny varchar(40),
departure timestamp,
arrival timestamp,
driverid integer, --fk
truckid integer --fk
)
CLUSTER CLUSTER_TRANSPORTE (deliveryid);
alter table DELIVERY
add constraint FK_DELIVERY_DRIVERid
foreign key (DRIVERID)
references DRIVER(DRIVERid);
alter table DELIVERY
add constraint FK_DELIVERY_TRUCKid
foreign key (PLATE)
references TRUCK(PLATE);
-----------------------------------------------------------------------------------------------------

CUSTOMER
customerid integer pk,
name varchar2(40) not null,
address varchar2(40) default 'unknown',
email varchar2(100)

• Crear claves primarias
• Crear trigger de autonumérico
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

CREATE TABLE CUSTOMER(
customerid integer primary key,
name varchar2(40) not null,
address varchar2(40) default 'unknown',
email varchar2(100)
);
CREATE SEQUENCE Incrcustomer
  MINVALUE 1
  INCREMENT BY 1
  START WITH 1
  nomaxvalue
;
Create Trigger TriggerCUSTOMER
  before insert on CUSTOMER
  for each row
  begin
    Select Incrcustomer.nextval into:new.customerid from dual;
  end;-----------------------------------------------------------------------------------------------------

PACKAGE
packageid integer pk,
description varchar2(40),
withcare char(1) default 'Y',
customerid integer fk,
deliveryid integer fk

• Crear claves primarias y extranjeras
• Crear trigger de autonumérico
• Introduce unos registros de ejemplo.

-----------------------------------------------------------------------------------------------------

create table PACKAGE(
packageid integer primary key,
description varchar2(40),
withcare char(1) default 'Y',
customerid integer, -- fk,
deliveryid integer -- fk
);
CLUSTER CLUSTER_TRANSPORTE (packageid);
alter table package
add constraint FK_PACKAGE_CUSTOMERID
foreign key (CUSTOMERID)
references CUSTOMER(CUSTOMERid);
alter table PACKAGE
add constraint FK_PACKAGE_DELIVERYID
foreign key (DELIVERYID)
references DELIVERY(DELIVERYID);
-----------------------------------------------------------------------------------------------------

Sinónimos

Por cada tabla crea un sinónimo para que se puedan manejar
las tablas con el prefijo LOGISTICS.
-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

Consultas

• Crea una consulta que muestre todos los nombres de camiones.
• Crea una consulta que muestre el total de paquetes que se han mandado.
• Crea una consulta que muestre los nombres de los conductores que han hecho algún envío.
• Crea una consulta que solicite un código de conductor y muestre los camiones que ha utilizado en un envío
• Crea una consulta que solicite un envío y muestre los paquetes que van en ese envío.
• Crea una consulta que muestre la suma de paquetes que hay en cada envío.
• Crea una consulta que solicite un código de paquete y muestre el nombre del conductor que lo transportaba
• Crea una consulta que dado un código de envío elimine todos los paquetes que forman parte de el.