Skip to content

Optimizacion SQL.sql

Pierre Germain edited this page May 11, 2017 · 3 revisions

===============

TRUCOS SQL*PLUS

===============

SQL> set heading off; -- quitar cabeceras a las tablas
SQL> r; -- repetir comando
SQL> connect SH/ORACLE -- conectar cómo otro usari/contraseña
SQL> select * from tab; -- ver tablas
SQL> select user from dual; -- ver con quien estoy conectado
SQL> desc SALES; -- ver info de la tabla SALES
SQL> SPOOL D:\carga.txt -- -- Salida a fichero:
SQL> Clear Screen
-- Ejecutar el script:
SQL>@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

-- Trazar una select
SQL> SET AUTOTRACE ON EXPLAIN;-- Mostrar traza y resultado de select
SQL> SET AUTOTRACE TRACEONLY; -- Solo mostrar la traza
SQL> SET AUTOTRACE ON STATISTICS; -- Ver estadistica

-- Para que estimaciones de tiempo sean reales
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

=====================
PLSQL INSERCION DATOS
=====================

begin
for i in 1..1000000 loop
insert into prueba values (i,'Heyyaa');
end loop;
commit;
end;


begin
for i in 1..300000 loop
insert into prueba values (i+1041000,'Heyyaa');
end loop;
commit;
end;

=======================
OPERACIONES RELANZABLES
=======================

SQL>
ALTER SESSION ENABLE RESUMABLE
TIMEOUT 600;

--> Una session para el proceso, otro para ver las siguientes tablas

DBA_RESUMABLE
V$SESSION_WAIT


SQL> 
select * from DBA_RESUMABLE where TABLESPACE_NAME like '%TS_CURSO%';
select column_name from all_tab_columns where table_name = '%DBA_RESUMABLE%';

=======
TS UNDO
=======

ORA-01555 Snapshot to old
--> necesitamos mas espacio


===============
VER TABLESPACES
===============

select * from dba_tablespaces
--> con ese comando no se ve el espacio usado


=================================================
RECUPERAR ESPACIO DE UNA TABLA BORRADA CON DELETE
=================================================
1) Activar movimiento de filas
SQL> ALTER TABLE prueba ENABLE ROW MOVEMENT;

2) Compactar el espacio
SQL> ALTER TABLE prueba SHRINK SPACE COMPACT;

3) Mover la marca de agua (HWM);
SQL> ALTER TABLE prueba SHRINK SPACE;

-- Ver vistas
DBA_TABLES
ALL_TABLES

--> ES MUY IMPORTANTE HACER ESTO PARA QUE LA TABLA ESTE VACÍA DE VERDAD!!!


=================
ANALIZAR OBJECTOS
=================

SQL> SELECT blocks, num_rows FROM dba_tables WHERE table_name like '%PRUEBA%';
-> Si devuelve NULL en cada campo es OKEY, simplemente no sabe lo que ocupa
-> Si NO devuelve filas esque no existe la tabla

SQL> ANALYZE TABLE prueba COMPUTE STATISTICS;
-> Ahora ya devolvera datos el 'select * from dba_tables' de nuestra tabla.
-> Sirve para actualizar los datos de 'dba_tables'
-> Es un proceso pesado!!!

===========
PARTICIONES
===========

-- MÉTODOS PARTICIONAMIENTO - RANGE

CREATE TABLE VENTAS_CUATRIMESTRES
	( comercial_id NUMBER(4),
	comercial_nombre VARCHAR2(40),
	region_venta VARCHAR2(40),
	importe_venta NUMBER(6),
	fecha_venta DATE)
PARTITION BY RANGE (fecha_venta)
	(PARTITION ventas_1cuat VALUES LESS THAN
		(TO_DATE('01/05/2004','DD/MM/YYYY')),
	PARTITION ventas_2cuat VALUES LESS THAN
		(TO_DATE('01/09/2004','DD/MM/YYYY')),
	PARTITION ventas_3cuat VALUES LESS THAN
		(TO_DATE('01/01/2005','DD/MM/YYYY')));

--> MAXVALUE para rango superior!!!!

-- MÉTODOS PARTICIONAMIENTO - LIST
-- Mapea los datos en particiones, definidas sobre una lista de valores del

partition key.
CREATE TABLE ventas_regiones
( comercial_id NUMBER(4),
comercial_nombre VARCHAR2(40),
region_venta VARCHAR2(40),
importe_venta NUMBER(6),
fecha_venta DATE)
PARTITION BY LIST (region_venta)
(PARTITION ventas_1region VALUES ('ASTURIAS', 'GALICIA','MADRID'),
PARTITION ventas_2region VALUES (‘ARAGON’,’CATALUÑA’,’VALENCIA’,
’BALEARES’),
PARTITION ventas_3region VALUES
(‘ANDALUCIA’,EXTREMADURA,’MURCIA’));


-- MÉTODOS PARTICIONAMIENTO - HASH

CREATE TABLE ventas_regiones
( comercial_id NUMBER(4),
comercial_nombre VARCHAR2(40),
region_venta VARCHAR2(40),
importe_venta NUMBER(6),
fecha_venta DATE)
PARTITION BY HASH (comercial_id) -- Criterio de cómo ordenar los datos en particiones
PARTITIONS 4; -- Número de particiones


-- MÉTODOS PARTICIONAMIENTO - COMPOSITE

CREATE TABLE ventas_composite_rl
( comercial_id NUMBER(4),
comercial_nombre VARCHAR2(40),
region_venta VARCHAR2(40),
importe_venta NUMBER(6),
fecha_venta DATE)
PARTITION BY RANGE (fecha_venta)
SUBPARTITION BY LIST (region_venta)
SUBPARTITION TEMPLATE (
SUBPARTITION ventas_1region VALUES ('ASTURIAS', ‘GALICIA’,’CANTABRIA’,‘MADRID’),
SUBPARTITION ventas_2region VALUES(‘ARAGON’,’CATALUÑA’,’VALENCIA’,’BALEARES’),
SUBPARTITION ventas_3region VALUES (‘ANDALUCIA’,’C.LA MANCHA’,’MURCIA’))
( PARTITION ventas_1cuat VALUES LESS THAN
(TO_DATE(‘01/05/2004’,’DD/MM/YYYY’)),
PARTITION ventas_2cuat VALUES LESS THAN
(TO_DATE(‘01/09/2004’,’DD/MM/YYYY’)),
PARTITION ventas_3cuat VALUES LESS THAN
(TO_DATE(‘01/01/2005’,’DD/MM/YYYY’)));

----------------
TABLAS TEMPORALES
-----------------

-- Mantienen los datos únicamente durante la sesión o transacción en curso.
Dos tipos:

PRESERVE: datos permanecen hasta finaliza sesión

	CREATE GLOBAL TEMPORARY TABLE prueba_temp2
	( campo1 NUMBER)
	ON COMMIT DELETE ROWS;

DELETE: datos permanecen hasta finaliza transacción

	CREATE GLOBAL TEMPORARY TABLE prueba_temp1
	( campo1 NUMBER)
	ON COMMIT PRESERVE ROWS;


=======
INDICES
=======

EJEMPLO

CREATE TABLE INDICES
(A NUMBER,
 B VARCHAR2(6),
 C VARCHAR2(40)
)
TABLESPACE TS_CURSO;

BEGIN 
 FOR I IN 1..10000 LOOP
  INSERT INTO INDICES VALUES (I,'A'||I,'AAAA'||I);
 END LOOP;
 COMMIT;
END;

SQL> SELECT * FROM INDICES WHERE A=1;

-------------------------------------
-- PLAN DE EJECUCION (MUY IMPORTANTE)
SQL> SET AUTOTRACE ON EXPLAIN;
-------------------------------------

SQL> SELECT * FROM INDICES WHERE A=1;
--> nos da  TABLE ACCESS FULL (ESO SIGNIFICA QUE SE HA ACCEDIDO A TODA LA TABLA)

-- CREACION DEL INDICE
CREATE INDEX IDX_INDICES ON INDICES(A,B,C);

SQL> SELECT * FROM INDICES WHERE A=1;
--> ahora nos da INDEX RANGE SCAN (ESO SIGNIFICA QUE NO SE HA ACCEDIDO A TODA LA TABLA) 

SQL> select count(*) from indices;
--> TABLE ACCESS FULL

SQL> select count (a) from indices;
--> TABLE ACCESS FULL

----------------------------------
EJEMPLO: CREACION INDICE
----------------------------------

SQL> CREATE INDEX IDX_C ON INDICES (C);

SQL> SELECT * FROM INDICES WHERE C='AAAAA1';
--> INDEX RANGE SCAN

SQL> SELECT * FROM INDICES WHERE UPPER(C)='AAAAA1'; 
--> TABLE ACCESS FULL (YA QUE SE HACE WHERE CON FUNCION)

----------------------
INDICE DE TIPO FUNCION
----------------------

SQL> CREATE INDEX IDX_CF ON INDICES (UPPER(C));
SQL> SELECT * FROM INDICES WHERE UPPER(C)='AAAAA1'; 
--> INDEX RANGE SCAN


-----------------------
ver Para DWH
ROLLUP
CUBE
-----------------------

=======================
PRACTICA FILAS MIGRADAS
=======================

CREATE TABLE FILAS_MIGRADAS
(A NUMBER,
 B VARCHAR2(200),
 C VARCHAR2(400))
TABLESPACE TS_CURSO;

BEGIN 
 FOR I IN 1 .. 1000 LOOP
  INSERT INTO FILAS_MIGRADAS 
  VALUES (I,'A','AA');
 END LOOP;
 COMMIT;
END;
/

-- PROVOCAR MIGRACION

(...)

---------------------------------------
-- MONOTORIZAR INDICES
---------------------------------------
-- Ver nombres de indices
SELECT INDEX_NAME
FROM ALL_INDEXES
WHERE TABLE_NAME = 'INDICES';

-- ACTIVAR MONOTORIZACION INDICES
ALTER INDEX indice MONITORING USAGE;

-- DESACTIVAR 
ALTER INDEX indice NOMONITORING USAGE;

-- Pedir el Grant al DBA lectura a la siguiente tabla:
GRANT SELECT ON V$OBJECT_USAGE TO <usuario>;

-- Monotorizar el uso:

SELECT index_name, table_name, used, start_monitoring, end_monitoring
FROM V$OBJECT_USAGE
WHERE table_name = '(...)'
AND Index_name = '(...)';

-- Monotorizar miles de indices a la vez:
SELECT 'ALTER INDEX ' || INDEX_NAME || ' MONOTORING USAGE';
FROM ALL_INDEXES 
WHERE OWNER = 'CONTABILIDAD';
---> GUARDAR EL RESULTADO EN UN TXT (ESO NOS DA LA SELECT A EJECUTAR)

--------------------------------------------------------------
-- Ejemplo MONOTORIZAR INDICES:
--------------------------------------------------------------

SELECT 'ALTER INDEX ' || INDEX_NAME || ' MONOTORING USAGE;'
FROM ALL_INDEXES 
WHERE OWNER = 'SYSTEM'
and TABLE_NAME = 'INDICES';

--Esto nos genera
ALTER INDEX IDX_INDICES MONITORING USAGE;
ALTER INDEX IDX_C MONITORING USAGE;
ALTER INDEX IDX_CF MONITORING USAGE;

-- Luego lo ejecutamos, hacemos selects, y al final hacemos:


SQL> SELECT index_name, table_name, used, start_monitoring, end_monitoring
  2  FROM V$OBJECT_USAGE
  3  WHERE table_name = 'INDICES';

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
START_MONITORING    END_MONITORING
------------------- -------------------
IDX_INDICES                    INDICES                        YES
06/01/2011 09:14:14

IDX_C                          INDICES                        NO
06/01/2011 09:14:17

IDX_CF                         INDICES                        NO
06/01/2011 09:14:29

=======================================
ORGANIZACION DE TABLAS
=======================================

Usuarios
SYS
SYSTEM

Tablas del Usuario
USER_TABLES
USER_INDEXES
USER_VIEWS

Tablas mas internas
ALL_TABLES
ALL_*

Tablas super internas
DBA_TABLES
DBA_INDEXES

Tablas Dinamicas
V$...
K$...



=================================
-- EVITAR QUERIES DE ESTE ESTILO:
=================================

=
1
=

WHERE CAMPO = 16
WHERE CAMPO = 18
----> Las tiene que volver a recompilar
-- MEJOR USAR:
WHERE CAMPO = :variable

---> Esto es asi porque existe:
'CURSOR_SHARING' que puede tomar el valor:
	EXACT
	SIMILAR
	FORCE

SQL>show parameter CURSOR_SHARING;


=
2 Tratar con Objetos es menos eficiente que con datos relacionales.
=


=========================================
-- SQL*Loader : Carga de ficheros a BD --
=========================================
-- Crear objeto destino VENTAS
SQL> CREATE TABLE VENTAS
(ID_PRODUCTO NUMBER,
ID_CLIENTE NUMBER,
ID_TIEMPO DATE);

-- Salida a fichero:
SQL> SPOOL D:\carga.txt

-- Quitar cabeceras
SQL> SET HEADING OFF;

-- guardar datos a fichero de una tabla: 
SQL> SELECT prod_id ||'#'|| cust_id ||'#'|| time_id FROM SALES

-- guardar/completar con blancos (RPAD los rellena a la derecha)
SQL> SELECT RPAD(PROD_ID,20,' ')||RPAD(CUST_ID,10,' ')|| RPAD(TIME_ID,15' ')

SQL> SPOOL OFF ------------------> NO OLVIDAR!!
-- NO OLVIDAR SPOOL OFF

-- Ir al Enterprise Manager "Cargar datos de archivo de usuario"
--> Cargamos el Fichero guardado anteriormente. Además limpiarlo!!!
--> D:\carga.txt

-- ARCHIVO DE CONTROL:
LOAD DATA
APPEND
INTO TABLE SYSTEM.VENTAS
FIELDS TERMINATED BY '#' OPTIONALLY ENCLOSED BY '"'
(
  ID_PRODUCTO INTEGER EXTERNAL,
  ID_CLIENTE INTEGER EXTERNAL,
  ID_TIEMPO DATE
)


-- Parametros
CONTROL=D:\carga.CTL
LOG=D:\carga.LOG
DATA=D:\carga.txt
DIRECT=TRUE
PARALLEL=TRUE
ERRORS=1
BAD=D:\IMPORT_SQL_LOADER.bad
DISCARD=D:\IMPORT_SQL_LOADER.dsc

--

=================================
EXPLAIN PLAN (complicado de usar)
=================================
-- Crear tabla temporal
--$ORACLE_HOME/rdbms/admin/utlxplan.sql
--D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\

create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);


-- Creacion del plan de ejecución para cierta SELECT
EXPLAIN PLAN
FOR
SELECT prod_Name FROM products;

-- Consultar el Plan Table:
-- (1)-- Script utlxpls.sql
set markup html preformat on -- (esta linea no hace falta
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

-- Ejecutar el script desde el SQL*Plus
SQL>@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql


====================
GENERACION DE TRAZAS
====================
- Siempre sacar trazas cuando se vaya a cambiar algo. No te fies nunca de los tiempos de ejecución.

-- Mirar el siguiente parametro (con el usario system)
SQL> connect system/oracle
SQL> SHOW parameter USER_DUMP_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \CLE\UDUMP
-- PASOS:
-- 1 Activar la traza
SQL> ALTER SESSION SET SQL_TRACE = TRUE;

-- 2 Hacer SELECT
SQL> Select * from INDICES where a = 100;

-- 3 Desactivar la traza
SQL> ALTER SESSION SET SQL_TRACE = FALSE;

-- 4 Dar formato al siguiente fichero (el último modificado del siguiente directorio)
D:\ORACLE\PRODUCT\10.2.0\ADMIN\CLE\UDUMP

-- 5 TKPROF:
$ORACLE_HOME/bin/tkprof
D:\oracle\product\10.2.0\db_1\BIN\tkprof.exe
-- Desde MSDOS:
>tkprof fichero_origen.trc fichero_salida.txt

-- 6 Buscar nuestra Query en el fichero fichero_salida.txt

********************************************************************************

Select * 
from
 INDICES where a = 100

--        (*)                              (*)     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          1          3          0           0
Execute      1      0.00       0.00          0          0          0           0 --<---- OP. INSERT/DELETE/UPDATE
Fetch        2      0.00       0.00          0          3          0           1 --<---- OP. SELECCION
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          1          6          0           1

Misses in library cache during parse: 1 --                    <-----  'FALLOS EN LA COMPILACION'
Optimizer mode: ALL_ROWS  --                                  <----- Optimizador usado (ver explicación)
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX RANGE SCAN IDX_INDICES (cr=3 pr=0 pw=0 time=15 us)(object id 52547)

********************************************************************************

--(*) Columnas Importantes
 disk: --(*) las veces que se ha accedido a disco
 count: --(*) veces que se ha realizado
 query: buffers leidos para la select
 current: buffers leidos distintas a la select
 


==============
OPTIMIZER_MODE
==============

Antiguamente 2 tipos de optimizadores
 - Reglas (deprecated ... Ojo: Que puede ser mejor en algunos casos)
 - CBO (basado en costes). Uso de estadisticas

3 Tipos de optimizadores basado en CBO: 'OPTIMIZER_MODE'
	- ALL_ROWS
	- FIRST_ROWS_n 
		-- Ej) FIRST_ROWS 10 dev. las 10 primeras filas lo mas rapido posible
			---> Ejemplo, en el TOAD que solo devuelve algunas filas.
	- FIRST_ROWS

Estos parametros son globales, afectan a toda la BBDD. Pero se puede forzar cierta select para que solo afecte asimisma


==============
CURSOR_SHARING
==============
Tipos:
--> EXACT
--> SIMILAR
--> FORCE



======================================
======================================
PRACTICA TABLAS ORGANIZADAS POR INDICE
======================================
======================================

-- con indice
CREATE TABLE PRODUCTOS_IOT
( PRODUCTO_ID NUMBER,
NOMBRE VARCHAR2(200),
FECHA_CREACION DATE,
UNIDADES NUMBER(10),
CONSTRAINT PK_PRODUCTOS PRIMARY KEY (PRODUCTO_ID, NOMBRE))
ORGANIZATION INDEX TABLESPACE TS_CURSO;

-- sin indice
CREATE TABLE PRODUCTOS
( PRODUCTO_ID NUMBER,
NOMBRE VARCHAR2(200),
FECHA_CREACION DATE,
UNIDADES NUMBER(10),
CONSTRAINT PK_PRODUCTOSS PRIMARY KEY (PRODUCTO_ID, NOMBRE))
TABLESPACE TS_CURSO;

SQL> SET AUTOTRACE ON EXPLAIN;-- Mostrar traza y resultado de select
SQL> select * from productos;

--> TABLE ACCESS FULL

SQL> select * from productos_iot

--> INDEX FAST FULL SCAN

==============
METODOS ACCESO
==============

-- desbloquear usuario
SYSTEM> ALTER USER OE IDENTIFIED BY ORACLE ACCOUNT UNLOCK
--> OE es usuario, ORACLE es contraseña 

SYSTEM> SET AUTOTRACE TRACEONLY;
SYSTEM> SELECT * FROM OE.EMPLOYEES;


********************************************************************************

SQL> desc oe.employees;
 Nombre                                    ¿Nulo?   Tipo
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

********************************************************************************

SQL> SELECT * FROM OE.EMPLOYEES
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 |

********************************************************************************


SQL> SELECT FIRST_NAME, EMPLOYEE_ID FROM OE.employees WHERE employee_id = 101;

|   0 | SELECT STATEMENT            |               |     1 |    11 |     1   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    11 |     1   (0
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0



********************************************************************************


SQL> SELECT FIRST_NAME, EMPLOYEE_ID FROM OE.employees WHERE employee_id > 101 and employee_id < 150; 

|   0 | SELECT STATEMENT            |               |    49 |   539 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    49 |   539 |     2   (0
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    49 |       |     1   (0

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

********************************************************************************

SQL> SELECT FIRST_NAME, EMPLOYEE_ID FROM OE.employees

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  1177 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  1177 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

=====
NOTA:
=====

--> En el método anterior recorre TODA la tabla, pero si la tabla es GRANDE no lo haría.

********************************************************************************

SQL> SELECT EMPLOYEE_ID FROM OE.employees

--> ahora accede a todo el INDICE, no la tabla. Eso es mucho mas rápido que acceder a toda la tabla

|   0 | SELECT STATEMENT |               |   107 |   428 |     1   (0)| 00:00:01
|   1 |  INDEX FULL SCAN | EMP_EMP_ID_PK |   107 |   428 |     1   (0)| 00:00:01
--------------------------------------------------------------------------------


********************************************************************************

=====
ROWID
=====
--> Acceso mas rapido
--> Los ROWID son fijos, pero si se modifican los registros puede cambiar. FUCK!!!
--> mucho cuidado al usarlo!!!

SQL> SET AUTOTRACE OFF
SQL> SELECT ROWID,employee_id,first_name FROM OE.employees; 
SQL> SET AUTOTRACE ON
SQL> SELECT FIRST_NAME FROM OE.EMPLOYEES WHERE ROWID = 'AAAMg6AAFAAAABYABV';

FIRST_NAME
--------------------
Alexis


|   0 | SELECT STATEMENT           |           |     1 |    19 |     1   (0)| 00
|   1 |  TABLE ACCESS BY USER ROWID| EMPLOYEES |     1 |    19 |     1   (0)| 00




===============================
METODOS DE UNIÓN (JOIN METHODS)
================================


SYSTEM> desc oe.departments;

 Nombre                                    ¿Nulo?   Tipo
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

SQL> SELECT department_name, first_name, last_name 
FROM oe.employees E, oe.departments D
WHERE E.department_id = D.department_id;


|   0 | SELECT STATEMENT             |             |   106 |  3604 |     4   (0)
|   1 |  NESTED LOOPS                |             |   106 |  3604 |     4   (0)
|   2 |   TABLE ACCESS FULL          | EMPLOYEES   |   107 |  1926 |     3   (0)
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)

--> hay dos metodos de acceso. en realidad hace un NESTED LOOP JOIN
-->> VEMOS QUE LA PRIMERA TABLA ES LA OUTER, LA SEGUNA LA INER

-- Ahora cambiamos el orden de las tablas

SQL> SELECT department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE E.department_id = D.department_id;

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

|   0 | SELECT STATEMENT             |             |   106 |  3604 |     4   (0)
|   1 |  NESTED LOOPS                |             |   106 |  3604 |     4   (0)
|   2 |   TABLE ACCESS FULL          | EMPLOYEES   |   107 |  1926 |     3   (0)
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)

--> Mismo metodo de acceso que antes. Eso es asi porque el orden no importa!!! 

-- Ahora cambiamos el orden de los join

SQL> SELECT department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id ;

|   0 | SELECT STATEMENT             |             |   106 |  3604 |     4   (0)
|   1 |  NESTED LOOPS                |             |   106 |  3604 |     4   (0)
|   2 |   TABLE ACCESS FULL          | EMPLOYEES   |   107 |  1926 |     3   (0)
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)

--> El orden de las Join tampoco influye!!!


=                                  =
==                                ==
=====                         ======
====================================
             HINTS
====================================
=====                         ======
==                                ==
=                                  =


-- Son directivas que le damos al optimizador para modificar su comportamiento.
-- Se ponen justo despues de la palabra reservada. Parecen comentarios pero no lo son.
-- Tipos:
---> ORDERED   ---> para forzar el orden de las tablas del FROM
---> USE_NL (campos)    ---> para usar NESTED LOOP
---> USE_MERGE (campos) ---> para usar SORT MERGE
---> NO_USE_MERGE (campos) ---> NO USAR MERGE
---> USE_HASH ()
---> NO_USE_HASH()
---> FULL (tabla) ---> acceder a toda la tabla
---> INDEX (tabla indice) ----> acceder a la tabla por cierto indice
---> NO_INDEX (tabla indice)
---> INDEX_ASC (tabla indice) ---> acceder ascendientemente por la tabla
---> INDEX_DESC(tabla indice)
---> INDEX_RS (tabla indice) ---> Forzar Range SCAN
---> NO_INDEX_RS (tabla indice)




-------------------------
SINTAXIS:
SELECT /*+ HINT */ campos
-------------------------


--------------
TIPO:
/*+ ORDERED */
--------------

--> Para respetar el orden de las tablas del FROM

===========
EJEMPLO UNO
===========

(1) -- NORMAL

SELECT  department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id ;

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

|   0 | SELECT STATEMENT             |             |   106 |  3604 |     4   (0)
|   1 |  NESTED LOOPS                |             |   106 |  3604 |     4   (0)
|   2 |   TABLE ACCESS FULL          | EMPLOYEES   |   107 |  1926 |     3   (0)
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)

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

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

(2) -- CAMBIANDO EL PLAN DE EJECUCIÓN

SELECT  /*+ ORDERED */ department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id ;

|   0 | SELECT STATEMENT             |             |   106 |  3604 |     6  (17)
|   1 |  MERGE JOIN                  |             |   106 |  3604 |     6  (17)
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)
|*  4 |   SORT JOIN                  |             |   107 |  1926 |     4  (25)
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1926 |     3   (0)


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")



===========
EJEMPLO DOS
===========

(1) -- NORMAL

SELECT  department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id 
AND    D.department_id = 10;


DEPARTMENT_NAME                FIRST_NAME           LAST_NAME
------------------------------ -------------------- -------------------------
Administration                 Jennifer             Whalen

|   0 | SELECT STATEMENT             |                   |     1 |    34 |     2
|   1 |  NESTED LOOPS                |                   |     1 |    34 |     2
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |     1 |    16 |     1
|*  3 |    INDEX UNIQUE SCAN         | DEPT_ID_PK        |     1 |       |     0
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |    18 |     1
|*  5 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     0

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPARTMENT_ID"=10)
   5 - access("E"."DEPARTMENT_ID"=10)


---> Vemos que actua adecuadamente

(2) -- OUTER JOIN

SELECT  department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id (+); 
--AND    D.department_id = 10;

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

|   0 | SELECT STATEMENT             |             |   106 |  3604 |     6  (17)
|   1 |  MERGE JOIN OUTER            |             |   106 |  3604 |     6  (17)
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)
|*  4 |   SORT JOIN                  |             |   107 |  1926 |     4  (25)
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1926 |     3   (0)

SELECT  department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id (+); 
AND    D.department_id = 10;



|   0 | SELECT STATEMENT             |             |   106 |  3604 |     6  (17)
|   1 |  MERGE JOIN OUTER            |             |   106 |  3604 |     6  (17)
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)
|*  4 |   SORT JOIN                  |             |   107 |  1926 |     4  (25)
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1926 |     3   (0)


(3) -- USE_NL 

SELECT /*+ USE_NL (E D) */department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id (+); 


|   0 | SELECT STATEMENT             |                   |   106 |  3604 |    10
|   1 |  NESTED LOOPS OUTER          |                   |   106 |  3604 |    10
|   2 |   TABLE ACCESS FULL          | DEPARTMENTS       |    27 |   432 |     3
|   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     4 |    72 |     1
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0


(4) -- USE_MERGE 

SELECT /*+ USE_MERGE (E D) */department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id (+); 


|   0 | SELECT STATEMENT             |             |   106 |  3604 |     6  (17)
|   1 |  MERGE JOIN OUTER            |             |   106 |  3604 |     6  (17)
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)
|*  4 |   SORT JOIN                  |             |   107 |  1926 |     4  (25)
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1926 |     3   (0)

(5) -- NO_USE_MERGE

SELECT /*+ NO_USE_MERGE (E D) */department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id (+); 

|   0 | SELECT STATEMENT   |             |   106 |  3604 |     7  (15)| 00:00:01
|*  1 |  HASH JOIN OUTER   |             |   106 |  3604 |     7  (15)| 00:00:01
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01


(6) -- NO_USE_MERGE y NO_USE_NL juntos!

SELECT /*+ NO_USE_MERGE (E D), NO_USE_NL (E D) */department_name, first_name, last_name 
FROM  oe.departments D, oe.employees E
WHERE  D.department_id = E.department_id (+); 

|   0 | SELECT STATEMENT   |             |   106 |  3604 |     7  (15)| 00:00:01
|*  1 |  HASH JOIN OUTER   |             |   106 |  3604 |     7  (15)| 00:00:01
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01



======
EJEMPLO
=======

Tomar id de empleado y usar FULL e INDEX


(1)

SQL> select employee_id from oe.employees where employee_id = 101;


|   0 | SELECT STATEMENT  |               |     1 |     4 |     0   (0)| 00:00:0
|*  1 |  INDEX UNIQUE SCAN| EMP_EMP_ID_PK |     1 |     4 |     0   (0)| 00:00:0


(2)

 select /*+ FULL (employees) */ employee_id from oe.employees where employee_id = 101;


-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     4 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |     4 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

(3)

 select /*+ INDEX (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id = 101;


|   0 | SELECT STATEMENT  |               |     1 |     4 |     0   (0)| 00:00:0
|*  1 |  INDEX UNIQUE SCAN| EMP_EMP_ID_PK |     1 |     4 |     0   (0)| 00:00:0



(4)

 select /*+ INDEX_DESC (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id = 101;

|   0 | SELECT STATEMENT            |               |     1 |     4 |     0   (0
|*  1 |  INDEX RANGE SCAN DESCENDING| EMP_EMP_ID_PK |     1 |     4 |     0   (0


(5)

 select /*+ INDEX_RS (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id = 101;

|   0 | SELECT STATEMENT  |               |     1 |     4 |     0   (0)| 00:00:0
|*  1 |  INDEX UNIQUE SCAN| EMP_EMP_ID_PK |     1 |     4 |     0   (0)| 00:00:0


(6)

 select /*+ INDEX_RS (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id > 101;

|   0 | SELECT STATEMENT |               |   106 |   424 |     1   (0)| 00:00:01
|*  1 |  INDEX RANGE SCAN| EMP_EMP_ID_PK |   106 |   424 |     1   (0)| 00:00:01




=                                  =
==                                ==
=====                         ======
====================================
         MODO OPTIMIZADOR
====================================
=====                         ======
==                                ==
=                                  =

OPTIMIZAR_MODE:
---> ALL_ROWS
---> FIRST_ROWS
---> FIRST_ROWS_N 

Se puede cambiar el modo del optimizador por Session:

SQL> ALTER SESSION SET OTIMIZER_MODE = FIRST_ROWS;

Tambien se puede cambiar a nivel de HINTS el modo del optimizador:

--> /*+ ALL_ROWS */
--> /*+ FIRST_ROWS */
--> /*+ FIRST_ROWS(25) */

Los siguientes se van a quitar de Oracle, puede haber casos que son dpm, pero cuidado!
--> /*+ RULE */
--> /*+ CHOOSE */

=========
Ejemplos:
=========

-- Para que estimaciones de tiempo sean reales
 ALTER SYSTEM FLUSH BUFFER_CACHE;
 ALTER SYSTEM FLUSH SHARED_POOL;


(1)
select * from oe.employees ;

107 filas seleccionadas.


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7276 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
       1036  recursive calls
          0  db block gets
        256  consistent gets
         35  physical reads
          0  redo size
       9800  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
        107  rows processed



**********************************************************************

(2)
 ALTER SYSTEM FLUSH BUFFER_CACHE;
 ALTER SYSTEM FLUSH SHARED_POOL;
 select  /*+ ALL_ROWS */ * from oe.employees ;


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7276 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
       1056  recursive calls
          0  db block gets
        259  consistent gets
         29  physical reads
          0  redo size
       9800  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
        107  rows processed

---> Vemos que ha EMPEORADO "recursive calls" al usar el HINT.
---> El Oprtimizador de Oracle va bien

**********************************************************************
(3)

 ALTER SYSTEM FLUSH BUFFER_CACHE;
 ALTER SYSTEM FLUSH SHARED_POOL;
 select count (*) from indices;

  COUNT(*)
----------
      9999


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3620601557

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| INDICES |  9999 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Estadísticas
----------------------------------------------------------
        583  recursive calls
          0  db block gets
        179  consistent gets
         54  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed


**********************************************************************
(4)
 ALTER SYSTEM FLUSH BUFFER_CACHE;
 ALTER SYSTEM FLUSH SHARED_POOL;
select /*+ ALL_ROWS */ count (*) from indices;


  COUNT(*)
----------
      9999


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3620601557

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| INDICES |  9999 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Estadísticas
----------------------------------------------------------
        583  recursive calls
          0  db block gets
        179  consistent gets
         52  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

---> como por defecto es ALL_ROWS no cambia el "recursive calls"

**********************************************************************

(5)
 ALTER SYSTEM FLUSH BUFFER_CACHE;
 ALTER SYSTEM FLUSH SHARED_POOL;
select /*+ FIRST_ROWS(100) */ count (*) from indices;


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3620601557

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| INDICES |  9999 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Estadísticas
----------------------------------------------------------
        974  recursive calls
          0  db block gets
        239  consistent gets
         62  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

--> Al poner FIRS_ROWS los recursive calls son mayores, pero se supone que las primeras filas se consiguen antes.


=========================================
	EJEMPLO/PRACTICA:
=========================================

Tabla departments
Tabla employees

Se unen por department_id

Mostar department_name, department_id
que tienen al menos un empleado


===================
MI FORMA (LA BUENA)
===================

SQL> select D.department_name, D.department_id
     from oe.departments D
     where exists (select 1 
     	from oe.employees E
    	where E.department_id = D.department_id);

--> Lo bueno de esta forma es que en el exist en cuanto una fila cumple ya pasa del resto.

DEPARTMENT_NAME                DEPARTMENT_ID
------------------------------ -------------
Administration                            10
Marketing                                 20
Purchasing                                30
Human Resources                           40
Shipping                                  50
IT                                        60
Public Relations                          70
Sales                                     80
Executive                                 90
Finance                                  100
Accounting                               110

11 filas seleccionadas.


Plan de Ejecución

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    10 |   190 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |                   |    10 |   190 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E2"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


Estadísticas
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         37  consistent gets
          0  physical reads
          0  redo size
        727  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed


=========================================
	EJEMPLO/PRACTICA:
=========================================

Tabla departments
Tabla employees

Se unen por department_id

Departamente que no tienen empleado

===================
MI FORMA (LA BUENA)
===================

SQL> select D.department_name, D.department_id
     from oe.departments D
     where NOT exists (select 1 
     	from oe.employees E
    	where E.department_id = D.department_id);


DEPARTMENT_NAME                DEPARTMENT_ID
------------------------------ -------------
Treasury                                 120
Corporate Tax                            130
Control And Credit                       140
Shareholder Services                     150
Benefits                                 160
Manufacturing                            170
Construction                             180
Contracting                              190
Operations                               200
IT Support                               210
NOC                                      220
IT Helpdesk                              230
Government Sales                         240
Retail Sales                             250
Recruiting                               260
Payroll                                  270

16 filas seleccionadas.


Plan de Ejecución

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


Estadísticas
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         37  consistent gets
          0  physical reads
          0  redo size
        959  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

==========
OTRA FORMA
==========

SQL> select D.department_name, D.department_id
     from oe.departments D
     where D.department_id NOT IN
	(SELECT E.department_id
	FROM oe.employees E
	where E.department_id is not null); ---- Si no pones esto se jode!!!!



----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID" IS NOT NULL)


Estadísticas
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         37  consistent gets
          0  physical reads
          0  redo size
        959  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed



====
OTRA
====

usar el MINUS, pero no es nada eficiente y además solo puedes sacar el department_id




===========================
      ESTADISTICAS
===========================

-------
ANALIZE
-------


ANALYZE TABLE nombre_tabla COMPUTE STATISTICS
--> Dev BLOCKS-NUM_ROWS, si dev. nada hay que analizarla
--> Columna LAST_ANALYZED nos dice cuando ha sido analizado por última vez (DD/MM/YYYY HH:MM:SS)

Ejemplo:

SQL> SELECT BLOCKS, NUM_ROWS, LAST_ANALYZED
FROM ALL_TABLES
WHERE TABLE_NAME = 'VENTAS';


    BLOCKS   NUM_ROWS LAST_ANA
---------- ---------- --------


-- Al no dev. nada hacemos analisis


SQL> ANALYZE TABLE VENTAS COMPUTE STATISTICS

SQL> SELECT BLOCKS, NUM_ROWS, LAST_ANALYZED
FROM ALL_TABLES
WHERE TABLE_NAME = 'VENTAS';

    BLOCKS   NUM_ROWS LAST_ANA
---------- ---------- --------
      2639     918843 02/06/11


SQL> select count (*) from ventas;


 COUNT(*)
---------
   918843

-- Borrar estadisticas


SQL> ANALYZE TABLE VENTAS DELETE STATISTICS;


-- Recorrer un 1% de la tabla:

SQL> ANALYZE TABLE VENTAS ESTIMATE STATISTICS SAMPLE 1 PERCENT;

SQL> SELECT BLOCKS, NUM_ROWS, LAST_ANALYZED
FROM ALL_TABLES
WHERE TABLE_NAME = 'VENTAS';

    BLOCKS   NUM_ROWS LAST_ANA
---------- ---------- --------
      2639     918843 02/06/11

---> Cómo esta tabla nunca ha tenido actualizaciones pues el resultado es el mismo


================
ANALIZAR INDICES ================

SQL> ANALYZE TABLE VENTAS COMPUTE STATISTICS FOR ALL INDEXES;


============
CREAR SCRIPT
============

SELECT 'ANALYZE TABLE' || OWNER || '.' || TABLE_NAME || 'COMPUTE STATISTICS;'
FROM ALL_TABLES
WHERE OWNER = 'OE';

--> Esto devuelve el siguiente script:

ANALYZE TABLEOE.WAREHOUSESCOMPUTE STATISTICS;
ANALYZE TABLEOE.ORDER_ITEMSCOMPUTE STATISTICS;
ANALYZE TABLEOE.ORDERSCOMPUTE STATISTICS;
ANALYZE TABLEOE.PRODUCT_INFORMATIONCOMPUTE STATISTICS;
ANALYZE TABLEOE.PROMOTIONSCOMPUTE STATISTICS;
ANALYZE TABLEOE.SYS_IOT_OVER_52439COMPUTE STATISTICS;
ANALYZE TABLEOE.SYS_IOT_OVER_52444COMPUTE STATISTICS;
ANALYZE TABLEOE.PRODUCT_REF_LIST_NESTEDTABCOMPUTE STATISTICS;
ANALYZE TABLEOE.SUBCATEGORY_REF_LIST_NESTEDTABCOMPUTE STATISTICS;
ANALYZE TABLEOE.PRODUCT_DESCRIPTIONSCOMPUTE STATISTICS;
ANALYZE TABLEOE.INVENTORIESCOMPUTE STATISTICS;
ANALYZE TABLEOE.CUSTOMERSCOMPUTE STATISTICS;



======================================
RECOPILAR ESTADISTICAS DE TODA LA BBDD
======================================

Para dar permisos de hacerlo hay que ejecutar lo siguiente desde SYSTEM:
GRANT EXECUTE ON DBMS_STATS TO BOS_MNTRREY;


1. DBMS_STATS.GATHER_DATABASE_STATS --> CUIDADO AL USARLO, SACA INFO DE TODO
2. DBMS_STATS.GATHER_DICTIONARY_STATS
3. DBMS_STATS.GATHER_SCHEMA_STATS ('OE') --> Saca estadísticas de esquemas
4. DBMS_STATS.GATHER_SYSTEM_STATS ('SYSTEM','VENTAS') --> Todos los objetos de SYSTEM
5. DBMS_STATS.GATHER_TABLE_STATS ('OE','ORDERS')
6. DBMS_STATS.GATHER_INDEX_STATS


==========================
VER SELECTS MAS PESADAS
=======================

SELECT SQL_TEXT, DISK_READS, PARSE_CALLS, OPTIMIZER_MODE,
OTPIMIZER_COST, CPU_TIME, ELAPSED_TIME
FROM V$SQLAREA
ORDER BY DISK_READS DESC;

-- A nivel de Administración
V$SESSION ----> En esta vista vemos sesiones abiertas
V$PROCESS ----> Cada proceso ejecutandose ahora mismo e Info de la memoria PGA de cada proceso
V$SYSSTAT ----> Info de estádisticas del sistema
V$SESSTAT ----> Estadistica de la sesión



===============
TABLAS EXTERNAS
===============


 drop table ext_ventas;

CREATE TABLE EXT_VENTAS
(ID_PRODUCTO varchar2(30),
ID_CLIENTE NUMBER,
ID_TIEMPO NUMBER)
ORGANIZATION EXTERNAL
(DEFAULT DIRECTORY DIR_SQLOADER
 ACCESS PARAMETERS
  (RECORDS DELIMITED BY newline
   FIELDS TERMINATED BY '#' )
 LOCATION ('carga.txt')
);

select count (*) from ext_ventas;

Clone this wiki locally