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
This series of articles has focused on Cost-Based Optimizer (CBO) hints or directives to the cost-based optimizer (CBO) on manual directions for the explain plan (be sure to read part 1, part 2 and part 3). In this final article, let’s take a look at the hints that I do use. QB_NAME Hint I use the QB_NAME hint quite […]
Read More
I have had to use far fewer hints starting with Oracle10. This optimizer takes a much closer look at index processing than all prior Oracle databases. I have found I simply don’t need index hints of any kind anymore. Try this during your testing. This setting will cause the CBO to ignore any hint […]
Read More
This article series will focus using Oracle cost-based optimizer (CBO) hints, and this second article on producing a series of hints that will replicate an exact explain plan. In Part 1, we discussed the OUTLINE produced by DBMS_XPLAN.Display(FORMAT=>’OUTLINE’), I find the IGNORE_OPTIM_EMBEDDED_HINTS to be an interesting hint to appear here. This hint says to ignore all hints. […]
Read More
This article series will focus using Oracle cost-based optimizer (CBO) hints, and this first article on producing a series of hints that will replicate an exact explain plan. This technique might be useful for some problem SQL but this technique is also a useful learning tool for hints. Remember, the DBMS_XPAN.Display procedure can be run […]
Read More