Creating Tables


The table is the most basic and most important object you will create in a database. Essentially, you could do without every other database object in a database except for tables. Without tables, you cannot store anything in a database.

You can create tables with the CREATE TABLE statement or "on the fly" with a method known as Create Table As Select, or CTAS.

Once you know that you need to create a table, you must decide what kind of table you want. In this section, we’ll cover the most common types of tables:

  • Relational tables

  • Tables created directly from the result of a query

  • Tables whose data resides outside the database

  • Tables with a definition that is available to all sessions but whose data is local to the session that created the data

Relational Tables

A relational table is the most common form of a table in the Oracle database. It is created with the CREATE TABLE statement, its data is stored in the database, and it can be partitioned. When you partition a table, the data for the table is internally stored in two or more pieces to potentially improve performance and to make the table easier for the DBA to manage if the table has many rows. Partitioning tables is covered in more detail in Chapter 12, "Making Things Run Fast (Enough)."

relational table

The most common form of a table in the Oracle database; the default type created with the CREATE TABLE statement. A relational table is permanent and can be partitioned.

The basic syntax for the CREATE TABLE statement is as follows:

CREATE TABLE [schema.]tablename    (column1 datatype1 [DEFAULT expression]       [, ...]);

The table that Scott, the company founder, created back in Chapter 2 was built with this statement:

create table emp_hourly (   empno      number(4)    not null,   ename      varchar2(10),   job        varchar2(9),   mgr        number(4),   hiredate   date,   hourrate   number(5,2)  not null default 6.50,   deptno     number(2),   constraint pk_emp primary key ( empno ) ) ; 

Now, the HR schema is used to manage employee information. Therefore, Janice, the DBA and senior developer, must re-create the table to match the datatypes and name of the EMPLOYEES table in the HR schema, as follows:

create table employees_hourly (   employee_id     number(6)    not null,   first_name      varchar2(20),   last_name       varchar2(25) not null,   email           varchar2(25) not null,   phone_number    varchar2(20),   job_id          varchar2(10) not null,   manager_id      number(6),   hire_date       date not null,   hourly_rate     number(5,2) default 6.50 not null,   department_id   number(4),   ssn             varchar2(11),   constraint pk_employees_hourly               primary key( employee_id ) ) ;

Because of the PRIMARY KEY constraint on the EMPLOYEE_ID column, the values in the EMPLOYEE_ID column must be unique within the table.

Create Table As Select (CTAS)

If you want to base the contents of a new table on the results of a query of one or more other tables, you can use the statement CREATE TABLE ... AS SELECT, otherwise known as CTAS. It’s shorthand for two or more individual statements: the traditional CREATE TABLE statement and one or more INSERT statements. Using CTAS, you can create a table and populate it in one easy step.

CTAS

Also known as Create Table As Select, a method for creating a table in the database by using the results from a subquery to both populate the data and specify the datatypes of the columns in the new table.

The syntax for CTAS varies from the basic syntax of a CREATE TABLE statement as follows:

CREATE TABLE [schema.]tablename    AS SELECT <select_clauses>;

Notice that with CTAS you cannot specify the datatypes of the new columns; the column datatypes of the original columns, along with any NOT NULL constraints, are derived from the columns in the SELECT query. Any other constraints or indexes may be added to the table later. Column aliases in the SELECT query are used as the column names in the new table.

At Scott’s widget company, the Order Entry department frequently sends out mailings to nonadministrative staff, but the mailing list is becoming outdated. The manager in the Order Entry department asks Janice to grant the developers in the group the rights to access the EMPLOYEES table. However, the EMPLOYEES table contains sensitive personal information about employees, such as their salary. So, instead of granting access to the EMPLOYEES table, Janice decides to give the Order Entry department developers their own table with a limited number of columns. Using CTAS, her CREATE TABLE statement extracts the name and e-mail address for the Order Entry department as follows:

create table oe.non_admin_employees     as select employee_id, last_name, first_name, email     from hr.employees e where e.job_id not like ‘AD_%’;

Notice that Janice is copying some of the rows with only a few of the columns from the EMPLOYEES table in the HR schema, and she is creating a new table named NON_ADMIN_EMPLOYEES in the OE schema. To confirm her work, Janice checks the new table:

describe oe.non_admin_employees  Name                         Null?    Type  ---------------------------- -------- ---------------  EMPLOYEE_ID                           NUMBER(6)  LAST_NAME                    NOT NULL VARCHAR2(25)  FIRST_NAME                            VARCHAR2(20)  EMAIL                        NOT NULL VARCHAR2(25) select * from oe.non_admin_employees; EMPLOYEE_ID LAST_NAME          FIRST_NAME       EMAIL ----------- ------------------ ---------------- -----------         103 Hunold             Alexander        AHUNOLD         104 Ernst              Janice           JERNST         105 Austin             David            DAUSTIN         106 Pataballa          Valli            VPATABAL         107 Lorentz            Diana            DLORENTZ         108 Greenberg          Nancy            NGREENBE ...         195 Jones              Vance            VJONES         196 Walsh              Alana            AWALSH         197 Feeney             Kevin            KFEENEY         198 OConnell           Donald           DOCONNEL         199 Grant              Douglas          DGRANT         201 Hartstein          Michael          MHARTSTE         202 Fay                Pat              PFAY         203 Mavris             Susan            SMAVRIS         204 Baer               Hermann          HBAER         205 Higgins            Shelley          SHIGGINS         206 Gietz              William          WGIETZ 103 rows selected.

Everyone in the EMPLOYEES table is in the new NON_ADMIN_EMPLOYEES table, except for the four administrative employees whose job ID begins with AD_.

Janice makes sure to re-create the table in the OE schema every time employees are added, deleted, or changed in HR’s EMPLOYEE table. If the Order Entry department wants any other constraints or indexes other than the NOT NULL constraint on columns in the new table, Janice will need to create them manually.

External Tables

Sometimes you want to access data that resides outside the database, but you want to use it as if it were another table within the database. An external table is a read-only table whose definition is stored within the database but whose data stays external to the database itself.

click to expand

external table

A table whose definition is stored in the database but whose data is stored externally to the database.

You may ask, “Why not use one of Oracle’s utilities to load the external data into an internal table, and then use the internal table?” While this is an option, there are many reasons why this may not be the best solution. One reason is that you can use the functionality of Oracle SQL against the external table to more easily load the data into other tables. Also, if the external data source is maintained by another business area in a text format, the internal copy of the data most likely will be out of synch until the next time you import it. If you treat the external data as a table, it will always be up to date every time you access it as an external table.

There are a few drawbacks to using external tables. External tables are read-only; changes cannot be made to the external data source with UPDATE statements. Also, external tables cannot be indexed. Therefore, if you need to access only a small fraction of the rows in the external table, an internal table with an index might be a better solution.

Janice, the DBA, has been assigned the task of making the customer feedback files maintained by the Customer Service group accessible from within the database. Currently, the Customer Service group receives customer feedback, which is entered on a daily basis into a text file on the shared network drive I:\Common\CustomerComments with a filename of feedback.txt.

The first step Janice must perform is to define an Oracle object known as a directory. An Oracle directory is an Oracle object that contains an alias to a directory path on the operating system’s filesystem. Once defined in this manner, the Oracle directory object can be used to refer to the location on the filesystem in subsequent Oracle commands, such as the CREATE TABLE ... ORGANIZATION EXTERNAL command. You need to run the CREATE DIRECTORY command only once for each filesystem pathname you want to access. Janice’s command for creating this directory object is as follows:

create directory comment_dir as         ‘I:\Common\CustomerComments’; Directory created.

directory

A database object that stores a reference to a directory on the host operating system’s filesystem.

The file that contains the data for the external table, feedback.txt, looks like this:

154,Helpful and Friendly. 150,Took the time to help me buy the widgets I really needed. 156,Didn’t really seem too enthusiastic. 152,The Best experience I’ve had with Widgets-R-Us.

The external table will have two columns: The first field is the employee number, and the second field is the text of the comments from the customer. A comma separates the employee number from the comment. Janice uses the following CREATE TABLE statement to create the external table:

create table cust_comments (   employee_id   number,   comments      varchar2(100)) organization external  (default directory comment_dir   access parameters   (records delimited by newline    fields terminated by ‘,’     (employee_id char, comments char))    location(‘feedback.txt’)); Table created. 

The first part of the CREATE TABLE statement looks familiar. It contains two columns: EMPLOYEE_ID and COMMENTS. The ORGANIZATION EXTERNAL clause specifies this table to be an external table. The operating system file is located in the directory defined by the directory object comment_dir. Each line of data corresponds to one row in the table, and each column in the external file is separated by a comma. Both of the fields are character strings in the external file, so we define those fields as CHAR. Finally, we specify the name of the external file itself with the LOCATION clause.

Janice, as well as anyone else who can access tables in the HR schema, can use the CUST_COMMENTS table in a query as easily as using any of the internal tables:

select * from cust_comments; EMPLOYEE_ID COMMENTS ----------- ------------------------------------------         154 Helpful and Friendly.         150 Took the time to help me buy the widgets                I really needed.         156 Didn’t really seem too enthusiastic.         152 The Best experience I’ve had with                Widgets-R-Us. 4 rows selected.

To produce a report that is more readable for the boss, Janice joins the external table with the internal EMPLOYEES table:

select employee_id "EmpID",     last_name || ‘, ‘ || first_name "Name", comments from employees join cust_comments using (employee_id);  EmpID Name                 COMMENTS ------ -------------------- -------------------------    154 Cambrault, Nanette   Helpful and Friendly.    150 Tucker, Peter        Took the time to help me                             buy the widgets I really                             needed.    156 King, Janette        Didn’t really seem too                             enthusiastic.    152 Hall, Peter          The Best experience I’ve                             had with Widgets-R-Us. 4 rows selected.

The CUST_COMMENTS table is indistinguishable in usage from any other table in the database, as long as you don’t try to perform any INSERT, UPDATE, or DELETE statements on the external table.

Temporary Tables

A temporary table is a table whose definition is available to all sessions in the database, but whose rows are available only to the session that added the rows to the table. Once the transaction is committed or the session is terminated, the data created during that session is removed from the temporary table. To create a temporary table, you use the familiar CREATE TABLE syntax with the addition of the GLOBAL TEMPORARY clause. An additional clause, ON COMMIT PRESERVE ROWS, retains the rows added to the table until the end of the session; otherwise, the rows are removed after each COMMIT.

temporary table

A table whose definition is persistent and shared by all database users but whose data is local to the session that created the data. When the transaction or session is completed, the data is truncated from the temporary table.

A temporary table might be useful in an application that uses a table for its session data and is used by hundreds of users; the table needs to be created only once, with the proper permissions so that all application users can access it.

Janice, the DBA, is installing a travel itinerary application that employees use to plan their business trips. The application needs a table that temporarily holds the travel destinations and costs for the employee. Janice realizes a temporary table is perfect for this purpose. Her CREATE TABLE statement looks like this:

create global temporary table travel_dest    (employee_id      number(6),     destination_id   number(4),     airfare          number(7,2),     hotel            number(6,2)) on commit preserve rows; Table created.

Once the travel itinerary application is terminated and the user disconnects from the database, any rows placed in this table by the user are automatically removed.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net