Subscribe to our ezine - just go to www.asktheoracle.net/oracle-tips-signup.html
The resources section has recommendations for further reading to enhance your knowledge of Oracle and contains links to information on OTN and other places.
New subscribers can access Oracle 9i training material here and advice on general Oracle performance tuning here
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,
salary 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_non_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.
Example:
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 department.
Example:
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 no a lot of information to go on there, 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. You might also want to check the size of the extents that are created to make sure 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 acccess the particular tables - 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 performance tuning tips see:
http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm
---------
From: shiva shankar - What is the exact definition of normalisation.How many types are there please explain them in detail
Normalization is the process of decomposing objects into their constituent parts, removing repeating groups and any items not functionaly dependent on the key. The process was defined by Dr. E.F. Codd in 1970 when he defined the relational (database) model.
There are 6 levels of normalization called normal forms (abbreviated NF): 1st normal form (1NF); 2nd normal form 2NF; 3rd normal form (3NF); Boyce-Codd normal form (BCNF); 4th normal form (4NF); and 5th normal form (5NF). Each is a refinement of the previous normal form, although it is very rare to progress beyond 3NF.
It should be stressed that normalization is an analysis tool, not a programming tool, so in practice most databases will be denormalized to a certain extent to improve performance. The trade off is redundant storage of information.
---------
From: "Manish" - how to see all the indexes created on a particular table? how to view all the indexes in a tablespace?
To see all the indexes created on a particular table you need to query all_indexes specifying the name of the table of interest as follows:
SELECT index_name FROM all_indexes WHERE table_name = 'EMP';
To see all the indexes in a particular tablespace you just have to query all_indexes specifying the name of the tablespace of interest as follows:
SELECT index_name FROM all_indexes WHERE tablespace_name = 'USERS';
---------
From: Bill Rowe - I read your articles on optimizing views and wondered what the differences between cursors in packages and views might be. I've been reading Feuerstein's O'Reilly books on PL/SQL and wonder if a cursor would work as well as a view, if the cursor were in a package specification.
This is an interesting question. On one level, views and cursors are completely different: cursors are defined in PL/SQL and views are defined in the database, but a more fundamental level they are very similar - they are both pre-compiled queries so they could be used in similar ways. However using views would provide more flexibility, they can be combined into other queries which you could not do with cursors, they can also be used for database updates which again you could not do with a cursor. The answer, therfore, is that a cursor would work as well as a view if you only need to use it for querying the database and you don't want to combine the view with other queries.
---------
From: Hrishikesh Srivatsa - I have a question regarding a SQL query. There is an employee table emp which has 3 fields namely EmpNo (Employee number) EmpName (Employee name) MgrEmpNo (Employee's manager No). The CEO's Employee Manager No can be assumed to be null or a special value.
A query has to be written to display the organization's heirarchy.
The answer to this is to use the hierarchical query clause of the select statement - i.e. use the connect by prior keyword to indicate the relationship between the parent and child columns and the start with keywords to indicate the root of the tree. In this example, the query would be :
SELECT EmpName FROM emp
CONNECT BY PRIOR EmpNo = MgrEmpNo
START WITH MgrEmpNo IS NULL;
---------------------------------------
Looking for more Oracle tips and tricks ?
If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Send us your questions and we'll answer them in the next newsletter.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.