Pros-Cons of Defining Relations for Oracle Tables.

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.

GK
prjcity.txt
prjlocations.txt
GurcanKAsked:
Who is Participating?
 
mrjoltcolaCommented:
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).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.