A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.
So, when should we use dynamic SQL?
FAQ
Frequently asked questions about the Oracle database, SQL and PL/SQL.
Static SQL vs Dynamic SQL: Which to use?
Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.
Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.
INNER JOIN and OUTER JOIN Explained
For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.
Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.
How to Find out Your Oracle Database Version
Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your Oracle database version.
Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.
What are CBO and RBO?
In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well — get the results in fastest possible time, using the least possible resources.
This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options of CBO (cost-based optimizer) and RBO (rule-based optimizer). RBO has been desupported by Oracle 10G onwards, but it is still worth knowing about to understand the evolution of Oracle’s optimization techniques.
This post explores the key differences between CBO and RBO.
15 Things You Should Know about the ORDER BY Clause
-
When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.
-
The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.
-
ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.
The Difference Between Views and Materialized Views
Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion if you are new to these database objects.
Here’s a summary of the key differences between views and materialized views, to put an end to all mix-ups. By the end of the article, you should be in a position to decide if your specific scenario needs a view, a materialized view, or neither.
How to Find Duplicate Records in a Table
A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked. Now we want to find and delete those duplicates.
In this article, we’ll see how to achieve this.
What is CTAS?
You have probably heard other DBAs/developers and websites talk about creating a database table through CTAS. Does that term leave you befuddled? There’s a very simple explanation to that acronym – read on!