TAREA4-SECUENCIA-INDICES-SYNONYM

SECUENCIA

Crea un objeto capaz de darnos números consecutivos únicos.

CREATE SEQUENCE secuencia

      INCREMENT BY n

      START WITH n

      {MAX VALUE n | NOMAXVALUE}

      {MIN VALUE N | NOMINVALUE}

      {CYCLE | NOCYCLE}

      {CACHE N | NOCACHE}

      {ORDER | NOORDER};

En realidad es un generador de identificadores únicos que no bloquea transacciones.

Es muy útil para generar primary keys.

EJEMPLO: crea una secuencia de nombre ID, que se utilizara para la clave primaria de tabla ALUMNO

CREATE SEQUENCE  ID

                INCREMENT BY 1    // se incrementara en 1

                START WITH 1        // se iniciara en 1

                NOMAXVALUE       // sin valor máximo o tope

    NOCYCLE                 // no cycle, no es un ciclo

                NOCACHE ;             // no cache, para no perder números

Si no nos gusta perder números usamos NOCACHE.

CREATE SEQUENCE ID MINVALUE 1 START WITH 1

    INCREMENT BY 1 NOCACHE;

Si nos interesa la velocidad:

CREATE SEQUENCE ID MINVALUE 1 START WITH 1

    INCREMENT BY 1 CACHE 20;

Así obtenemos el siguiente valor:

SELECT ID.NEXTVAL FROM DUAL;

NEXTVAL -------------> Siguiente numero en la secuencia

CURRVAL -------------> Ultimo numero generado

También podemos obtener el valor actual:

SELECT ID.CURRVAL FROM DUAL;

MODIFICAR SECUENCIA:

      Se modifica secuencia creada cambiando el incremento de 1 a 20 y valor máximo a 999999

alter sequence ID

increment by 20

maxvalue 999999

nocache

nocycle;

DONDE SE PUEDE USAR UNA SECUENCIA:

- Clausula VALUES de la instrucción INSERT
- Clausula SET de la instrucción UPDATE
- Clausula SELECT de la instrucción SELECT

DONDE NO SE PUEDE USAR:

- Clausulas DISTINCT, GROUP BY, ORDER BY, WHERE de la instrucción SELECT

- Valor por defecto de una columna

PARA ELIMINAR UNA SECUENCIA UTILIZAMOS EL COMANDO DROP:

- DROP SEQUENCE ID;

INDICES

Los índices se usan para mejorar el rendimiento de las operaciones sobre una tabla.

En general mejoran el rendimiento las SELECT y empeoran (mínimamente) el rendimiento de los INSERT y los DELETE.

Una vez creados no es necesario nada más, Oracle la usa cuando es posible

En Oracle existen tres tipos de índices:

1)Table Index:

CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name

      ON [esquema.]table_name [tbl_alias]

         (col [ASC | DESC]) index_clause index_attribs

2)Bitmap Join Index:

CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name

      ON [esquema.]table_name [tbl_alias]

         (col_expression [ASC | DESC])

            FROM [esquema.]table_name [tbl_alias]

               WHERE condition [index_clause] index_attribs

3)Cluster Index:

CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name

      ON CLUSTER [esquema.]cluster_name index_attribs             

las index_clauses posibles son:

LOCAL STORE IN (tablespace)

LOCAL STORE IN (tablespace)

  (PARTITION [partition

       [LOGGING|NOLOGGING]

       [TABLESPACE {tablespace|DEFAULT}]

       [PCTFREE int]

       [PCTUSED int]

       [INITRANS int]

       [MAXTRANS int]

       [STORAGE storage_clause]

       [STORE IN {tablespace_name|DEFAULT]

       [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

LOCAL (PARTITION [partition

       [LOGGING|NOLOGGING]

       [TABLESPACE {tablespace|DEFAULT}]

       [PCTFREE int]

       [PCTUSED int]

       [INITRANS int]

       [MAXTRANS int]

       [STORAGE storage_clause]

       [STORE IN {tablespace_name|DEFAULT]

       [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

GLOBAL PARTITION BY RANGE (col_list)

   ( PARTITION partition VALUES LESS THAN (value_list)

       [LOGGING|NOLOGGING]

       [TABLESPACE {tablespace|DEFAULT}]

       [PCTFREE int]

       [PCTUSED int]

       [INITRANS int]

       [MAXTRANS int]

       [STORAGE storage_clause] )

INDEXTYPE IS indextype [PARALLEL int|NOPARALLEL] [PARAMETERS ('ODCI_Params')]

 {Esto es solo para table index, no para bitmap join Index}

Y además index_attribs puede ser cualquier combinación de los siguientes:

NOSORT|SORT

REVERSE

COMPRESS int

NOCOMPRESS

COMPUTE STATISTICS

[NO]LOGGING

ONLINE

TABLESPACE {tablespace|DEFAULT}

PCTFREE int

PCTUSED int

INITRANS int

MAXTRANS int

STORAGE storage_clause

PARALLEL parallel_clause

Si usamos la opcion PARALLEL esta debe estar al final.

create index es una de las pocas sentencias que pueden usar nologging option.

create index requiere un segmento temporal si no hay espacio en memoria suficiente.

Crear indices basados en funciones require que query_rewrite_enabled este a true y query_rewrite_integrity este a trusted.

Un ejemplo de indices basados en funciones para busquedas en mayusculas:

CREATE INDEX idx_case_ins ON my_table(UPPER(empname));

SELECT * FROM my_table WHERE UPPER(empname) = 'KARL';

SINONIMOS

Crea un sinónimo para algún objeto de la base de datos.

   CREATE [OR REPLACE] [PUBLIC] SYNONYM [esquema.]sinonimo

      FOR [esquema.]objeto [@dblink]

Con la opción 'PUBLIC' se crea un sinónimo público accesible a todos los usuarios, siempre que tengan los privilegios adecuados para el mismo.

Sirve para no tener que usar la notación 'esquema.objeto' para referirse a un objeto que no es propiedad de usuario.

Crea un nombre abreviado a la vista PROGRAMADOR.T_PEDIDOS:

CREATE PUBLIC SYNONYM T_PEDIDOS

FOR PROGRAMADOR.T_PEDIDOS;

No es necesario recompilarlos cuando se redefinen las tablas, de hecho puedes existir sin que exista el objeto al que referencian.

El acceso es un poco más eficiente cuando se accede por sinónimos públicos.

Cuando en una sentencia no citamos el nombre del esquema, Oracle resuelve los nombres en el siguiente orden:

·       usuario actual

·       private synonym

·       public synonym

También podemos usarlo para cambiar el objeto que usamos sin tener que cambiar la programación.

Así cambiamos la tabla:

CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS_PRUEBA;

Borrar un sinónimo:

DROP SYNONYM T_PEDIDOS;

Desarrollo taller Vistas

Profesor

Adjuntamos ejercicios de vistas solicitado en clases, correspondiente al grupo G:


/* VISTA SIMPLE */
create view udla_v_contratos (sal_con_numero, sal_con_tasa_actual)
as select con_numero, con_tasa_actual
from adm_contratos where con_numero =39792 ;

/* VISTA COMPLEJA */
create view udla_v_contratos_empresa (sal_con_numero, sal_con_tasa_actual, sal_nombre_empresa)
as
select cnt.con_numero, cnt.con_tasa_actual, emp.emp_razon_social
from adm_contratos cnt, adm_empresas  emp where
cnt.adh_rut = emp.emp_rut
and cnt.con_numero =39792 ;

/* VISTA SIMPLE  CON RESTRICCION*/
create or replace view udla_v_contratos_restriccion
as select *
from adm_contratos where con_numero = 5
with check option constraint conres_ck;

/* VISTA CON DENEGACION OPERACIONES  */
create view udla_v_contratos_read (sal_con_numero, sal_con_tasa_actual)
as select con_numero, con_tasa_actual
from adm_contratos where con_numero =39792
with read only;
 
/* ELIMINACION DE VISTA */
drop view udla_v_contratos;

/* INSERT DE VISTA */
insert into vista_empleados2 values('Juan Perez','m','Sistemas',2);

/* UPDATE DE VISTA */
update vista_empleados2 set nombre='Tomas' where nombre='Juan Perez';

Tarea2

Instrucción comando ALTER TABLE : Permite cambiar la definición de una tabla, cambiar tanto columnas como restricciones, para este ejemplo usaremos la  tabla
llamada 'PERSONA' que ya fue creada en tarea anterior

Ejemplo: agregar campos (TELEFONO - Est_Civil)

--Agregando un campo a la tabla
ALTER TABLE PERSONA ADD TELEFONO INTEGER;  

--cambiar el tamaño de un campo
ALTER TABLE PERSONA MODIFY TELEFONO INTEGER;

--Hacer NOT NULL una columna de una tabla
ALTER TABLE PERSONA MODIFY TELEFONO NOT NULL;  

--Hacer NOT NULL una columna de una tabla
ALTER TABLE PERSONA DROP COLUMN TELEFONO;    

--Agregando 2 columnas a la tabla
ALTER TABLE PERSONA ADD (TELEFONO INTEGER, Est_civil VARCHAR2(25 BYTE));  

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

--Crear una clave primaria (primary key)
ALTER TABLE PERSONA ADD CONSTRAINT PK_PERSONA PRIMARY KEY (RUT);

--Crear una clave externa, para integridad referencial (foreign key)
ALTER TABLE PERSONA ADD CONSTRAINT FK_PERSONA_AUTO FOREIGN KEY (RUT) REFERENCES T_AUTO (RUT));

--Crear un control de valores (check constraint)
ALTER TABLE PERSONA ADD CONSTRAINT CK_ESTADO CHECK (estado IN (1,2,3));

--Crear una restricción UNIQUE al campo NOMBRE
ALTER TABLE PERSONA ADD CONSTRAINT UK_APELLIDO UNIQUE (NOMBRE);


--Borrar una restricción
ALTER TABLE PERSONA DROP CONSTRAINT UK_APELLIDO;    

--Deshabilita una restricción:
ALTER TABLE PERSONA DISABLE CONSTRAINT UK_APELLIDO;  

--habilita una restricción:
ALTER TABLE PERSONA ENABLE CONSTRAINT UK_APELLIDO;  


GRUPO G

DBUDLA

CREATE TABLE PERSONA

(

  RUT        INTEGER           NOT NULL,

  DV         CHAR(1 BYTE)      NOT NULL,

  NOMBRE     VARCHAR2(50 BYTE) NOT NULL,

  PATERNO    VARCHAR2(30 BYTE) NOT NULL,

  MATERNO    VARCHAR2(30 BYTE) NOT NULL,

  DIRECCION  VARCHAR2(60 BYTE) NOT NULL,

  COMUNA     VARCHAR2(30 BYTE) NOT NULL);

ALTER TABLE PERSONA ADD (

  CONSTRAINT PERSONA_PK

 PRIMARY KEY

 (RUT));

INSERT INTO PERSONA (RUT,DV,NOMBRE,PATERNO,MATERNO,DIRECCION,COMUNA)

VALUES (14364643,2,'RODRIGO','ARAYA','RAMIREZ','NICANOR PARRA 1745','QUILICURA');

INSERT INTO PERSONA (RUT,DV,NOMBRE,PATERNO,MATERNO,DIRECCION,COMUNA)

VALUES (17201704,5,'YESSENIA','CATALAN','COFRE','REVERENDO JAVIER PERO 4410','PEÑALOLEN') ;

INSERT INTO PERSONA (RUT,DV,NOMBRE,PATERNO,MATERNO,DIRECCION,COMUNA)

VALUES (15240757,2,'MARÍA','ANTILEO','CATRILEO','MANUEL ANTONIO ROMO 551','SAN RAMON') ;

INSERT INTO PERSONA (RUT,DV,NOMBRE,PATERNO,MATERNO,DIRECCION,COMUNA)

VALUES (15971739,9,'JESSICA INES','MUÑOZ','ALVARADO','PSJE, RIO IGUAZU 2018 CASAS BLANCA','PUENTE ALTO') ;

INSERT INTO PERSONA (RUT,DV,NOMBRE,PATERNO,MATERNO,DIRECCION,COMUNA)

VALUES (17764281,9,'FRANCISCO JAVIER','MIRANDA','BASTÍAS','PSJE. CERRO EL PLOMO 3908 VILLA LA CUMBRE','RENCA') ;

CREATE TABLE AUTO

(

  PATENTE  VARCHAR2(9 BYTE)  NOT NULL,

  RUT      INTEGER           NOT NULL,

  MARCA    VARCHAR2(30 BYTE) NOT NULL,

  MODELO   VARCHAR2(30 BYTE) NOT NULL,

  ANIO     DATE              NOT NULL,

  COLOR    VARCHAR2(20 BYTE) NOT NULL);

ALTER TABLE PRODSSI.AUTO ADD (

  CONSTRAINT AUTO_PK

 PRIMARY KEY

 (PATENTE));

ALTER TABLE AUTO ADD (

  CONSTRAINT FK_PER_RUT

 FOREIGN KEY (RUT)

 REFERENCES PRODSSI.PERSONA (RUT));

INSERT INTO AUTO (PATENTE,RUT,MARCA,MODELO,ANIO,COLOR)

VALUES ('BB-4205',14364643,'MAZDA','DEPORTIVO',sysdate,'VERDE');

INSERT INTO AUTO (PATENTE,RUT,MARCA,MODELO,ANIO,COLOR)

VALUES ('DD-3574',14364643,'FERRARI','CONCEPT',sysdate,'ROJO');

INSERT INTO AUTO (PATENTE,RUT,MARCA,MODELO,ANIO,COLOR)

VALUES ('XX-2875',17764281,'Honda','SUV',sysdate,'CAFE');

INSERT INTO AUTO (PATENTE,RUT,MARCA,MODELO,ANIO,COLOR)

VALUES ('XD-9548',17764281,'Aston Martin','DEPORTIVO',sysdate,'AZUL');

INSERT INTO AUTO (PATENTE,RUT,MARCA,MODELO,ANIO,COLOR)

VALUES ('GH-2548',15240757,'FERRARI','CONCEPT',sysdate,'VERDE');