Oracle Interview Preparation Notes Part 3

Managing Indexes:

B-TREE – leafs store lists of rowids.
High cardinality
Inexpensive key updates
Not good for “OR” queries
Useful in OLTP
Bitmap index – leafs store bitmap pointer
Low cardinality (more compact)
Very expensive key updates
Good for “OR” queries
Better for Data Warehousing

CREATE [BITMAP] INDEX ON tab1 PCTFREE x [NOLOGGING] [NOSORT] [REVERSE]
· Pctfree only applies at index creation – set high if new rows will fall between current range.
· Nologging – No redo generated
· Nosort – Rows are already in order
· Reverse – Reverses key (i.e. inv# 12353 becomes 35321) – use for better tree balance.

Functional Indexes: CREATE INDEX idx1 ON tab1 (Column1 * Column2 / 100)

Rebuild – Need space to hold new index, no sorting is required, old index stays online until new is done.
ALTER INDEX idx1 REBUILD TABLESPACE idx1 PCTFREE x STORAGE ( … ) ONLINE
Coalescing – free up leaf blocks for reuse ALTER INDEX idx1 COALESCE

Index Organized Tables - IOT

· Cannot create any other indexes on table.
· Primary Key definition is required

CREATE TABLE a (col type) CONSTRAINT pk1 PRIMARY KEY
ORGANIZATION INDEX PCTTHRESHOLD 25 OVERFLOW TABLESPACE ofts
Rows using over 25% bock, excess data moved to overflow tablespace.

Indexes should be dropped before bulk loads for faster load times and more efficient indexes.

DBA_INDEXES, DBA_IND_COLUMNS

Data Integrity:

ALTER SESSION SET CONSTRAINTS { IMMEDIATE | DEFERRED | DEFAULT }
Immediate = at end of every DML, Deferred = only at commit time (only works if constraint is created with the DEFERRED option).

Foreign Keys:

· Create index on child table to prevent table locks for updates to parent.
· To truncate the parent table, must disable or drop the foreign key.
· To drop the parent table, you can use CASCASE CONSTRAINTS.
Ordinality: [x,y] x=[0 optional, 1 mandatory] y=[1 one, N many]
e.g. [1,1] = mandatory 1 to 1 [0,N] = optional 1 to many

Column Constraint:

ALTER TABLE ENABLE NOVALIDATE CONSTRAINT pk1 – Does not check existing data.
ALTER TABLE ENABLE VALIDATE CONSTRAINT pk1 EXCEPTIONS INTO exceptions.
Check existing data, insert exceptions into exceptions table (created by UTLEXCPT.SQL).

DBA_CONSTRAINTS, DBA_CONS_COLUMNS

Loading Data:

Direct Load Insert - bypasses buffer cache

INSERT /*+APPEND*/ INTO tab1 NOLOGGING SELECT … FROM …

To Parallelize inserts

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLEL(tab1, 2)*/ …

SQL Loader:

Loads data from 1 or more external fixed or variable length files (binary, text, packed decimal…)
REPLACE or APPEND
CONTROL FILE – contains input format of data file, tables, select criteria, and options
PARAMETER FILE – stores command line parameters
LOG FILE – stores logged results
DISCARD FILE – stores records not satisfying selection criteria
BAD FILE – stores rejected rows (Oracle error on insert)

Conventional – Builds arrays of rows and uses SQL statements to insert records.

Direct Path – builds blocks of data in memory and saves directly to allocated extents
No redo (unless archivelog is on), No constraint checks, No insert triggers, tables are locked.
You cannot direct path load into a clustered table.

Reorganizing Data:

Export – makes a logical copy of object definitions and data to file or tape (Logical backup).
Conventional (DIRECT=N) uses SQL select to extract data into buffer cache, then into evaluation buffer
Direct Path (DIRECT=Y) Data is read from disk into buffer cache – data in blocks is not reorganized.
COMPRESS=Y – initial extent resized as total current segment size
FEEDBACK=x – A dot is displayed in log file for every x records inserted.
FULL=Y or OWNER=user or TABLES=schema.table
CONSISTENT=Y – entire export as 1 read only transaction.

Import – Reads export files and copies object definitions and data into an Oracle database.

IGNORE=Y – Overlook object creation errors – causes rows to be imported into existing tables.
INDEXFILE=file – Index creation commands written to file (no actual import).
Order of operations: Type defs | Data | B-Tree indexes | Views, procedures, constraints | bitmap indexes.
Before import: Disable Ref Integrity before imports and increase buffer size for better performance
After import: Compile all invalid objects, reenable RI.

Transportable Tablespaces:

1) Make tablespace Read Only.
2) Export the Metadata. EXP TRANSPORT_TABLESPACE=Y TABLESPACE ts1
3) Copy data file to target system.
4) Copy export dump to target system.
5) Import the Metadata. IMP TRANSPORT_TABLESPACE=Y DATAFILES (file1, file2)
6) Bring tablespace online and enable original for Read/Write.
Note: Nested tables, varrays, and bitmap indexes are NOT transportable.

Tags