[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Pros-Cons of Defining Relations for Oracle Tables.

Posted on 2009-04-28
Medium Priority
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

mrjoltcola earned 1050 total points
ID: 24279159
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

873 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