One of the key tenants of MongoDB schema design is to account for the absence of server-side joins. Data is joined all the time inside of application code of course, but traditionally there’s been no way to perform joins within the server itself.
This changed in 3.2 with the introduction of the $lookup operator within the aggregation framework. $lookup performs the equivalent of a left outer join – eg: it retrieves (more...)
As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result.
Here’s a trivial example
SQL> create table t as
2 select rownum x
3 from dual
4 connect by level <= 10;
SQL> select x, lag(x) over ( order by x ) as lag_Test
2 from t;
An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c. We can see the difference by running a simple script in both v11 and v12
SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production
PL/SQL Release 220.127.116.11.0 - Production
CORE 18.104.22.168.0 Production
TNS for (more...)
I’ve been meaning to write about Apache Spark for quite some time now – I’ve been working with a few of my customers and I find this framework powerful, practical, and useful for a lot of big data usages. For those of you who don’t know about Apache Spark, here is a short introduction.
Apache Spark is a framework for distributed calculation and handling of big data. Like Hadoop, it uses a clustered environment in (more...)
It’s that time again. Another year older and unfortunately not another year wiser. I still keep reliving those same mistakes again and again. I guess the old adage, “You can’t teach an old dog new tricks”, is true. 🙂
I’m sure I’ve said this before, but the plans for this next year are “to do me” a bit more. That might sound funny to people who know me, as I am incredibly self-centred, but I keep (more...)
“Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in June 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use… TidBit #oratidbit The Oracle #APEX […]
This is not going to be a rant about Brexit and how the selfish, stupid and simply fearful were led by a jingoistic & deceitful bunch of career politicians to show the worst side of the UK. Well, maybe a bit… It’s more about something that struck me about Brexit in respect of who is to “blame”. And there are aspects of this that are echoed in our own industry.
I feel that there is (more...)
For some time I’ve been openly critical of the user experience (UX) of Oracle Public Cloud. Just to be clear what I mean by this…
- I am not talking about the quality of the services that are delivered, or the underlying technologies being used. I’m talking about the day-to-day usage of the Oracle Public Cloud (OPC) interface. The web pages you use to administer this stuff.
- I’m not talking about the SaaS offerings, like Oracle (more...)
This month I worked with a chicagoland company to improve performance for eBusiness Suite on AIX. I’ve worked with databases running on AIX a number of times over the years now. Nevertheless, I got thrown for a loop this week.
TLDR: In the end, it came down to a fundamental change in resource accounting that IBM introduced with the POWER7 processor in 2010. The bottom line is twofold:
- if SMT is enabled then the meaning (more...)
The cost-based optimizer tries to merge views whenever possible but sometimes we ma y want to override this behavior; that is, we may want the optimizer to push predicates into the view instead of merging the view into the main query. If the main query performs an inner join to the view, it becomes necessary to convert the inner join to an outer join if the database version is less than 22.214.171.124.(read more
We have an OEM job that we run after each system-wide Unix password change to verify success on all hosts.
The OEM Job is run against Dynamic Groups of hosts. In this example I’ve limited it to the group DBHosts.
The job consists of a very simple call for
The Credentials for the job contain the new password, of course.
The job quickly tries to connect to each host with that named named (more...)
There isn’t a documented method of controlling which specific predicate to push.; it appears that the decision is left to the cost-based optimizer. However there is an undocumented method. Both the PUSH_PRED and NO_PUSH_PRED hints accept an optional second parameter(read more
Carlos Sierra spent 17 years in the SQL performance group of Oracle Support. He couldn’t move a mountain nor pull down a big oak tree. But Carlos Sierra became a mighty big man by putting all the knowledge he gained during that time into a wonderful tool called “SQLT.”(read more
Someone recently gave me an insight with which you would probably agree with: If query performance is satisfactory before and after the upgrade, then business users are unlikely to complain if new optimizer features are not being used after the upgrade.(read more
In the previous parts I talked about security of the application and infrastructure layer of database security. This will be the last post in the series and I want to dedicated it to a very important topic that is not a database only issue – passwords. Links to the previous posts: Part 1 – general […]
When a query contains a regular or inline view, there are 3 basic strategies for the optimizer to choose from:
1) merge the view (no “VIEW” operation in the plan)
2) instantiate the view as the whole and join it to the rest of the query (the plan shows a VIEW “operation”)
3) push join predicates inside the view (the plan shows “VIEW PUSHED PREDICATE”).
The last strategy in that list is similar to a (more...)
Container Databases have been an area that I have researched intensively over the past years. With this post (and hopefully some others that follow) I would like to demonstrate some of the new situations the DBA might be confronted with. Please don’t use this post to give the new 12c architecture a hard time: standardised deployments (which I love) help you a lot. Not only do your DBA scripts work reliably everywhere, but the error condition (more...)
Last month I had the privilege of delivering the key note session to the quarterly gathering of Northern California Oracle User Group. My session was a set of vignettes in a theme regarding modern storage advancements. I was mistaken on how much time I had for the session so I skipped over a section about how we sometimes still expect systems performance to add up to a sum of its parts. This blog post (more...)
A new version 4.23 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version comes only with minor changes, see the change log below.
Here are the notes from the change log:
- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)