Docstoc

Oracle Architecture 2 - PowerPoint

Document Sample
Oracle Architecture 2 - PowerPoint Powered By Docstoc
					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 TEST_PK C col3 IN ('yes','no') 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
SQL> /

(cont.)

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

Branch blocks

< Davis Davis Jones

Smith Turner Turner >

Adams Brown Culver

Deal Howard Isis

Jules Klein Main

Moss Porter Sikes

Sykes Thomas Topper

Vera Wagner Yanks

Deal – ROWID Howard – ROWID Detail of leaf node Isis - ROWID

Leaf blocks

Bitmap index
Parts table
partno 1 2 3 4 color GREEN RED RED BLUE size MED MED SMALL LARGE

Bitmapped index on ‘color’
color = ‘BLUE’ color = ‘RED’ color = ‘GREEN’ Part number 0 0 0 1 0 1 1 0 1 0 0 0 1 2 3 4


				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:707
posted:8/12/2009
language:English
pages:26