Transcript Oracle Architecture - UNC School of Information and

Oracle Architecture
Overview
Oracle terms
• Schema – logical collection of user’s
objects
• Tablespace – logical space used for
storage
• Datafile – physical file used for storage
• Extent – group of contiguous blocks
• Block – unit of physical storage
Oracle Architecture
• database vs. instance
Database
Instance
Parameter files*
Control files**
Data files
Redo Log files
System Global Area (SGA)
Background Processes
Disk
Memory
* Parameter files include the init<SID>.ora and config<SID>.ora files. These
are used to set options for the database.
** Control files contain information about the db in binary form. They can be
backed up to a text file however.
Oracle vs. Access and MySQL
• Access
– One .mdb file contains all objects
– Limited roles/permissions
• MySQL
– Three files per table
– Permissions based on user, database, and host
• Oracle
– Many files
– Many roles/permissions possible
The Oracle Data Dictionary
• Collection of tables and views that show the
inner workings and structure of the db
• “static” data dictionary views
– owned by SYS
– created by catalog.sql script at db creation
– contain DDL info
• dynamic data dictionary views
– also referred to as V$ views
– based on virtual tables (X$ tables)
– provide info about the instance
More Data Dictionary
Create table samples (
ID number(3) primary key,
Type varchar2(5),
Constraint type_ck check (type in (‘photo’,’swatch’))
…);
1. Samples table created in user’s schema
2. Primary key index created in user’s schema (SYS_C984620)
3. Data dictionary is also updated, with rows being inserted into
tables underlying the following data dictionary views:
User_objects
User_constraints
User_cons_columns
And lots more…
Oracle Odds and Ends
• Dual table
SELECT 1+1*400 FROM DUAL;
• % - the SQL wildcard
SELECT ename FROM emp WHERE ename like ‘%neil%’;
• inserting apostrophes
INSERT INTO emp (name) VALUES (‘O’’Neill);
• Case sensitive string matching
UPDATE emp
SET ename=UPPER(ename) WHERE ename='O''Neill';
Sysdate
• Sysdate returns current system date AND time
• use trunc function to remove time piece
Example:
select to_char (adate, ‘dd-mon-yy hh24:mi:ss’)
TO_CHAR(ADATE, ‘DD-MON-YY:HH24:MI:SS’)
17-feb-00 23:41:50
select adate from samples where trunc(adate)=‘17-feb-00’;
ADATE
17-FEB-00
ROWID
• ROWID is an internal number Oracle uses to
uniquely identify each row
• NOT a primary key! Is the actual location of a
row on a disk. Very efficient for retrieval.
• Format specifies block, row, and file (and object
in 8)
– Oracle 7: BBBBBBB.RRRR.FFFFF
– Oracle 8: OOOOOO.FFF.BBBBBB.RRR
• Called pseudo-column since can be selected
Outer joins in Oracle
• Add (+) to table where nulls are
acceptable
SELECT *
FROM emp, dept
WHERE emp.deptno(+)=dept.id;
Oracle SQL functions
• Upper(), lower()
• Substr(), replace(), rtrim(), concat()
• Length()
• Floor(), sqrt(), min(), max(), stddev()
• Add_months(), months_between(), last_day()
• To_date(), to_char(), to_lob()
More functions
• nvl()
– If NULL, return this instead…
Nvl(lastname,’Anonymous’)
• decode()
– Sort of like an If/Then statement…
Decode(gender,0,’Male’,1,’Female’,’Unknown’)
Oracle error messages
• Divided into groups by first three letters
(e.g. ORA or TNS)
• Number gives more information about
error
• Several messages may be related to
only one problem
• oerr facility
Constraints
•
•
•
•
Primary key
Foreign key
Unique, not null
Check
CREATE TABLE test
(
id NUMBER(2),
col2 VARCHAR2(2),
col3 VARCHAR2(3),
CONSTRAINT test_pk PRIMARY KEY(id),
CONSTRAINT col3_ck CHECK (col3 IN ('yes','no'))
);
• Name your constraints
• User_constraints, user_cons_columns
SELECT
user_constraints.constraint_name name,
constraint_type type,
user_constraints.search_condition
FROM user_constraints, user_cons_columns
WHERE
user_constraints.table_name=user_cons_columns.table_name
AND user_constraints.constraint_name=user_cons_columns.constraint_name
AND user_constraints.owner=user_cons_columns.owner
AND user_constraints.table_name=‘TEST’;
NAME
T SEARCH_CONDITION
--------------- - ------------------------COL3_CK
C col3 IN ('yes','no')
TEST_PK
P
Constraints
• Oracle naming of constraints is NOT intuitive!
• enabling and disabling
disable constraint constraint_name;
• the EXCEPTIONS table
– run utlexcpt.sql to create EXCEPTIONS table then
– alter SQL statement:
SQL_query EXCEPTIONS into EXCEPTIONS;
More objects
• Sequences
– creating the sequence
create sequence CustomerID increment by 1
start with 1000;
– selecting from the sequence
insert into customer (name, contact, ID)
values (‘TManage’,’Kristin
Chaffin’,CustomerID.NextVal);
• CurrVal is used after NextVal for related inserts
• Synonyms
– provide location and owner transparency
– Can be public or private
PL/SQL - Triggers
• Executed on insert, update, delete
• Use to enforce business logic that can’t be
coded through referential integrity or
constraints
• Types of triggers
– row level (use FOR EACH ROW clause)
– statement level (default)
– Before and After triggers
• Referencing old and new values
Trigger example
SQL> desc all_triggers;
Name
Null?
------------------------------- -------OWNER
TRIGGER_NAME
TRIGGER_TYPE
TRIGGERING_EVENT
TABLE_OWNER
BASE_OBJECT_TYPE
TABLE_NAME
COLUMN_NAME
REFERENCING_NAMES
WHEN_CLAUSE
STATUS
DESCRIPTION
ACTION_TYPE
TRIGGER_BODY
Type
---VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(16)
VARCHAR2(75)
VARCHAR2(30)
VARCHAR2(16)
VARCHAR2(30)
VARCHAR2(4000)
VARCHAR2(128)
VARCHAR2(4000)
VARCHAR2(8)
VARCHAR2(4000)
VARCHAR2(11)
LONG
Trigger example
(cont.)
SQL> select trigger_name from all_triggers where owner='SCOTT';
TRIGGER_NAME
-----------------------------AFTER_INS_UPD_ON_EMP
set lines 120
col trigger_name format a20
col triggering_event format a18
col table_name format a10
col description format a26
col trigger_body format a35
select trigger_name, trigger_type, triggering_event,
table_name, status, description, trigger_body
from all_triggers
where trigger_name='AFTER_INS_UPD_ON_EMP';
Trigger example
(cont.)
SQL> /
TRIGGER_NAME
TRIGGER_TYPE
TRIGGERING_EVENT
TABLE_NAME STATUS
DESCRIPTION
-------------------- ---------------- ------------------ ---------- -------- ----------------------TRIGGER_BODY
----------------------------------AFTER_INS_UPD_ON_EMP BEFORE EACH ROW INSERT OR UPDATE
EMP
ENABLED
scott.after_ins_upd_on_emp
before insert or update
on scott.emp
for each row
begin
:new.ename := upper(:new.ename);
end;
The above trigger was created with the following statement:
create or replace trigger scott.after_ins_upd_on_emp
before insert or update on scott.emp
for each row
begin
:new.ename := upper(:new.ename);
end;
Remember those views?
• Query USER_TRIGGERS to get trigger info
• Query USER_SOURCE to get source of
procedure, function, package, or package body
• Query USER_ERRORS to get error information
(or use show errors)
col name format a15
col text format a40
select name, type, text
from user_errors
order by name, type, sequence;
• Query USER_OBJECT to get status info
Understanding Indexes
• Index overhead
– impact on inserts, updates and deletes
– batch inserts can be slowed by indexes - may want to
drop, then recreate
– rebuilding indexes
• Use indexes when query will return less than 5%
of rows in a large table
• Determining what to index
– All primary and foreign keys
– Examine SQL and index heavily hit, selective columns
(columns often found in where clauses)
What not to Index…preferably
• columns that are constantly updated
• columns that contain a lot of null values
• columns that have a poor distribution of
data
– Examples:
• yes/no
• true/false
• male/female
B*-tree index
Miller
< Miller
> Miller
< Davis
Davis
Jones
Adams
Brown
Culver
Deal
Howard
Isis
Branch
blocks
Smith
Turner
Turner >
Jules
Klein
Main
Moss
Porter
Sikes
Deal – ROWID
Howard – ROWID Detail of
leaf node
Isis - ROWID
Sykes
Thomas
Topper
Vera
Wagner
Yanks
Leaf
blocks
Bitmap index
Parts table
partno
1
2
3
4
color
size
GREEN
RED
RED
BLUE
MED
MED
SMALL
LARGE
Bitmapped index on ‘color’
color = ‘BLUE’
0 0 0 1
color = ‘RED’
0 1 1 0
color = ‘GREEN’
1 0 0 0
Part number
1 2 3 4