Oracle Developer Essentials: Tables and Indexes

This course covers the essentials of creating and managing tables in your Oracle database, including naming tables, defining columns, creating constraints and granting permissions. It also covers the basics of indexes, which are essential for efficiently accessing data in your tables.
Course info
Rating
(117)
Level
Intermediate
Updated
Jan 28, 2015
Duration
5h 10m
Table of contents
Introduction
Table Basics
Table Constraints
Table Storage Options
Managing Tables
Specialized Table Types
Indexes
Privileges and Roles
Good Practices
Description
Course info
Rating
(117)
Level
Intermediate
Updated
Jan 28, 2015
Duration
5h 10m
Description

This course introduces the most fundamental of data structures in an Oracle database: the table. Learn how to define tables, declare columns, define constraints on tables, important storage options you need to know about, and global temporary tables and external tables. You'll also learn about privileges and roles in Oracle, which are essential for granting appropriate permissions in your database. This course will also teach you best practices for building tables in your database, and how to create and use indexes for efficient data access.

About the author
About the author

David Berry is a software engineer with over 15 years of experience developing applications in languages such as Java and C#. Throughout his career, he has worked extensively with enterprise database systems including Oracle and SQL Server.

More from the author
More courses by David Berry
Section Introduction Transcripts
Section Introduction Transcripts

Table Constraints
Hello. My name is David Berry. Welcome to this module on Table Constraints. Constraints allow you to define rules that the data in your table needs to comply with. By defining appropriate constraints, you can help enforce business rules around the data in your database and ensure that the data you are collecting is clean and meets some minimum quality standards. For example, we might need to make sure that no two students have the same ID number at our university, as this would be confusing. We also need to make sure that every course we offer is associated with a department. We can't just have courses out in our database on their own. Constraints help us do both of these. We'll look at three types of constraints in this module. First up, for primary keys, which help you uniquely identify a row in a table. Then we'll talk about foreign keys, which are used to enforce data integrity between tables in a parent-child relationship. And finally, we'll talk about check constraints, which can be used to place some validation around the values in the columns on your table.

Managing Tables
Hello. My name is David Berry. Welcome to this module on Managing Tables in Oracle. At some point, you're going to have to do something like add a column to a table, rename a column, or change a data type. That is what this module is all about, managing the tables in your Oracle database once you have them created. We'll start off with a discussion of the Oracle Data Dictionary where you could find information about the tables and indeed all of the objects in your database. We'll then introduce the concept of database statistics and then talk a little bit about what database statistics are, what they are used for, and how they're collected. Then we'll move into a discussion about some commands that allow you to manage tables, including how to drop a table and rename a table. And, finally, we'll discuss how to manage the columns in your table, including adding a column, dropping a column, renaming a column, and making changes to its data type.

Specialized Table Types
Hello, my name is David Berry. Welcome to this module on Specialized Table Types in Oracle. So far the tables we have discussed are really for one purpose, persisting data in Oracle. This, of course, is the primary reason why you use a database like Oracle. However, there are a couple of specialized table types that aren't used for persisting data in Oracle, but help us solve some common problems we face when we're working with Oracle. The first type of these tables is a global Temporary Table. These are tables that you define in your database, but they only retain data for the duration of a transaction or a session, so they serve as a temporary holding area that we can use during a lengthy process. The second type of table that we'll talk about in this module in an External Table. Often times you're going to have the need to import some data into your Oracle database from a flat file. There are several ways to do this in Oracle, but what external tables provide is an interface that allows you to query that data in the flat file just like it was another table in your database. So let's dive right in and start talking about these specialized table types that are available in Oracle.

Indexes
Hello, my name is David Berry. Welcome to this module on Indexes in Oracle. So far in this course, we have spent a lot of time talking about tables, but the purpose of using a database system like Oracle is not just to store data. What we really want is to be able to quickly locate and retrieve a subset of our data and bring it back into our application so we can work with it. And this is where indexes come in. We'll start off with a general discussion about what an index is, and how the most common type of index, a B-tree index, works. We'll then discuss how to create an index and some of the options that you may need to use. Then, we'll discuss unique indexes, which not only can speed up access to your data, but also serve as a constraint on the data in your table. We'll discuss function based indexes, which allow you to create an index over a derived value rather than just the raw data values present in your table. We'll then proceed into a discussion of bitmap indexes. And finally, we'll wrap up by talking about some tips for creating indexes in your database. So as you can tell, we have a lot to talk about, so let's get started.

Good Practices
Hello, my name is David Berry. Welcome to this module on Good Practices in Oracle. In this module, we're going to talk about some of the good practices that you should adopt as you create your databases in Oracle. We'll start out by talking about naming conventions, how they make your database easier to use and understand, and what are some conventions that you might want to adopt in your work. Next, we'll talk about how to create comments on our tables and columns in Oracle. As much as we strive to have databases which are self documenting, there are still some times where we need to explain things a little bit more in depth with a comment, and Oracle gives you a way to do this right in the database. Finally, we'll talk about the single responsibility principle and how it applies to databases. If you are a software developer, you may have already heard about the single responsibility principle and how it applies to the code that you write. I believe this principle applies not just to software we write, but indeed throughout technology, so you will want to keep this in mind as you develop your database.