To improve the performance of an application, you can make local copies of remote tables that use distributed data or create summary tables based on GROUP BY operations. Oracle provides materialized views to store copies of data or aggregations. Materialized views can be used to replicate all or part of a single table or to […]
Read More
The ORDER BY clause is executed after the WHERE, GROUP BY, and HAVING clauses. It can employ group functions, or columns from the GROUP BY, or a combination. If it uses a group function, that function operates on the groups and then the ORDER BY sorts the results of the function in order. If the […]
Read More
Let’s take a moment to discuss the real power of a relational database. Once you’ve created a view with the count by category, and a second view displaying the count for the entire table, you can join them together to reveal information never before apparent. For instance, what percentage of the books are in each category? […]
Read More
You can create a table that exists solely for your session or whose data persists for the duration of your transaction. You can use temporary tables to support specialized rollups or specific application-processing requirements whose results will not persist beyond the session or even past a COMMIT statement. To create a temporary table, use the […]
Read More
Views can build on each other. You can easily join views to other views and tables to produce additional views to simplify the tasks of querying and reporting. As your groupings grow more complex, you will find that views are invaluable to your coding efforts; they simplify the representation of data at different grouping levels […]
Read More
Scaling of existing software solutions is never a linear process. Contemporary database systems consume different kinds of resources (CPU time, memory, I/O channels) and behave differently when some of these resources become sparse. Considering that hardware is always limited by available budgets and technologies, it is critical to understand potential bottlenecks. Typically, this is accomplished […]
Read More
The ASM instance can now be set up as a local ASM instance on the database server or as an ASM cluster. An ASM instance can manage the following files for all of the instances on each cluster node: control files, data files, tempfiles, spfiles, redo logs, archive logs, RMAN backups, Data Pump dumpsets, and […]
Read More
Simply understanding a nine-step tuning process is not enough to be able to make a system work efficiently. You need a formal, quantitative way to measure performance. You also need some specific vocabulary to avoid any possible misunderstanding. The vocabulary list may vary somewhat, but the following terms are fundamental: Command. An atomic part of […]
Read More
One of Oracle’s more interesting but little used or understood facilities is its CONNECT BY clause. Put simply, this method is used to report, in order, the branches of a family tree. Such trees are encountered often—the genealogy of human families, livestock, horses; corporate management, company divisions, manufacturing; literature, ideas, evolution, scientific research, theory; and […]
Read More
People who have worked with computers for any period of time are familiar with the concept of a file; it’s a place on disk where information is stored, and it has a name. Its size is usually not fixed: If you add information to the file, it grows larger and takes up more disk space, […]
Read More
Most single-value functions in Oracle are pretty straightforward. This article gives short examples of the major functions, and it shows both the results of the functions and how they correspond to columns, rows, and lists. After the examples, you’ll learn how to combine these functions. A table named MATH was created to show the calculation effects […]
Read More
Here are the rules Oracle uses to execute each query, and the order in which execution takes place: Choose rows based on the WHERE clause. Group those rows together based on the GROUP BY clause. Calculate the results of the group functions for each group. Choose and eliminate groups based on the HAVING clause. Order […]
Read More