Performance Recommendation on creation of table in Oracle

Performance Recommendation on table while creating.

Keep things in mind while creating table in Oracle. Choose it’s tablespace or constraints during creation.

1. PCTFREE value higher than default 10% for update operation later on if no update is considering on table keep it lower value(prevent row migration/chaining)

2. All tables should be created with a primary key.

3. Most selected column in where clause must have index on it.

4. If no archive or recovery for table is required then enable its nologging option.

5. PCTUSED should be set according to delete or insert operation default is 40%.

6. Keep table and indexes on separate tablespace or disk.

7. If table is small and use. You can enable keep buffer cache to place it in Memory permanent for fast access instead of fetching every time from the disk.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.