Dynamic SQL and stored procedures are two of the most important components of SQL Server. In this article, we will look at the advantages and disadvantages of each of them and when to use them. (more…)
In this article, we’ll look at how an index can improve the query performance.
Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement.
They aren’t a “silver bullet” – they don’t always solve performance problems with SELECT statements. However, they can certainly help.
Let’s consider this on a particular example.
In this article, I would like to talk about one of the basic certifications from Oracle – Oracle Database SQL Certified Expert. Unfortunately, this certification has become unavailable recently, but still, this article may be useful for preparing for other certifications and exams from Oracle. I wish a good read to everyone who wants to know which questions and tricks may await them and wants to be ahead of the game. (more…)
A subquery is a powerful way to find the data you want to use for another query. They are often used in SELECT and UPDATE statements to make these queries more efficient and easier to maintain.
There are several different ways to use subqueries in UPDATE statements. Let’s take a look at each of them.
I continue a series of articles on the basics of EXPLAIN in PostgreSQL, which is a short review of Understanding EXPLAIN by Guillaume Lelarge.
To better understand the issue, I highly recommend reviewing the original “Understanding EXPLAIN” by Guillaume Lelarge and read my first and second articles.
In my previous article, we started to describe the basics of the EXPLAIN command and analyzed what happens in PostgreSQL when executing a query.
I am going to continue writing about the basics of EXPLAIN in PostgreSQL. The information is a short review of Understanding EXPLAIN by Guillaume Lelarge. I highly recommend reading the original since some information is missed out.
Why does it take so much time to execute a query? Why are there no indexes? Chances are you’ve heard about EXPLAIN in PostgreSQL. However, there are still many people who have no idea how to use it. I hope this article will help users tackle with this great tool.
This article is the author revision of Understanding EXPLAIN by Guillaume Lelarge. Since I have missed out some information, I highly recommend you get acquainted with the original.
When developing an information system that also includes various processing of design and technological documentation, I faced the following problem.
We have a certain product structure. During the day, different parts of this product are changed and by the evening, it is already unclear what has been changed. Sometimes, products can consist of more than 10 000 elements. The elements are not unique, and the reality is that the structure can be often modified, although the product is almost ready. The failure to understand the scope of changes complicates planning.
The product structure can be represented as a tree graph in PL/SQL. Since I could not find a suitable way to compare two graphs, I decided to create my own method.
A transaction in SQL is a unit of execution that groups one or more tasks together. A transaction is considered successful if all the tasks within it are executed without error.
However, if any of the tasks within a transaction fails to execute, the whole transaction fails. A transaction has only two results: successful or failed. (more…)
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it” message.
Let’s have a look at the existing workarounds.
The first one, through the package, is ancient and seems to be effective, however, it takes much time to prepare and run it. The second one is simple and performed using compound triggers.