A repeating group is a series of fields/attributes that are repeated throughout a database table. It is a common problem faced by organizations both large and small, one that can have several ramifications. For example, the same set of information being present in different areas can cause data redundancy and data inconsistency. Moreover, all of this repeating data can eat up a lot of valuable disk space and take a long of time to search through. The problem of repeating groups can be manageable in small organizations, but for larger organizations, whom must manage huge volumes of information, repeating groups can become a nightmare to deal with.
In today's blog, we'll learn how to identify repeating groups both during design time and in existing databases, as well as how to fix them. Since repeating groups are a phenomenon that can affect any relational database, we'll use Navicat Premium as our database development tool.
ANSI SQL includes several aggregate functions, which allow you to perform a calculation on a set of values to return their result as a single value. These include Count(), Min(), Max(), Sum() and AVG(), and others. By default, aggregate functions apply to all rows, but you can narrow down the field by applying a WHERE clause to the SELECT statement. Moreover, you can conditionally select certain rows using a few more techniques that we'll explore here today using Navicat Premium. These include the use of a CASE statement as well as the GROUP BY clause. We'll apply these techniques on the AVG() function, but they will work equally well with all aggregate functions.
SQL makes selecting all fields in a table quite trivial via the SELECT * (SELECT ALL) clause. Unfortunately, as soon as you omit a column from the list, the SELECT ALL statement goes out the window. Writing out every every column name can quickly become tedious, especially if you happen to be dealing with tables that contain dozens of columns. What if we could select every column but one - selecting by exclusion rather than inclusion? It can be done. In fact there are a couple of ways to do it - one simple, the other, a bit less so. These will be the focus of today's blog.
Every so often, we need to rebuild indexes in Oracle, because indexes become fragmented over time. This causes their performance - and by extension - that of your database queries, to degrade. Hence, rebuilding indexes every now and again can be quite beneficial. Having said that, indexes should not be rebuilt to often, because it's a resource intensive task. Worse, as an index is being rebuilt, locks will be placed on the index, preventing anyone from accessing it while the rebuilding occurs. Any queries trying to access this index in order to return the required results will be temporarily blocked, until the rebuild is complete.
In today's blog, we'll learn how often to build indexes and how to determine when an index needs to be rebuilt.
The number of images in web applications has been growing steadily in recent years. There is also a need to distinguish between images of different sizes, like thumbnails, web display images, and the like. For example, one application that I recently developed shows news items where each item has a thumbnail and main article image. Another app shows company logos in small and large sizes.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
