I’m chairing a Q&A session on the Cost Based Optimizer for OUG Ireland.
We will be copying the format we used for the UKOUG annual conference, with some questions submitted in advance and Martin Widlake on the door handing out 5×3 cards for the audience to supply questions as they arrive, then running around the room with a microphone reading out questions or getting the audience to reply to comments from the table.
If you’d like to submit a question in advance, email me, or post it in the comments section below.
We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement.
Here’s another trivial example of this- I was doing a little demo script for an AskTom question.
Here’s the script running in 18.104.22.168
A while back I did a blog post showing that when you have set a column to UNUSED, it still “counts” as a validation step when doing exchange partition. So if you had a partitioned table that previously had a column set to unused, then the candidate table to be exchanged in also had to have undergone the alteration. Just having the visible columns aligned is not sufficient.
In the comments, Sergey presented an interesting variation of this, which we will look at below
As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.
However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component.
Cursors can also be defined in the same way as the following example shows:
CREATE PACKAGE my_pkg AS CURSOR c_recent_hires RETURN emp%ROWTYPE; ... END emp_stuff; / CREATE PACKAGE BODY my_pkg AS CURSOR c_recent_hires RETURN emp%ROWTYPE IS SELECT * FROM emp WHERE hiredate > sysdate-30; ... END emp_stuff; /
The long existing BITAND function is now within the documentation, to let you do logical AND on two numbers, and is also available from PL/SQL
If you need other bit operations, a little boolean math should suffice Just make sure you stay within the limits of BINARY_INTEGER
CREATE OR replace FUNCTION bitor( x IN binary_integer, y IN binary_integer ) RETURN binary_integer AS BE
I saw this on an ideas forum today
and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository.
So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, but it should get you going
It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What I’m referring to here is the default mechanism for locking in Oracle.
If you try access a row that is locked by someone else, you will wait. Don’t get me wrong … that’s a very good thing. The alternative – of letting two people change the same data at the same time is equivalent to saying “I dont care about my data at all”.
And how long will we wait ? Well… we’ll wait forever. We will never stop waiting, until that lock has been released.
I don’t know about you … but for me… forever is long time. A really long time.
I saw a tweet that made me smile a few days ago:
Like many of you, I started out my working life in IT as a computer programmer. Like some of you, I still am a computer programmer from time to time. I don’t think I’ve had a year of my working life when I did not do either some application development programming or some database infrastructure programming. I am constantly writing small (or not so small) SQL or PL/SQL programs to do what I do in Oracle.
Most people already know about the very cool GATHER_PLAN_STATISTICS hint. If not, you can see an example here
But here’s a common cause of confusion when using it in SQL Plus: