Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the
REPLACE INTO, and
DELETE statements. Indexes are also called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve (more...)
Have you ever wondered how to leverage substitution variables in anonymous block programs? There are several tricks that you can use beyond passing numeric and string values to local variable. The generic default appears to take a number unless you cast it as a string but that’s not really the whole story. The first two are standard examples of how to use numeric and string substitution values.
The following accept a numeric substitution value:
In the August 2015 issue of the NoCOUG Journal, we asked Stéphane Faroult why it is so hard to get SQL performance right the first time. His answer implies that SQL itself and the way it is taught are the problems.(read more
What would you do if you were lost in a labyrinth of underground caves? In the dark. With no food. Mark Twain tells the story of how Tom Sawyer and Becky Thatcher got separated from their picnic group and got lost in a labyrinth of underground caves. What’s that got to do with problem solving? I think that the first rule of problem solving is “If at first you don’t succeed, try, try, again.” (more...)
Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the
CALIBRATE_IO procedure from the
Oracle provides some great examples about how to use the
CALIBRATE_IO procedure of the
DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The
CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the
Who can resist a free book Rich Nimeiec book on SQL Tuning? O.K., those who know everything can resist. If you’re like me, this is an opportunity to learn from Rich. Click on the book image or this link to get a free copy, or if you want to pay $10 for a copy click here to buy Quick Start Guide to Oracle Query Tuning: Tips for DBAs and Developers from Amazon. (more...)
We can’t help attaching meaning to optimizer statistics. After all, they represent our data, don’t they? So we refresh them as frequently as possible and use the largest sample sizes that we can use. Recently, for the first time in my life, I encountered a group of DBAs who understood that statistics do not have any intrinsic meaning; that they are nothing more than a collection of numbers that influence the generation of query plans. (more...)
Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the from clause), but then they get errors like this:
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier
The problem is how Oracle treats (more...)
Read this article on my new blog
A very common use case when working with Hadoop is to store and query simple files (CSV, TSV, ...); then to get better performance and efficient storage convert these files into more efficient format, for example Apache Parquet.
Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem. Apache Parquet has the following
My thoughts on SQL plan management decision points:
SQL Patches are also available and not covered in the above flowchart.
Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.
To create an example, first let's create a table with only three records in it.
SQL> create table t
3 select rownum + 42 id
4 from dual
5 connect (more...)
Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
To solve this cent-problem, we decide that the (more...)
I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12.
Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after
those rows to avoid unwanted "double" matches.
But can there be uses where we want (more...)
Over past year we have been (hopefully) hearing about Oracle Big Data SQL.
This is a new(-ish) option from Oracle that allows us to run our SQL queries not just on the data in our Oracle Database but also against NoSQL databases and Hadoop. No extra coding is needed, no extra formatting is needed, etc.
All the hard work in connecting to the data in this systems, translating it into executable code on these systems, (more...)
Read this article on my new blog
Apache Drill allows users to explore any type of data using ANSI SQL. This is great, but Drill goes even further than that and allows you to create custom functions to extend the query engine. These custom functions have all the performance of any of the Drill primitive operations, but allowing that performance makes writing these functions a little trickier
When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the (more...)