Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson

DML vs. DDL

DML changes data in an object.  If you insert a row into a table, that is DML.  You have manipulated the data.  When you create, change or remove a database object, it is referred to as data definition language (DDL).  As we will discuss at the end of this chapter, all DDL statements issue an implicit commit, so they are a permanent change.  All DML statements change data and must be committed before the change becomes permanent.

Managing Tables

The table is the basic building block of any database system.  We discussed tables in Chapter 1 and talked about normalizing data to remove redundancy.  In this section, we are going to discuss the different types of tables inside an Oracle database and how they are created and used.  We need this information as we progress into manipulating the data in tables with the INSERT, UPDATE and DELETE statements.  In computer parlance, updates are DML.

You create a table by defining the column names and their data types.   Columns can be any of the data types discussed in Chapter 2, to include user defined data types.  When we loaded the PUBS schema, we ran the pubs_db.sql script that contained the commands to create the tables.  Let’s look at the AUTHOR table.

CREATE TABLE AUTHOR
(
  AUTHOR_KEY                VARCHAR2(11),
  AUTHOR_LAST_NAME          VARCHAR2(40),
  AUTHOR_FIRST_NAME         VARCHAR2(20),
  AUTHOR_PHONE              VARCHAR2(12),
  AUTHOR_STREET             VARCHAR2(40), 
  AUTHOR_CITY               VARCHAR2(20), 
  AUTHOR_STATE              VARCHAR2(2), 
  AUTHOR_ZIP                VARCHAR2(5),  
  AUTHOR_CONTRACT_NBR       NUMBER(5)    
);

This statement creates a table named AUTHOR that contains nine columns defined within the parentheses.  Each column definition is separated by a coma and contains the data type and size of the column.  The CREATE TABLE command can be quite involved, defining the table storage location and constraints.

CREATE TABLE "PUBS"."EDITOR"
(
  "EDITOR_KEY" VARCHAR2(9) NOT NULL,
  "EDITOR_LAST_NAME" VARCHAR2(30) NOT NULL,
  "EDITOR_FIRST_NAME" VARCHAR2(30) NOT NULL,
  "HIRE_DATE" DATE DEFAULT SYSDATE NOT NULL, 
  "EDITOR_ACTIVE" CHAR(1) DEFAULT 'Y',
   CONSTRAINT "EDITOR_PK" PRIMARY KEY("EDITOR_KEY")
      USING INDEX 
      TABLESPACE "INDX"
)  TABLESPACE “USERS” 
 
In the example above, I created a table called EDITOR in the PUBS schema or user.  It has five columns, all of which will not allow NULL values except for editor_active.  The hire_date column will default to the SYSDATE, if a date is not provided when a row is inserted.  Likewise, the editor_active column will default to Y.  I defined a primary key constraint on the editor_key called editor_pk.  The editor_pk constraint uses an index, which will be built in the INDX tablespace.  The table itself will be built in the USERS tablespace. 
 

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.