ORACLE ARCHITECTURE Overview
Presented By Kelly Technologies www.kellytechno.com
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
www.kellytechno.com
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. www.kellytechno.com
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
www.kellytechno.com
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
www.kellytechno.com
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… www.kellytechno.com
ORACLE ODDS AND ENDS
Dual table SELECT 1+1*400 FROM DUAL;
% - the SQL wildcard
inserting apostrophes SELECT ename FROM emp WHERE ename like ‘%neil%’;
Case sensitive string matching INSERT INTO emp (name) VALUES (‘O’’Neill); UPDATE emp SET ename=UPPER(ename) WHERE ename='O''Neill'; www.kellytechno.com
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
www.kellytechno.com
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 www.kellytechno.com
OUTER JOINS IN ORACLE ď&#x201A;˘
Add (+) to table where nulls are acceptable
SELECT * FROM emp, dept WHERE emp.deptno(+)=dept.id;
www.kellytechno.com
ORACLE SQL FUNCTIONS Upper(),
lower() Substr(), replace(), rtrim(), concat() Length() Floor(),
sqrt(), min(), max(), stddev()
Add_months(),
last_day()
To_date(),
months_between(),
to_char(), to_lob() www.kellytechno.com
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’)
www.kellytechno.com
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
www.kellytechno.com
CONSTRAINTS Primary key Foreign key Unique, not null Check Name your constraints User constraints, user_cons_columns
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')) ); www.kellytechno.com
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=â&#x20AC;&#x2DC;TESTâ&#x20AC;&#x2122;;
NAME
T SEARCH_CONDITION
--------------- - ------------------------COL3_CK
C col3 IN ('yes','no')
TEST_PK
P www.kellytechno.com
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;
www.kellytechno.com
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
www.kellytechno.com
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
www.kellytechno.com
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
www.kellytechno.com
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';
www.kellytechno.com
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;
www.kellytechno.com
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 www.kellytechno.com
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) www.kellytechno.com
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
www.kellytechno.com
THANK YOU www.kellytechno.com