questo link - WordPress.com

L'immaginazione è
più importante della
conoscenza. La
conoscenza è
limitata,
l'immaginazione
abbraccia il mondo,
stimolando il
progresso, facendo
nascere l'evoluzione.
( A. Einstein )
1
Di cosa parleremo
Le BASI DI DATI : le
fondamenta dei moderni sistemi
Software
2
Mi presento
Paolo Castelletti
– Esperienza: 15 anni maturati in aziende IT in
ambito internazionale e Italiano, sia come libero
professionista che come dipendente;
– Ruoli: Responsabile Data Base Administration,
DBA, Project Manager.
– [email protected]
– [email protected]
– paolocastle.wordpress.com
3
La mia ditta
Chi siamo:
Aspasiel è una Società del Gruppo Acciai Speciali
Terni. Fornisce la sua competenza nel campo delle
soluzioni ICT (Information and Communication
Technology) e servizi infrastrutturali per aziende e
gruppi industriali. La forza di Aspasiel è nelle capacità
e nel know-how del suo personale.
La Mission:
I dati e le informazioni costituiscono il patrimonio di
un’Azienda.
Le minacce informatiche, i guasti, i disastri e gli errori
utente mettono a repentaglio la loro sicurezza,
Disponibilità, Integrità, Riservatezza.
L’obiettivo è supportare le aziende fornendo servizi
IT di alta qualità e permettendogli di concentrare
tutte le energie nella crescita del loro Business.
4
Personaggi Notevoli
Lawrence Joseph "Larry" Ellison
Edgar Frank "Ted" Codd
5
Le architetture multitier
6
La diffusione dei RDBMS oggi
7
Chi lavora con il RDBMS
DB
Administrator
8
DB Developer
Torniamo alla teoria : come progettiamo un DB oggi ?
Modello ER
Normalizzazione
Decomposizione dello
schema di relazioni
Obiettivi:
Eliminare la ridondanza
dati
Rappresentare
correttamente tutto lo
schema di relazioni
9
Mai sentito parlare di “Forme Normali?”
10
DB non normalizzato
Contacts
11
Name
Company
Address
Phone1
Phone2
Phone3
ZipCode
Joe
ABC
123
5532
2234
3211
12345
Jane
XYZ
456
3421
Chris
PDQ
789
2341
14454
6655
14423
Prima forma normale
Contacts
Id
Name
Company
Address
Phone
ZipCode
1
Joe
ABC
123
5532
12345
1
Joe
ABC
123
2234
12345
1
Joe
ABC
123
3211
12345
2
Jane
XYZ
456
3421
14454
3
Chris
PDQ
789
2341
14423
3
Chris
PDQ
789
6655
14423
Benefits: Now we can have infinite phone numbers or
company addresses for each contact.
Drawback: Now we have to type in everything over and over
again. This leads to inconsistency, redundancy and wasting
space. Thus, the second normal form…
12
Seconda forma normale
People
PhoneNumbers
PhoneID
Id
Phone
1
1
5532
2
1
2234
3
1
3211
4
2
3421
5
3
2341
6
3
6655
13
Id
Name
Company
Address
Zip
1
Joe
ABC
123
12345
2
Jane
XYZ
456
14454
3
Chris
PDQ
789
14423
Terza forma normale
Elimina i campi che non
dipendono dalle PK
People
Id
Name
AddressID
1
Joe
1
2
Jane
2
3
Chris
3
PhoneNumbers
PhoneID
Id
Phone
1
1
5532
2
1
2234
3
1
3211
4
2
3421
5
3
2341
6
3
6655
14
Address
AddressID
Company
Address
Zip
1
ABC
123
12345
2
XYZ
456
14454
3
PDQ
789
14423
Tipi di relazioni
Uno a Uno
One row of a table matches exactly to another
One person, one id number, one address
Uno a Molti
One row of a table matches many of another
One person, many phone numbers
Molti a Molti
One row may match many of another or many rows match one row of
another
15
Quarta forma normale
In una relazione molti-a-molti, entità
indipendenti non possono essere
memorizzate nella stessa tabella.
People
Id
Name
AddressID
1
Joe
1
2
Jane
2
3
Chris
3
PhoneID
Phone
1
5532
2
2234
3
3211
4
3421
5
2341
6
6655
PhoneRelations
Address
16
PhoneNumbers
PhoneRelID
Id
PhoneID
1
1
1
2
1
2
AddressID
Company
Address
Zip
3
1
3
1
ABC
123
12345
4
2
4
2
XYZ
456
14454
5
3
5
3
PDQ
789
14423
6
3
6
Quindi … perché normalizzare una base dati?
1. Aumenta l’integrità dei dati
2. Riduce la ridondanza
3. Migliora l’efficienza nell’accesso ai dati
4. Migliora la scalabilità dell’applicazione
5. Anche se “è un lavoraccio” paga sul
lungo termine
18
Non serve essere “talebani relazionali” basta ricordarsi …
• Tenete a mente il concetto di normalizzazione
• Non replicare i dati in tabelle
• Se infrangete le regole, siate consapevoli del
motivo per cui lo fate e fatelo per una buona
ragione (la pigrizia non lo è).
19
Fino a che punto infrangere le regole relazionali? Un caso esemplare
20
Fino a che punto infrangere le regole relazionali? Un caso esemplare
21
Fino a che punto infrangere le regole relazionali? Un caso esemplare
OLTP System
Online Transaction Processing
(Operational System)
OLAP System
Online Analytical Processing
(Data Warehouse)
Source of data
Operational data; OLTPs are the original source
of the data.
Purpose of data
To control and run fundamental business tasks
Consolidation data; OLAP data comes from the
various OLTP Databases
To help with planning, problem solving, and
decision support
Multi-dimensional views of various kinds of
business activities
What the data
Inserts and Updates
Queries
Reveals a snapshot of ongoing business
processes
Short and fast inserts and updates initiated by
end users
Relatively standardized and simple queries
Returning relatively few records
Processing Speed
Typically very fast
Space Requirements
Can be relatively small if historical data is
archived
Database Design
Highly normalized with many tables
22
Periodic long-running batch jobs refresh the data
Often complex queries involving aggregations
Depends on the amount of data involved; batch
data refreshes and complex queries may take
many hours; query speed can be improved by
creating indexes
Larger due to the existence of aggregation
structures and history data; requires more indexes
than OLTP
Typically de-normalized with fewer tables; use of
star and/or snowflake schemas
R.D.B.M.S.
Dobbiamo sapere :
•Le potenzialità del RDBMS
•Come sono organizzati i dati
•Come viene gestita la concorrenza
23
Sql Server
24
Oracle
25
MySQL
26
Data Manipulation Language
A DML statement is executed when you:
Add new rows to a table
Modify existing rows in a table
Remove existing rows from a table
A transaction consists of a collection of DML statements that form
a logical unit of work.
27
The INSERT Statement
Add new rows to a table by using the INSERT statement.
INSERT INTO table [(column [, column...])]
Only one row(value
is inserted
a time with this syntax.
VALUES
[, at
value...]);
28
Inserting Special Values
The SYSDATE function records the current date and time.
SQL> INSERT INTO
2
3
4 VALUES
5
6
1 row created.
29
emp (empno, ename, job,
mgr, hiredate, sal, comm,
deptno)
(7196, 'GREEN', 'SALESMAN',
7782, SYSDATE, 2000, NULL,
10);
Inserting Specific Date Values
Add a new employee.
SQL> INSERT INTO
2 VALUES
3
4
1 row created.
emp
(2296,'AROMANO','SALESMAN',7782,
TO_DATE('FEB 3, 97', 'MON DD, YY'),
1300, NULL, 10);
• Verify your addition.
EMPNO ENAME
JOB
MGR
HIREDATE SAL COMM DEPTNO
----- ------- -------- ---- --------- ---- ---- -----2296 AROMANO SALESMAN 7782 03-FEB-97 1300
10
30
Inserting Values by Using Substitution Variables
Create an interactive script by using SQL*Plus substitution
parameters.
SQL> INSERT INTO
2 VALUES
3
dept (deptno, dname, loc)
(&department_id,
'&department_name', '&location');
Enter value for department_id: 80
Enter value for department_name: EDUCATION
Enter value for location: ATLANTA
1 row created.
31
Updating Rows in a Table
Specific row or rows are modified when you specify the WHERE
clause.
SQL> UPDATE emp
2 SET
deptno = 20
3 WHERE
empno = 7782;
1 row updated.
All rows in the table are modified if you omit the WHERE clause.
SQL> UPDATE employee
2 SET
deptno = 20;
14 rows updated.
32
Deleting Rows from a Table
Specific rows are deleted when you specify the WHERE clause.
SQL> DELETE FROM
department
2 WHERE
dname = 'DEVELOPMENT';
All rows in the table are deleted if you omit the WHERE clause.
1 row deleted.
SQL> DELETE FROM
4 rows deleted.
33
department;
JOIN
34
INNER JOINS
SELECT EE.NAME, DD.NAME , …
FROM EMPLOYEES AS EE INNER JOIN DEPARTMENTS AS DD
ON (EE.DEP_ID = DD.DEP_ID)
WHERE …
SELECT EE.NAME, DD.NAME , …
FROM EMPLOYEES AS EE , DEPARTMENTS AS DD
USING (DEP_ID)
WHERE …
SELECT EE.NAME, DD.NAME , …
FROM EMPLOYEES AS EE , DEPARTMENTS AS DD
WHERE(EE.DEP_ID = DD.ID)
AND …
35
OUTER JOINS
SELECT EE.NAME, DD.NAME , …
FROM EMPLOYEES AS EE LEFT OUTER JOIN DEPARTMENTS AS DD
ON (EE.DEP_ID = DD.DEP_ID)
WHERE …
SELECT EE.NAME, DD.NAME , …
FROM EMPLOYEES AS EE RIGHT OUTER JOIN DEPARTMENTS AS DD
ON (EE.DEP_ID = DD.DEP_ID)
WHERE …
SELECT EE.NAME, DD.NAME , …
FROM EMPLOYEES AS EE FULL OUTER JOIN DEPARTMENTS AS DD
ON (EE.DEP_ID = DD.DEP_ID)
WHERE …
36
SORTING RESULTS , PSEUDOCOLUMNS
SELECT EE.NAME, EE.SURNAME , ROWID
FROM EMPLOYEES AS EE
WHERE …
ORDER BY EE.SURNAME DESC , EE.NAME
SELECT NAME, SURNAME , ROWNUM
FROM EMPLOYEES
ORDER BY SURNAME
SELECT NAME, SURNAME
FROM (
SELECT NAME, SURNAME , ROWNUM
FROM EMPLOYEES
ORDER BY SURNAME) QQ
WHERE QQ.ROWNUM >= 10
AND QQ.ROWNUM <= 20;
37
Operatori insiemistici SQL
SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;
SELECT product_id FROM order_items
UNION ALL
SELECT product_id FROM inventories;
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;
38
SQL aggregate functions
SELECT DEPARTMENT_ID , COUNT (*)
FROM EMPLOYEES
WHERE …
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 6 ;
39
•
•
•
•
•
•
•
SUM
MAX
MIN
AVG
SDEV
VARIANCE
PERCENT_RANK
Cosa accade a una istruzione SQL in una istanza
40
Transazioni … ACID
41
Database Transactions
Consist of one of the following statements:
DML statements that make up one consistent change to the data
One DDL statement
One DCL statement
42
Database Transactions
Begin when the first executable SQL statement is executed
End with one of the following events:
COMMIT or ROLLBACK is issued
DDL or DCL statement executes (automatic commit)
User exits
System crashes
43
Controlling Transactions
COMMIT
Transaction
INSERT
Savepoint A
UPDATE
INSERT
DELETE
Savepoint B
ROLLBACK to Savepoint B
ROLLBACK to Savepoint A
ROLLBACK
44
Implicit Transaction Processing
An automatic commit occurs under the following circumstances:
DDL statement is issued
DCL statement is issued
Normal exit from SQL*Plus, without explicitly issuing
COMMIT or ROLLBACK
An automatic rollback occurs under an abnormal termination of
SQL*Plus or a system failure.
45
State of the Data Before
COMMIT or ROLLBACK
The previous state of the data can be recovered.
The current user can review the results of the DML operations by using
the SELECT statement.
Other users cannot view the results of the DML statements by the current
user.
The affected rows are locked; other users cannot change the data within
the affected rows.
46
State of the Data After COMMIT
Data changes are made permanent in the database.
The previous state of the data is permanently lost.
All users can view the results.
Locks on the affected rows are released; those rows are available for
other users to manipulate.
All savepoints are erased.
47
Committing Data
Make the changes.
SQL> UPDATE emp
2 SET
deptno = 10
3 WHERE
empno = 7782;
1 row updated.
• Commit the changes.
SQL> COMMIT;
Commit complete.
48
State of the Data After ROLLBACK
Discard all pending changes by using the ROLLBACK statement.
Data changes are undone.
Previous state of the data is restored.
Locks on the affected rows are released.
SQL> DELETE FROM
14 rows deleted.
SQL> ROLLBACK;
Rollback complete.
49
employee;
Rolling Back Changes
to a Marker
Create a marker in a current transaction by using the
SAVEPOINT statement.
Roll back to that marker by using the ROLLBACK TO
SAVEPOINT statement.
SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.
50
Statement-Level Rollback
If a single DML statement fails during execution, only that
statement is rolled back.
The Oracle Server implements an implicit savepoint.
All other changes are retained.
The user should terminate transactions explicitly by executing a
COMMIT or ROLLBACK statement.
51
Read Consistency
Read consistency guarantees a consistent view of the data at all
times.
Changes made by one user do not conflict with changes made by
another user.
Read consistency ensures that on the same data:
Readers do not wait for writers
Writers do not wait for readers
52
Locking
Oracle locks:
Prevent destructive interaction between concurrent transactions
Require no user action
Automatically use the lowest level of restrictiveness
Are held for the duration of the transaction
Have two basic modes:
Exclusive
Share
53
Isolamento,consistenza e concorrenza
Isolation Level
Dirty Read
Nonrepeatable
Read
Phantom Read
Read
uncommitted
Read committed
Possible
Possible
Possible
Not possible
Possible
Possible
Repeatable read
Not possible
Not possible
Possible
Serializable
Not possible
Not possible
Not possible
54
Read committed : statement consistency on Oracle
Transaction 1
SQL> select balance from accounts where
account = 1;
BALANCE ---------500
SQL> select balance from accounts where
account = 2;
BALANCE ---------200
SQL> select balance from accounts where
account = 30;
BALANCE ---------100
SQL> select balance from accounts where
account = 30;
BALANCE ---------500
SQL>
55
Transaction 2
SQL> update accounts set balance = 100
where account = 1;
1 row updated.
SQL> update accounts set balance = 500
where account = 30;
1 row updated.
SQL>
SQL> Commit;
Commit complete.
Read committed : statement consistency and locks on SQL
Transaction 1
begin transaction
select * from accounts where account = 1;
select * from accounts where account = 2;
account
balance
------------------ ---------------1
500
(1 row(s) affected)
account
balance
------------------ ---------------2
200
(1 row(s) affected)
Transaction 2
begin transaction
update accounts set balance = 100 where
account = 1;
update accounts set balance = 500 where
account = 30;
(1 row(s) affected)
(1 row(s) affected)
select * from accounts where account = 30;
account
balance
------------------ ---------------30
500
(1 row(s) affected)
56
Commit transaction
Commit complete.
Read committed : exclusive locks on Oracle
Transaction 1
SQL> update accounts set balance = 500
where account = 30;
1 row updated.
SQL> select balance from accounts where
account = 2;
BALANCE ---------200
SQL> Commit;
Commit complete.
SQL> select balance from accounts where
account = 30;
BALANCE ---------500
SQL>
57
Transaction 2
SQL> update accounts set balance = 100
where account = 1;
1 row updated.
SQL> update accounts set balance = 700
where account = 30;
1 row updated.
SQL>
Read committed : exclusive locks on Oracle
Transaction 1
SQL> update accounts set balance = 500
where account = 30;
1 row updated.
SQL> select balance from accounts where
account = 2;
BALANCE ---------200
SQL> Commit;
Commit complete.
SQL> select balance from accounts where
account = 30;
BALANCE ---------500
SQL>
58
Transaction 2
SQL> update accounts set balance = 100
where account = 1;
1 row updated.
SQL> update accounts set balance = 700
where account = 30;
1 row updated.
SQL>
Deadlocks
59
Come fa Oracle ?
60
Gestione sessioni serializable
61
62
Architettura Oracle
63
Oracle Instance
Server Process
1 -SGA
Oracle
Server
Architecture
Overview
(System
Global
Area)
PGA
Database
Shared
Buffer
Pool
Cache
Redo
Log
Buffer
Java Pool
(optional)
User Process
Large Pool
(optional)
DBWR LGWR SMON PMON CKPT RECO
ARCH
Oracle
Database
LCKn
Pnnn
Dnnn
SNPn
Redo Log Files
Data files
Control Files
64
Other
Files...
Parameter,
Password,
Archive
Logs
Client Oracle
65
Connessione applicazioni
66
Tablespace
67
Oggetti Oracle
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
68
Tablespace
Tabelle (tipi di tabelle)
Constraints ( vincoli )
Sequences
Triggers
Viste
Viste Materializzate
Indici (vari tipi di indice)
Funzioni
Procedure
Packages
DB link
Directories
Tipi
Autorizzazioni : Grants, ruoli e accenni di VPD
Sinonimi
…
Tipi di base Oracle : la regola d’oro
27
15,3
32E12
15/3/2013
69
Testo
25,883
31/12/2003 19:30
27/07/1972
Paolo
Alfa34
Il Data Dictionary
70
Tabelle
•
•
•
•
•
CREATE TABLE
DDL
ALTER TABLE
DML
INDICI
71
DML
•
•
•
•
•
SELECT …
INSERT …
UPDATE …
DELETE …
MERGE …
INSERT INTO HR.TESTTABLE
(AAA,BBB) VALUES (100,
'PROVA');
UPDATE HR.TESTTABLE
SET BBB = 'PROVA‘
WHERE AAA = 100;
DELETE HR.TESTTABLE
WHERE AAA = 100;
72
Bulk operations
•
•
•
•
Merge ( DML )
Insert into select ( DML )
Create table as ( DDL )
Truncate ( DDL )
MERGE INTO dest_tab a
USING src_tab b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
INSERT INTO dest_tab a
(object_id,status )
SELECT (object_id,status )
FROM src_tab b
WHERE object_id > 500;
73
High Water Mark
74
Create table
CREATE TABLE RICHIESTA
(
ID_RICHIESTA
NUMBER(7),
DATA_RICHIESTA DATE,
RICHIEDENTE
VARCHAR2(300 BYTE),
NOMINATIVO
VARCHAR2(300 BYTE),
PERIODO_DA
DATE,
PERIODO_A
DATE,
SOC_APPARTENENZA VARCHAR2(300 BYTE),
LUOGO
VARCHAR2(300 BYTE),
MOTIVO
VARCHAR2(300 BYTE),
NR_RDA
VARCHAR2(50 BYTE)
)
TABLESPACE ARCHIMEDE
75
Indici
76
Come è fatto un indice ?
77
Creazione indici
80
70
60
Bad Indexing
Stale stats
Bad SQL
Need partitioning/PX
Others
50
40
30
20
10
0
Cause problemi lentezza query
CREATE INDEX emp_ename
ON emp(ename,esurname)
TABLESPACE users;
78
Bitmap Indexes
79
Viste
CREATE VIEW staff AS
SELECT employee_id,last_name,job_id,manager_id,department_id
FROM employees
WHERE status=1;
80
Inline views
SELECT *
WITH
FROM ( SELECT … ) a
sum_sales AS
INNER JOIN ( SELECT … ) b
( select /*+ materialize */
ON (a.object_id = b.object_id)
sum(quantity) all_sales from stores ),
WHERE b.fa IN ( SELECT fa FROM …)
number_stores AS
AND …
( select /*+ materialize */
count(*) nbr_stores from stores ),
sales_by_store AS
( select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales )
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores);
81
Viste Materializzate
82
Constraints
TIPI DI VINCOLI
• Primary Key
• Unique
• Forign Key
• Not Null
• Check
83
Contraints ( how to create them )
ALTER TABLE DEPARTMENTS ADD (
CONSTRAINT DEPT_ID_PK
PRIMARY KEY
(DEPARTMENT_ID));
ALTER TABLE EMPLOYEES ADD (
CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS
(DEPARTMENT_ID));
ALTER TABLE EMPLOYEES ADD (
HIRE_DATE CONSTRAINT
EMP_HIRE_DATE_NN NOT NULL);
84
ALTER TABLE EMPLOYEES ADD (
CONSTRAINT EMP_EMP_ID_PK
PRIMARY KEY
(EMPLOYEE_ID));
ALTER TABLE EMPLOYEES ADD (
CONSTRAINT EMP_EMAIL_UK
UNIQUE (EMAIL));
ALTER TABLE EMPLOYEES ADD (
CONSTRAINT EMP_SALARY_MIN
CHECK (salary > 0));
Distributed DB
85
Programmability
•
•
•
•
PL/SQL , T-SQL
Triggers
Functions
Procedures
86
Bakup
87
Posso fermare il DB ?
88
Hot backup policy
89