What is normalization with advantages and disadvantages?
Answers
Answered by
0
Benefits of normalization
Normalization produces smaller tables with smaller rows:
More rows per page (less logical I/O)
More rows per I/O (more efficient)
More rows fit in cache (less physical I/O)
The benefits of normalization include:
Searching, sorting, and creating indexes is faster, since tables are narrower, and more rows fit on a data page.
You usually have more tables.
You can have more clustered indexes (one per table), so you get more flexibility in tuning queries.
Index searching is often faster, since indexes tend to be narrower and shorter.
More tables allow better use of segments to control physical placement of data.
You usually have fewer indexes per table, so data modification commands are faster.
Fewer null values and less redundant data, making your database more compact.
Triggers execute more quickly if you are not maintaining redundant data.
Data modification anomalies are reduced.
Normalization is conceptually cleaner and easier to maintain and change as your needs change.
While fully normalized databases require more joins, joins are generally very fast if indexes are available on the join columns.
Adaptive Server is optimized to keep higher levels of the index in cache, so each join performs only one or two physical I/Os for each matching row.
The cost of finding rows already in the data cache is extremely low.
Normalization produces smaller tables with smaller rows:
More rows per page (less logical I/O)
More rows per I/O (more efficient)
More rows fit in cache (less physical I/O)
The benefits of normalization include:
Searching, sorting, and creating indexes is faster, since tables are narrower, and more rows fit on a data page.
You usually have more tables.
You can have more clustered indexes (one per table), so you get more flexibility in tuning queries.
Index searching is often faster, since indexes tend to be narrower and shorter.
More tables allow better use of segments to control physical placement of data.
You usually have fewer indexes per table, so data modification commands are faster.
Fewer null values and less redundant data, making your database more compact.
Triggers execute more quickly if you are not maintaining redundant data.
Data modification anomalies are reduced.
Normalization is conceptually cleaner and easier to maintain and change as your needs change.
While fully normalized databases require more joins, joins are generally very fast if indexes are available on the join columns.
Adaptive Server is optimized to keep higher levels of the index in cache, so each join performs only one or two physical I/Os for each matching row.
The cost of finding rows already in the data cache is extremely low.
Similar questions