Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show off all the tables involved, the joins, the volume of data involved, and the indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables.
But something I see people learning over and over and over again.
I was reminded of a recently asked asktom question - regarding an "intermittent" 'Oracle' bug in a java application ('Oracle' used for sarcasm on my part).
I was reading the current Steven Feuerstein's Blog entry (read that link before going on). It was exactly the same problem found by the java developers on asktom - which they were certain would be an 'Oracle bug' (hint: it wasn't, it was clearly and demonstrably in their code).
Funny, I see the pattern so often that I saw the bug in both bits of code almost immediately. It jumped out and hit my in the face.
SPOILER ALERT: don't read past here if you want to test your ability to find the bug, read Steven's article first.
Maybe I'll put in a request for the to_date function to be overloaded to accept a date as input and just return that date as output.
edit: added after *thinking* about what I just said...
That of course would never work. People are expecting the date format to be applied to the string, so just returning the date could of course NOT be the right thing to do. I guess the overload would have to turn:
to_date( DATE, 'fmt' )
into
to_date( to_char(date,'fmt'), 'fmt' )
instead of
to_date( to_char(date, IMPLICIT_FORMAT ), 'fmt' )
as it does now, but it would be a HUGE change to existing code that 'relies' the way it currently works...
The to_date function takes a string as input and since the date can be converted to a string - it is. I've seen MANY people use:
to_date( date )
to "truncate" a date (horrible idea - not only slow, but RISKY) - it would break their code (not that it isn't already broken) so it would probably be questionable...
Important enough to point out to a wide audience though - beware implicit conversions and watch out for to_date( of a date )!
Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. Remember, a migrated row is one that has been moved in its entirety to a different block because an update to the row resulted in the row needing more space than was [...]
As you may already know, the terminal 10gR2 patch set (10.2.0.5) has been released recently on the Linux x86 & x86-64. Today I’ve installed 32-bit version on a test machine and was going to have a look on the Doc ID 1088172.1 for list of bugs fixed in this patch set, but there were almost [...]
In the first and second post of this series I shared with you some basics about smart scan and gave some details about projection and restriction. The aim of this post is to cover the third basic technique: join filtering.
Join filtering is not something specific to the Exadata Storage Server. In fact, it is an [...]
We have demonstrated our database security vulnerability scanner product to a lot of our customers and partners customers over the last few months and the reactions and feedback have been amazing. The product has two major modes, which map to....[Read More]
Posted by Pete On 05/05/10 At 01:24 PM
Today I installed for the first time the patchset 10.2.0.5. While reading the README file, I noticed the following piece of information.
To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter:
_optimizer_native_full_outer_join =force
You can set this parameter for the system or for a specific session.
Besides [...]
Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= [...]
It was ten years ago that asktom was "born" (and five years and a month ago this blog was)...
The first question asked was about Oracle 7.3 on a Sun 5.5.1 machine - in early 2000 (right after we got by that year 2000 thing). Funny, the last time it was updated was.... just a little more than 3 months ago - it is still alive...
Man oh man - have things changed since. I stand at about 12,000 published Q&A's (about 40,000 in total - not all get published for various reasons...) I have 10 new ones in the queue (will get to them soon - have lots of travel planned for today and the week in fact)...
I've learned a lot in the last ten years - things have changed considerably.
Remember what a "big" server looked like in 2000? Remember what they cost? Think about how now you can buy stuff off of the shelf on your credit card that blows them away.
In 1997 - we were doing terabyte test to scales - a terabyte was such a big deal then that we would put out a press release. Now we have a database machine with 5tb of flash cache just to buffer part of the database.... A terabyte is nothing - my son would be disappointed in any laptop with less than that for storage...
It is only because things keep changing so fast, so much - that this stuff stays interesting. Imagine if it were still like 1993 (when I joined Oracle) 17 years later. It would be pretty boring...
Anyway, thanks for all of the great questions - looking forward to more...
In the first post of this series I shared with you some basics about smart scan and gave some details about projection. The aim of this post is to cover the second basic technique: restriction. Simply put, the aim of this technique is to offload to a cell the processing of predicates found in the [...]
Recent comments
1 year 46 weeks ago
2 years 6 weeks ago
2 years 10 weeks ago
2 years 11 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 5 weeks ago
3 years 34 weeks ago
4 years 19 weeks ago
4 years 19 weeks ago