A question came up on OTN today asking for suggestions on how to enforce uniqueness on a pair of columns only when the second column was not null. There’s an easy and obvious solution – but I decided to clone the OP’s example and check that I’d typed my definition up before posting it; and the result came as a bit of a surprise. Here’s a demo script (not using the OP’s table):
The following question came up on OTN recently:
Which one gives better performance? Could please explain.
1) nvl( my_column, ‘N’) <> ‘Y’
2) nvl( my_column, ‘N’) = ‘N’
It’s a question that can lead to a good 20 minute discussion – if you were in some sort of development environment and had a fairly free hand to do whatever you wanted.
The most direct answer is that you could expect the performance to be the same whichever option you chose – but the results might be different, of course, unless you had a constraint on my_column that ensured that it would hold only null, ‘Y’, or ‘N’. (Reminder: the constraint check (my_column in (‘Y’,’N’) will allow nulls in the column).
The following question came up on OTN recently:
Which one gives better performance? Could please explain.
1) nvl( my_column, ‘N’) <> ‘Y’
2) nvl( my_column, ‘N’) = ‘N’
It’s a question that can lead to a good 20 minute discussion – if you were in some sort of development environment and had a fairly free hand to do whatever you wanted.
The most direct answer is that you could expect the performance to be the same whichever option you chose – but the results might be different, of course, unless you had a constraint on my_column that ensured that it would hold only null, ‘Y’, or ‘N’. (Reminder: the constraint check (my_column in (‘Y’,’N’) will allow nulls in the column).
During the OCM Preparation Workshop today in Bucharest, we got into a discussion about parallel inserts vs. direct load with append hint, where I said something like: “I think a parallel insert is always* also a direct load.” Most attendees were satisfied with that, but some still looked a bit sceptical. And I was also not 100% sure about it. While the others practiced, I did a brief research and came up with this answer from Tom Kyte and quoted him: “Parallel is always a direct path, if you go parallel, you will be appending.” Everybody in the room was immediately convinced – only I felt a bit uncomfortable with the outcome myself. After all, I don’t want to teach people to blindly follow my or any other guys claims without checking it if possible.
This post is related to 12c and an active database duplication for a standby I did in my lab environment. I’d say although I first encountered it on 12c there is a chance you run into a similar situation with earlier releases too.
I would normally use ASM for all my databases to make my life easier but this time I had to be mindful of the available memory on the laptop-which at 8 GB-is not plenty. So I went with file system setup instead. After the initial preparations I was ready to launch the one-liner on the standby database:
RMAN> duplicate target database for standby from active database;
This worked away happily for a few moments only to come to an abrupt halt with the below error message. I have started the duplication process on the standby.
This post is related to 12c and an active database duplication for a standby I did in my lab environment. I’d say although I first encountered it on 12c there is a chance you run into a similar situation with earlier releases too.
I would normally use ASM for all my databases to make my life easier but this time I had to be mindful of the available memory on the laptop-which at 8 GB-is not plenty. So I went with file system setup instead. After the initial preparations I was ready to launch the one-liner on the standby database:
RMAN> duplicate target database for standby from active database;
This worked away happily for a few moments only to come to an abrupt halt with the below error message. I have started the duplication process on the standby.
A recent question on an internal forum asked whether an index on a Foreign Key (FK) constraint designed to avoid locking issues associated with deletes on the parent tables needs to precisely match the columns in the FK. Could the columns in the index potentially be a different order or be appended with additional columns ? The answer is […]
I saw this very odd statement on an SAP system last week.
SELECT /*+ AVOID_FULL ("/bic/xxx") */ * FROM "/BIC/XXX" WHERE "/BIC/XXX"=:A0
I had never seen that hint before so I thought I’d do a little investigation. First I did a quick check on a test case to see if it worked.
Recent comments
3 years 5 weeks ago
3 years 17 weeks ago
3 years 21 weeks ago
3 years 22 weeks ago
3 years 26 weeks ago
3 years 48 weeks ago
4 years 16 weeks ago
4 years 45 weeks ago
5 years 30 weeks ago
5 years 30 weeks ago