Audience
This course is for programmers, developers and support staff who wish to gain a comprehensive understanding of how to perform the more advanced tasks whilst developing and maintaining an Oracle Database.
Prerequisites
Delegates should have a good basic understanding of Oracle SQL. Previous attendance of our course, Oracle SQL Part 1, is highly recommended.
Duration
2 days. Hands on.
Course Objectives
This course is highly interactive and consists of theory coupled with plenty of hands on practical sessions. It will introduce delegates to the creation, security and maintenance of objects in an Oracle database. In addition the course will provide an understanding of SQL performance issues.
On completion of the course delegates will be able to:
- Utilise the SQL language to create and populate new objects.
- Establish security for those objects.
- Understand the performance of the SQL itself in respect of data retrieval.
Course Content
Database Definition
Creating and maintaining table structures
Renaming tables
Temporary tables
Use of indices
Creating and maintaining B-tree indices
Creating and maintaining Bitmap indices
Index only tables
Partitioned tables
Clusters
Views
Synonyms
Sequences
Dropping objects
Oracle Data Dictionary
Data dictionary view categories
Data dictionary view relationships
Data dictionary usage
SQL Performance Issues & Tracing
Use and misuse of Indexes
Use of explain plan - optimiser decision process
SQL trace (where applicable)
Performance monitoring tools - Enterprise Manager
Use of table and index statistics to optimise performance
Optimiser Hints
Goal hints
Access method hints
Join order hints
Join operation hints
Parallel execution hints
Miscellaneous hints
Data Warehouse
Basic principles
Materialised views
Dimensions
Query rewrite mechanism
Analytic Functions
SQL Modelling
Oracle Security Facilities
Access to system privileges
Access to database objects
NB This course is suitable for all recent versions of Oracle, up to and including 11G.