Exforsys.com
 

Sponsored Links

 

Interview Notes Tutorials

 
Home Career Center Interview Notes
 

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.



Read Next: Oracle Interview Preparation Notes Part 4



 

 

Comments



Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2009 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape