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.

Contents

• 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( invce_no NUMBER UNIQUE, .. cust_id NUMBER) CREATE TABLE sales( invce_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 department. 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. © Smartsoft Computing Ltd, Bristol, England  Need help? Contact Us 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 linked to individuals. View woopra privacy policy.    View our privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.