I understand the concept of Oracle. But I have several questions need your help:
1. Should we create primary key for almost every table?
Oracle will automatically create a unique index with the creation of a primary key on the table. If i do not need index on this column should i create this primary key? I remember that unique index is not easy to maintain. Is that correct?
2. In a table creation script should i specify the storage,
such as initial, next? Or should i just use the tablespace
For some special table I will spesify at table level.
3. Should i create the foreign key for every related table?
The foreign key will affect the exp/imp.
I have no idea about what the best way to create schema.
Every help is appreciated.
Well, your first and third question are basic database design issues not related to Oracle specifically. Personally, I can't imagine the reason for having tables without PKs. I make an abstracted, or surrogate, key for (nearly) every table in every database I make. These are simple, 1 column, numeric keys filled via sequences. This eliminates the question of uniqueness. However, I am concerned that you ask about that at all. If you would not have a unique PK, how would you ever identify an individual record? You would not even be in first normal form. As for the FKs every FK should be defined, except under extreme circumstances. Constraints and indexes can be disabled or dropped at imp/exp time if that is an issue, but sound database design would require their existance.
Of course, I am assuming an OLTP database. If you are doing DSS, then modifications are not an issue, so the FKs drops in importance. However, you still have to join tables, so if you have a child table, it still has to have the PK from its parent, so you still have to have PKs.
>>1. Should we create primary key for almost every table?
I agree with CHRISLONG.
>>Oracle will automatically create a unique index with the >>creation of a primary key on the table. If i do not need >>index on this column should i create this primary key? I >>remember that unique index is not easy to maintain. Is >>that correct?
Yes. Oracle will do this. You would do most of your searches based on IDs (PKs) and the joins that you do in your queries would be based on PKs and hence, Indexes on PKs are required and it's an Oracle feature that you can not avoid or over-rule it...I mean, there is no way you can create a PK without an index.
Maitaining UNIQUE KEY is just like maitaining another Oracle object. Who said it is tough to maintain? If the business rule says it must be unique then, this is how you enforce it. At the same time, you do lot of queries based on unique indexes as well.
>>2. In a table creation script should i specify the storage,
>>such as initial, next? Or should i just use the tablespace
>>For some special table I will spesify at table level.
You could specify your own parameters at the table level. Or, you could create 2-3 tablespaces and create tables in each of these based on their volume. This way you could specify parameters at tablespace level itslef instead of at the table level.
>>3. Should i create the foreign key for every related table?
>>The foreign key will affect the exp/imp.
It all depends on your business and the type of application you are designing (OLTP or DSS). FKs will not pose a problem during EXP/IMP. During IMP, oracle will load the data first and then create the constraints later on. So, you will be safe here.
For example, the table customer have column customer_id and active. The customer_id is generated by an Oracle sequence. Usually we use customer_id and active in the WHERE clause. If I create a primary key on customer_id, I also need to create another index on customer_id and active.
The index created with primary key actually can help me nothing but slow the insert, delete. The sequence will make sure the unique of the customer_id.
Why is the need for you to include ACTIVE in the WHERE clause (I am assuming that your WHERE clause would look like: WHERE customer_id = 1234 AND ACTIVE = 'N'). Since customer_id is the PK and is unqiue in the table and also you know the customer_id so, you would always get one record.
If you have to include ACTIVE in the where clause then, I WOULD NOT consider creating an index on ACTIVE column.
Index on customer_id would be just enough.
You may need to have index on ACTIVE column to speed up the query process if your SQL does not contain CUSTOMER_ID column in the WHERE claude. For example, If you want to know who are the active custmers in the state Florida, then your SQL looks like:
SELECT CUST_NAME, ACTIVE, STATE_CD
where ACTIVE='Y' and STATE_CD='FL';
*Only* if it is the secondary field in an index. Having a flag (True/False) field as the only or leading field in an index is pretty much pointless. The optimizer will see that the field has 2 values and assume a 50% distribution of the values. this means that a query on either value will return 50% of the rows (an extremely low selectivity) and (correctly) opt for a tablescan every time.
"What if I add another field to the index, say: ACTIVE, STATE_CD?"
No. If you are going to create a multi-part index, make it STATE_CD, ACTIVE. The reason is that:
- If you have both fields in the WHERE clause, then it will not matter which order you make the index.
- If you only have ACTIVE in the WHERE clause, *neither* index would be utilized, per the reasoning in the first paragraph.
- If you only have STATE_CD in the WHERE clause, the second index (STATE_CD, ACTIVE) would be utilitized, making it a much better choice as an index.
"Should I make an index on CUSTOMER_ID, ACTIVE as originally proposed"?
No! The CUSTOMER_ID field alone allows for a unique index and therefore 100% selectivity. There is absolutely no reason to add fields to an index once you have reached 100% selectivity! Adding fields to such an index will only increase the index size and hurt its I/O. Again, to be perfectly clear, if you have CUSTOMER_ID in the WHERE clause *regardless of any other fields in the WHERE clause*, the optimizer will (almost) always choose to use the unique index on this primary key to get to the target record. Once it is at the target record, it can test the values of any other fields on that record, such as ACTIVE. There is *no* need to have any other indexes that begin with CUSTOMER_ID.
Hope this helps,
PS. For those of you out there that are anal like me, you will know that there are exceptions to every rule. I am purposely ignoring the extreme cases here to drive home some fundamental rules of database design and not confuse the issues.