AI-Chapter 3 Database Storage and Schema Objects

UNIQUE: NULL values do not count as a distinct value, so this employees table can have multiple rows with a NULL . -167

 

PRIMARY KEY: A PRIMARY KEY constraint implicitly includes both NOT NULL
constraints on each column . -168

 

FOREIGN KEY:When you declare a FOREIGN KEY constraint, you identify the columns in one table whose values must also appear in the primary key or unique key of another table. -169

 

As with UNIQUE and PRIMARY KEY constraints, FOREIGN KEY constraints cannot be
created on columns of type CLOB, NCLOB, BLOB, LONG, LONG RAW, or TIMESTAMP WITH
TIMEZONE. -169

CREATE TABLE departments
(dept_nbr NUMBER NOT NULL
CONSTRAINT department_pk PRIMARY KEY
,dept_name VARCHAR2(32)
,manager_id NUMBER
);
CREATE TABLE employees
(employee_id NUMBER NOT NULL
,hire_date DATE NOT NULL
,first_name VARCHAR2(42)
,last_name VARCHAR2(42)
,payroll_id VARCHAR2(10)
,dept_nbr NUMBER
,CONSTRAINT uniq_payroll_id UNIQUE (payroll_id)
USING INDEX TABLESPACE indx
,CONSTRAINT employees_pk PRIMARY KEY (employee_id)
USING INDEX TABLESPACE indx
,CONSTRAINT emp_dept_fk FOREIGN KEY (dept_nbr)
REFERENCES departments(dept_nbr)
);

 

 

 

 

By default, FOREIGN KEYs allow NULLs.  -169

 

To delete rows from a parent table if those rows are referenced in the child table.

By deleting the child rows and specifying ON DELETE CASCADE -170

ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_nbr)
REFERENCES departments(dept_nbr) ON DELETE CASCADE;

   

 

By setting the columns in the child table to NULL with the ON DELETE SET NULL clause -170

ALTER TABLE departments ADD CONSTRAINT
dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES
employees(employee_id) ON DELETE SET NULL;

 

 

 

When you create a constraint, you can tell the database to allow either immediate or
deferred constraint checking by specifying the keyword DEFERRABLE. If you normally want
a DEFERRABLE constraint to be deferred, create it with the INITIALLY DEFERRED option.
Only DEFERRABLE constraints can be set to INITIALLY DEFERRED. Once you create a
constraint, you cannot change its deferability (for example, from NOT DEFERRABLE to
DEFERABLE); instead, you must drop and re-create the constraint with the new specification. -171

 

ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_nbr)
REFERENCES departments(dept_nbr) ON DELETE CASCADE
DEFERRABLE;

 

 

 

ALTER TABLE departments ADD CONSTRAINT
dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES
employees(employee_id) ON DELETE SET NULL
DEFERRABLE INITIALLY DEFERRED;

 

 

 

CHECK:CHECK constraints verify that a column or set of column values meet a specific condition that must evaluate to a Boolean. -171

ALTER TABLE employees ADD CONSTRAINT
validate_hire_date CHECK
(hire_date > TO_DATE('15-Apr-1999','DD-Mon-YYYY'));

 

 

 

Take care in disabling UNIQUE or PRIMARY KEY constraints because disabling these
constraints results in the supporting index being dropped (unless you also specify KEEP INDEX. -171

 

If FOREIGN KEY constraints reference your PRIMARY KEY or UNIQUE constraint, you need to drop these dependent constraints before or in conjunction (using the CASCADE keyword) with the PRIMARY KEY constraint:   -172

ALTER TABLE employees DROP PRIMARY KEY CASCADE;

 

 

 

To rebuild an index, which will shrink its size and possibly reduce the Btree depth (making
it more efficient), use a REBUILD clause, like this . －173
ALTER INDEX emp_seniority REBUILD;

 

 

COALESCE INDEX vs REBUILD INDEX

 

REF: http://space.itpub.net/10714335/viewspace-441096

 

Q: Does an Index Coalesce basically accomplish what an Index Rebuild Online?

A:

No. An offline index rebuild essentially does the following:

1. Lock the table
2. Create a new, temporary, index by reading against the contents of the existing index
3. Drops the original index
4. Renames the temporary index to make it seem to be the original index
5. Remove the table lock.

An online index rebuild basically does this:

1. Lock the table
2. Create a new, temporary and empty, index and an IOT to store on-going DML
3. Release the table lock
4. Populate the temporary index by reading against the contents of the existing index
5. Merge contents of the IOT in with the new index
6. Lock the table
7. Final merge from IOT and drop the original index
8. Renames the temporary index to make it seem to be the original index
9. Remove the table lock.

And a coalesce does this:

1. Scan along the base of the index
2. Where adjacent nodes can be combined into a single node, do so

That's it. There's no table locking. There's no new index created. There's no reduction in the size of the index, therefore. Just adjacent nodes which are mostly-full of empty space merged into a single, well-filled node, leaving a totally empty node now available for fresh inserts.

Q: Is it faster? Slower? Lower-impact? Logged?

A:

Yes, it's faster because it's not doing so much. Yes, it's lower-impact because there's no table locking (even an online rebuild takes exclusive table locks. Twice.) Yes, it's logged because you're modifying the contents of the index blocks.

 

CREATE OR REPLACE VIEW empv
(employee_name
,department
,manager
,hire_date
) AS SELECT
E.first_name||' '||E.last_name
,D.dept_name
,M.first_name||' '||M.last_name
,E.hire_date
FROM employees E
,departments D
,employees M
WHERE E.dept_nbr = D.dept_nbr
AND D.manager_id = M.employee_id
;