Career Center Interview NotesManaging Indexes:
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:
