Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Tips and Tricks To Make Your Oracle Systems
Better, Faster & Smarter
Send us your questions about Oracle and your feedback, or just click here to subscribe.
Constraints in Oracle databases
How to improve system performance
The resources page has recommendations for further reading to enhance your knowledge of Oracle and
contains links to information on OTN and other useful sites, See here for more Oracle tips and tricks.
From: V.Mahesh - please send me details about
constraints in oracle ( table level and column level )
There are 5 different types of constraints available in
Oracle: NOT NULL, CHECK, UNIQUE,
PRIMARY KEY and FOREIGN KEY.
All but NOT NULL can be defined at either the table
level or the column level. NOT NULL constraints
prevent column values being left undefined and so can
only be defined at the column level.
Example: CREATE TABLE emp(
id NUMBER NOT NULL,dept_id NUMBER)
The other constraints must be defined at the table level if they apply to more than one column.
CHECK constraints can be applied to one or more columns and are used to restrict the range of allowable
values for those columns. For example suppose we define an employee table EMP and want to ensure that
the id is always positive, we could define it as follows:
CREATE TABLE emp (id NUMBER CONSTRAINT emp_id_gt_zero CHECK (id>0),
dept_id NUMBER, salary NUMBER)
UNIQUE and PRIMARY KEY constraints are very similar except that a table can have only one primary
key (but many unique columns are allowed) and all columns in the primary key must also be NOT NULL.
CREATE TABLE sales(invoice_no NUMBER UNIQUE, .. cust_id NUMBER)
CREATE TABLE sales(invoice_no NUMBER PRIMARY KEY, .. cust_id NUMBER)
FOREIGN KEY constraints are used to define the relationship between one table and another and to
prevent orphaned records, thereby ensuring for example that all employees belong to an existing
CREATE TABLE emp(emp_id NUMBER , dept_id NUMBER, salary NUMBER,
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id));
From: Nath, Vivek - I have been doing large inserts in my database and on couple of tables and
the system has been degrading by the minute. Can i know some tips and tricks how to manage
this and improve the system performance?
There's not much information to go on here, but there are some general things that can be done. If a lot of
large inserts have been made, it is possible that the index has become skewed or fragmented and may need
rebuilding, but a better solution would be to drop the indexes all together whilst the loading is taking place
and re-create them once the load has finished. The same applies to constraints if they are enforced by
indexes. This should result in faster inserts as the indexes won’t be maintained for each insert operation,
If you are using partitioning in your database look at partition exchange as an alternative to inserting data
one row at a time. You could also consider the use of EXTERNAL TABLES if the data is coming from flat
files. This would avoid the need to load the data at all.
Another thing to check is the size of the extents that are created to ensure they are the optimal size for the
type of application and the operating system you're using.
One final check would be to look at the queries that access these tables after the inserts - are they using
full-table scans when an index read followed by table access by row id would be better? Or vice versa?
For more Oracle help, see the many free articles and tutorials we have on our site. Start from
http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm and just follow the links.
Looking to sky-rocket productivity, save time and reduce costs?
Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the
UK as well as self-study online training. See our Oracle training schedule, or tell us your requirements.
Oracle tips and tricks
Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make
your systems better, faster and smarter.
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Smartsoft Computing Ltd, Bristol, England Tel: 0117 924 7646 All rights reserved.