Oracle - Tables and Indexes

Temporary Table Properties

- Defined statically - Lifespan of the session its associated with - Table definition available to all sessions - Data is private to a session - Act as a private workspace - Can be indexed; cannot be used as keys - Table properties can be shared; table data cannot

External Table Properties

- Import Only - Can import both fixed width and delimited files - Files must be located on the Oracle server - Will need an OS directory created first - Users will need read/write permissions - Will need to create a directory object - Users will need read access to directory object

Bitmap Index Properties

- Intended to solve a very different problem than a B-tree index - Targeted at columns with low number of distinct values - Only suitable for data warehouse or read only applications - Only available in Oracle Enterprise Edition

ON COMMIT DELETE/ON COMMIT PRESERVE

- Used for temporary tables ON COMMIT DELETE ROWS //will delete rows after data is committed (default) ON COMMIT PRESERVE ROWS //will retain data after commit until session ends

Virtual Column Properties

- Value is computed in result set of query - Cannot INSERT into or UPDATE virtual columns - Can only make use of columns in the same table - Indexes can be created over virtual column values

External Table Syntax (CSV files)

//CREATE TABLE Syntax ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY directory_name ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE LOGFILE directory_name: 'string with file name' //the log file for the data import BADFILE directory_name: 'string with file name' //the file for rejected data //* can be generated by default by Oracle FIELDS (column_name //no data type necessary format DATE ) ) LOCATION ('string with file name') );

External Table Syntax (Fixed Width)

//CREATE TABLE Syntax ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY directory_name ACCESS PARAMETERS //fixed or delimited ( RECORDS //records type LOGFILE directory_name: 'string with file name' //the log file for the data import BADFILE directory_name: 'string with file name' //the file for rejected data //* can be generated by default by Oracle SKIP 1 //for header rows FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " ' //to delineate seperations within strings MISSING FIELDS ARE NULL REJECT ROWS WITH ALL NULL FIELDS (column_name data_type ) ) LOCATION ('string with file name') ) REJECT LIMIT UNLIMITED //process what you can regardless of bad import (limit would create error)

Index Names Good Practices

<prefix)_<table_name>_<column_name(s)> IX- Non Unique UX- Unique FX- Function-based

Database Role

A container to group a set of privileges

Index Key

A subset of columns used to organize and search the index

Table Naming Rules Syntax

ALLOWABLE CHARACTERS - Alphanumeric characters - Underscore (_) character - $ and # characters TYPICAL NAMING SYNTAX table_name Names will be case insensitive String values can be used, but make for complicated SQL commands

Table Privileges

ALTER Change the table definition using ALTER TABLE DEBUG Debug the PL/SQL in a trigger on the table DELETE Remove rows on the table via a DELETE statement INDEX Create an index on the table INSERT Add new rows to the table via an INSERT statement READ Allows queries from the table but not a SELECT ... FOR UPDATE statement (pessimistic locking) REFERENCES Allows a foreign key constraint to the table (can only be granted to a user, not a role) SELECT Allows reads from the table including SELECT ... FOR UPDATE statements UPDATE Modify rows in the table via an UPDATE statement

DROP COLUMN (Physical) Syntax

ALTER TABLE table_name DROP COLUMN (column_name1, column_name2);

DROP COLUMN (Logical) Syntax

ALTER TABLE table_name SET UNUSED (column_name1, column_name2); (later if you like) ALTER TABLE table_name DROP UNUSED COLUMNS *this is done to maintain performance during peak hours **if the column is a key, CASCADE CONSTRAINTS also applies

Adding Columns Syntax

ALTER TABLE table_name ADD ( column_name data_type );

Data Type Change Syntax

ALTER TABLE table_name MODIFY ( column_name new_data_type );

READ ONLY/READ WRITE Syntax

ALTER TABLE table_name READ ONLY ALTER TABLE table_name READ WRITE

Virtual Columns

Value is computed from other columns (not stored)

User_Constraints View

Contains an entry for each constraint (primary key, foreign key, check constraint) KEY COLUMNS table_name: Name of the table the constraint is associated with constraint_name: Name of the constraint constraint_type: Code that indicates the type of the constraint r_constraint_name: For foreign keys, the name of the unique constraint in the parent table deferrable: Indicates if the constraint can be deferred

User_Indexes View

Contains an entry for each index KEY COLUMNS table_name: Name of the table the index is on index_name: Name of index uniqueness: Indicated if this is a unique index distinct_keys: Number of distinct keys in the index last_analyzed: Date that stats were most recently gathered against the index

User_Tab_Cols View

Contains an entry for every column in a table or a view KEY COLUMNS table_name: Name of the table or view the column belongs to column_name: Name of the column data_type: Data type of this column num_distinct: Number of distinct values in this column num_nulls: Number of rows in this column with a null value

User_Objects View

Contains an entry for every database object (table, view, index, stored procedure, etc...) KEY COLUMNS object_name: Name of the table, view, index, etc... object_type:Indicated if this object is a table, view, etc... created: Timestamp of when this object was created last_ddl_time: Timestamp of when the definition of this object was last changed status: Indicated if the object is valid or invalid

User_Tables View

Contains one row for each table KEY COLUMNS table_name: Name of the table num_rows : Number of rows in the table blocks: Number of blocks that have been used in the table avg_row_len: Average length in bytes of a row in the table last_analyzed: Date that stats were most recently gathered against the table

Function Based Index

Created over a derived value index returned by a function (vs. actual column values)

View Privileges

DEBUG Debug the PL/SQL in a trigger on the table DELETE Remove rows on the table via a DELETE statement INSERT Add new rows to the table via an INSERT statement READ Allows queries from the table but not a SELECT ... FOR UPDATE statement (pessimistic locking) REFERENCES Allows a foreign key constraint to the table (can only be granted to a user, not a role) SELECT Allows reads from the table including SELECT ... FOR UPDATE statements UPDATE Modify rows in the table via an UPDATE statement

Index

a separate data structure designed to speed up the retrieval of rows from a table

Data Dictionary Views

dba_ Info about all objects in the database user_ Info about objects owned by the current user all_ Info about all objects the current user has perm to see

NULL Column Values

empty value CHAR, VARCHAR, NCHAR, NVARCHAR Empty string is treated at 'null'

Defining Virtual Columns

name data_type AS (column computation) VIRTUAL name data_type AS (function) VIRTUAL

Column Syntax

name data_type null/default

DROP TABLE with constraints

DROP TABLE table_name CASCADE CONSTRAINTS

DROP TABLE Syntax

DROP TABLE table_name; *You must drop dependent tables first prior to dropping a parent table

Directory Read Permissions Syntax

GRANT READ ON DIRECTORY directory_name TO user;

GRANT command syntax

GRANT privileges ON object TO user;

Data Type Change Rules

Increase in size of data type - Allowed Decrease in size of VARCHAR - Allowed if no data truncated Decrease in size of NUMBER - Allowed if empty Change to different data type - Allowed if empty

Column Order of an Index (tips)

Leading edge- first column of the index Use as many columns as possible from front of index Put columns most frequently in WHERE clause at the front of the index Index should include as many distinct columns as possible to be as efficient as possible Indexes should be frequently used by SQL statements in order to be valuable

Table Naming Rules

MAXIMUM LENGTH 30 characters for all objects UNIQUENESS Tables, views must be unique within schema Columns must be unique within a table SQL RESERVED WORDS Possible to use but discouraged

Unique v. Non-unique Indexes

Non-Unique index can return multiple rows i.e. Last Name, First Name Unique index returns only one row i.e. email address

Life of a SQL Statement

PARSE PHASE Check SQL Syntax Check Object Permissions Query Rewrite Process QUERY OPTIMIZATION Evaluate Statistics Create Execution Plan EXECUTION PHASE Read Blocks Filter Rows Sort Data

RENAME TABLE Syntax

RENAME table_name TO new_table_name * Auto updates for Constraints and Indexes * Manual updates for stored procedures, synonyms and functions

REVOKE command syntax

REVOKE privileges ON object FROM user;

Database role syntax

CREATE ROLE role_name; GRANT privilege ON table_name TO role_name; GRANT role_name TO user;

Creating a Unique Index syntax

CREATE UNIQUE INDEX index_name ON table_name (column_1, column_2) TABLESPACE index.tablespace.name;

CREATE VIEW syntax

CREATE VIEW view_name AS SELECT (colmun_names...//this is how to control what users can see/manipulate) FROM table_name GRANT select ON view_name TO user

User_Ind_Columns View

Contains an entry for each column in an index KEY COLUMNS table_name: Name of the table the index is on index_name: Name of index column_name: Name of the column column_position: Position of the column in the index

Column Level Privileges

Available on INSERT, UPDATE and REFERENCES privileges only. (ex. GRANT update (column_names...) ON table_name;

Column Definitions

COLUMNS PER TABLE Maximum of 1000 columns Over 255 columns results in row chaining COLUMN NAMING RULE 30 characters maximum Names must be unique within a table Names can be reused in different tables MINIMUM INFORMATION Name and datatype must be specified NULL an default clauses are optional

Database Comments

COMMENT ON TABLE table_name IS 'comment'; COMMENT ON COLUMN table_name IS 'comment'; *comments can be deleted with the same command incorporating empty strings*

Bitmap Index Syntax

CREATE BITMAP INDEX name_index ON table_name (column);

CREATE DIRECTORY Syntax

CREATE DIRECTORY directory_name AS 'C:\directory_name\';

GLOBAL TEMPORARY TABLE Syntax

CREATE GLOBAL TEMPORARY TABLE table_name ( column_name data_type CONSTRAINT //if any );

Creating a Non-unique Index syntax

CREATE INDEX index_name ON table_name (column_1, column_2) TABLESPACE index.tablespace.name;

Creating a Function-based Index syntax

CREATE INDEX index_name ON table_name (function applied to columns //case)