Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…[Read more]
If you are a developer working in a MySQL environment, this blog post is for you. I share 3 MySQL commands or statements that you should know. That is a bold statement, I know. Turns out, once you do know (of) these commands, you will use them all the time. They minimize guesswork which leads to better productivity in other facets of your programming and querying workflow. I use them myself almost daily and am sure you will too once you see how simple they are to use. So why should you know them? Continue reading and find out…
OS, Software, and DB used:
- OpenSuse Leap 15.1 …
Oracle 12c introduces new catalog features including CDB_ dictionary views (which include a CON_ID column) superseding the DBA_ views that most DBA sqlplus scripts are based upon.
However, existing DBA sqlplus scripts can easily be modified using just a few simple sqlplus techniques to be compatible with 11g, as well as all types of 12c databases including legacy and container databases.
The following simple SQL and sqlplus techniques can be used to make a “universal script” that is compatible with all versions.
Illustrating the Issue
Let’s say for sake of example that we have a simple 10g/11g monitoring script that’s checking the amount of freespace in each tablespace by querying the DBA_TABLESPACE_USAGE_METRICS view.
On our 10g or 11g database the following query gives the necessary information:
SQL> select version from v$instance; VERSION ----------------- 22.214.171.124.0 SQL> select …[Read more]
This is the continuation of my efforts to stage an awesome Fedora developer’s instance. It shows you how to install Java 1.8 software development kit, which is nice to have. Though you can’t use Java 1.8 officially with Oracle SQL Developer 4.0.3 it is required for Oracle SQL Developer 4.1. Fortunately, the Oracle Product Manager, Jeff Smith has advised us that you can use Java 1.8 JDK with Oracle SQL Developer 4.0.3, and he’s written a comment to the blog post that it runs better with the Java 1.8 SDK.
After you install Oracle SQL Developer 4.0.3 or Oracle SQL Developer 4.1, you can watch Jeff Smith’s YouTube Video on SQL Developer 3.1 to learn how to use the basics of SQL Developer. I couldn’t find an updated version of the video for SQL Developer 4 but I didn’t try too hard.
You …[Read more]