Oracle provides us with many functions that allow us to analyze and aggregate the data, returning results that differ greatly from the result sets you’ve seen so far. A function manipulates the contents of a column in a SQL statement. You can find what the largest credit limit is in the CUSTOMERS table, and you […]
Read More
Having the user interface and the terminal window for access to the command line means that there are a couple of ways to navigate through Linux and do what needs to be done. In previous examples of using the command line, files were edited, users were added, and parameters were set up. In managing some […]
Read More
Pluggable databases are the new kid on the block, one of the newfangled features of 12c. According to the documentation, a pluggable database (PDB) is a portable collection of schemas, schema objects, and non-schema objects that appear to an Oracle Net client as a separate database. One or more PDBs together are called a container database […]
Read More
This topic could fill an entire book, but for our purposes here, we’ll focus on the most important aspects. This article highlights concepts and provides a general overview of the players that deliver this proprietary database management system. These players fall into three main categories: Shared memory A section of the host server’s memory through which all […]
Read More
In the last few database versions, Oracle has consistently extended the functionality of the WITH clause. Oracle Database 12c introduces the capability to add user-defined functions and procedures directly to SQL statements instead of creating them as separate objects: The goal of this approach is to decrease the number of context switches between SQL and […]
Read More
Understanding how SQL and PL/SQL work together is critical for good database system development. The conceptual differences between these languages are large, but they complement each other in a way that is unique in the industry. SQL does the “heavy lifting” of data retrieval, while PL/SQL handles the procedural logic. Together, they form the backbone […]
Read More
Throughout the history of relational databases, the issue of denormalization has never been settled. Theoreticians still continue to argue about it, but from the practical point of view, most database systems are denormalized to some extent. For example, about 99.9 percent of address records in the United States have the ZIP code, city, and state […]
Read More
Sometimes performance problems are initiated by requirements beyond the control of an average database developer. For example, in a number of front-end environments, you cannot use DMLs against a table or view if it does not have a primary key. With tables, that kind of problem is taken seriously and constraints are not added indiscriminately. […]
Read More
The biggest misconception that deters people from using Dynamic SQL is the idea that it will always be slower than regular SQL. Nobody questions the fact that if you compare the execution of a plain query with exactly the same query wrapped with EXECUTE IMMEDIATE, you will see some performance degradation. But this is not […]
Read More
Too many DBAs insist that DDL commands should never be fired in a production environment. Therefore, they often try to prevent DDLs from happening by any means, whether technical or organizational. In addition to strict internal policies, the authors have encountered database-level triggers that limit the whole system to only basic DML operations. Of course, […]
Read More
The biggest challenge in learning Dynamic SQL is to get past your initial fear of this feature. Of course, with any advanced technology comes risk with its misuse. In the case of Dynamic SQL, the chances of encountering security or performance issues often outweigh the potential benefits. With proper understanding of the underlying mechanisms, you […]
Read More
If you ask several performance tuning specialists separately about the most irritating feature in the Oracle environment, very often you will hear exactly the same answer: the PL/SQL code wrapper. It is true that even Oracle uses it to protect internal packages. However, Oracle can be trusted to perform adequate testing and provide support if […]
Read More