Oracle Architecture Overview Oracle terms Schema logical collection

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 My. SQL • Access – One. mdb file contains all objects – Limited roles/permissions • My. SQL – 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 varchar 2(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_C 984620) 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 hh 24: mi: ss’) TO_CHAR(ADATE, ‘DD-MON-YY: HH 24: 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), col 2 VARCHAR 2(2), col 3 VARCHAR 2(3), CONSTRAINT test_pk PRIMARY KEY(id), CONSTRAINT col 3_ck CHECK (col 3 IN ('yes', 'no')) ); • Name your constraints • User_constraints, user_cons_columns

SELECT user_constraints. constraint_name, constraint_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 -------- - ------------COL 3_CK C col 3 IN ('yes', 'no') TEST_PK P

Constraints • Oracle naming of constraints is NOT intuitive! • enabling and disabling disable 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 Customer. ID increment by 1 start with 1000; – selecting from the sequence insert into customer (name, contact, ID) values (‘TManage’, ’Kristin Chaffin’, Customer. ID. Next. Val); • Curr. Val is used after Next. Val 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 ---VARCHAR 2(30) VARCHAR 2(16) VARCHAR 2(75) VARCHAR 2(30) VARCHAR 2(16) VARCHAR 2(30) VARCHAR 2(4000) VARCHAR 2(128) VARCHAR 2(4000) VARCHAR 2(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 a 20 col triggering_event format a 18 col table_name format a 10 col description format a 26 col trigger_body format a 35 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 a 15 col text format a 40 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 Jones Adams Brown Culver Deal Howard Isis Branch blocks Smith 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 BLUE 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