Pros-Cons of Defining Relations for Oracle Tables.

Posted on 2009-04-28
Last Modified: 2013-11-16
Hi Experts,

We are planning to create two tables named prjcity and prjlocations for our application. (attached below) The application performs mainly the "select" queries for these two tables.Below are my questions about this implementation:

1. What is the performance effect if we define a relation for 'comments' field between two tables?
2. If we define above relation, is there any optimized select query for this type of relation? (any guideline welcomes)

Best regards.

Question by:GurcanK
    1 Comment
    LVL 40

    Accepted Solution

    A relation is just a concept, but it is implemented and enforced by foreign keys, so the more usual term is foreign key, or FK for short. We use FKs to enforce referential integrity (RI), which means the relationship is more than theoretical, it is enforced, and we can guarantee that any data in the child will refer to a primary key in the parent.

    There is a VERY tiny performance overhead for Oracle to enforce referential integrity, but we accept that minor overhead because referential integrity provides benefits that we want, such as guarantees to programmers that they can rely on when writing code. In a database, where there is no enforced referential integrity, all sorts of nasty data problems crop up.

    Another very important benefit of explicit relationships / RI is that the schema model provides explicit information about the relationship, so you know for sure if 2 tables are related. A modelling tool can also analyze the relationships and convert to a visual form. This is very important when taking over a database from another person, where you need to learn the design.

    I have had customers that came to me with 200 table databases and no explicit relationships, and they want me to analyze their design. The only way I could do it was to make assumptions based on the names of the columns, because there were no explicit relationships. This is very bad, because often there are names like ID or ORDER_ID that exist all over and in some cases have different meanings.

    So you should _always_ define an explicit relationship so your model retains valuable information and the data stays consistent.

    >>1. What is the performance effect if we define a relation for 'comments' field between two tables?

    A relationship in Oracle will be typically implemented with a primary key on the parent table, (which creates an index no the parent column) and a foreign key constraint on the child table. The foreign key does not create an implicit index, but the DBA typically creates the index on that column. The typical queries in a database will join related tables on their key columns, so indexes should *always* exist for joined columns.

    >>2. If we define above relation, is there any optimized select query for this type of relation?

    Yes. For tables A and B, related by columns A.ID and B.PARENT_ID, you write the join query

    select ... from A
    join B on A.ID = B.PARENT_ID

    This SQL uses the relationship you have defined, and the performance enhancement provided by the indexes behind that relationship. Always index your keys (your relationships).

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle View 13 36
    Cross Outer Join 4 42
    custome paging in C# & oracle using inline queries. 12 29
    Oracle RMAN Database Restore 5 15’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
    I showed you how to use console view (HERE ( -– but how do you set it up on the admin side of Salesforce? Note that you have to have Admin leve…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now