| Considering constraints [message #176371] |
Thu, 08 June 2006 02:32  |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi!
i just want to ask for help on deciding what constraints could be applicable on this two tables, it's just a result from extraction of a huge table, here is what i just know. ill also attached some data
~suggested not null columns for the tables
[CS_CONTAINER_INFO]
CONTAINER_NO
BOOKING_NUMBER
[CS_TRANSACTION_INFO]
CONTAINER_NUMBER
BOOKING_DATE
BOOKING_NUMBER
BOOKING_STATUS
SHIPPER_NAME
~on indexes
[CS_CONTAINER_INFO]
container_no
booking_number
[CS_TRANSACTION_INFO]
container_no
booking_number
im not sure if those would be applicable to those tables, also why would it be the appropriate one, thank you so much for your help =)
|
|
|
|
|
|
| Re: Considering constraints [message #176388 is a reply to message #176373] |
Thu, 08 June 2006 03:13  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
It's pretty hard for us to tell you what constraints and indexes you should be applying since we know absolutely nothing about your data or the business rules surrounding it.
NOT NULLs
Make a column not null if it must contain a value period, that's it. no other explanation necessary, you along with others in charge of the data have to decide which columns that means.
Indexes:
Indexes should be put on columns that:
1: You will regularly use in the where clause of your sql statements.
2. That column contains a high level of selectivity (lots and lots of diferent values.
3. As a general rule, if your queries will return less than 4% of rows. (Note this is a very general rule and not entirely accurate- for more information read the Documentation)
4. Are foreign Keys
Do not put indexes on columns that:
1. Are rarely used in the where clause of SQL statements.
2. are in really small tables.
3. The queries 'normally' return more than 4% of the rows in the table (again see the note attached to point 3 above)
The key is that if you think an index should be used on a column. Test queries without the index, then test queries with the index (after taking appropriate statistics).
You also have to balance the need for faster queries with your DML requirements as indexes will slow down DML.
For more info se http://tahiti.oracle.com
Jim
|
|
|
|
| Re: Considering constraints [message #176394 is a reply to message #176388] |
Thu, 08 June 2006 03:38 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi!
thank you so much for your reply, also one thing, i frequently read those "small tables", is there any computation or means of knowing if this table is "small". So i need first to check the applications, and check the queries that will be run against those tables. I'll continue reading, thanks again sir =)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
