17-12-2012, 06:47 PM
Oracle Architecture
1Oracle Architecture.ppt (Size: 94 KB / Downloads: 268)
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
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
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
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)