I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.
It would have helped for sure. I also think that if you work in an environment where ANSI SQL is not the “standard” it is hard to adopt it as your main approach to writing queries.Comments Off | Filed in Oracle | Tags: join, sql
Apparently somewhere around the version 10 of sqlplus they changed the autotrace command to use DBMS_XPLAN.DISPLAY to show the plan of a query run with set autotrace on. But, I’ve never taken advantage of this feature because I’m using a 9.2 version of the Oracle client on my laptop.Comments Off | Filed in Oracle | Tags: sql
I will be presenting a free Webinar, brought to you by ODTUG, on Tuesday, July 17, 9:00 AM – 10:00 AM PDT. I will be talking about and demonstrating SQL and PL/SQL caching techniques that will considerably improve the performance of your queries and programs. The Webinar will cover the following five features:
Click here to register.
The webinar went very well. I thank all of you who attended and ODTUG for hosting it.
Click here to download a zip file containing the following files:
Now, at least I can get a copy of Oracle Linux for free (even if I was not working for Oracle) and I can/could use that on as many servers at home (or at my company if I worked elsewhere) for testing, development and production. I just go to http://edelivery.oracle.com/linux and download the version(s) I want and off I go.
Some of my servers are on the external network and I need to be current with security errata, but guess what, no problem, my servers are hooked up to http://public-yum.oracle.com which is open, free, and completely up to date, in a consistent, reliable way with any errata, security or bugfix. So I have nothing to worry about. Also, not because I am an employee. Anyone can.Comments Off | Filed in Oracle | Tags: linux
Pull up a chair and enjoy what turned out to be one of the highlight interviews of the 10th D: All Things Digital conference, which is essentially Ellison very, very unplugged:Comments Off | Filed in Oracle | Tags: video
Here are a few more use cases and resources about this little known feature:
Are you using this feature in your production environment?Comments Off | Filed in Oracle | Tags: reference
Excellent overview about the different ways you can setup networking in VirtualBox.Comments Off | Filed in Oracle | Tags: virtualbox, virtualization
1. Set based processing will likely be much faster than row based processing. Our experiment of processing 100K rows showed row based processing was 3700 times slower than set based processing. Not twice as slower or even 10 times slower… 3700 times slower!Comments Off | Filed in Oracle | Tags: pl/sql, sql
2. Compared to set based processing, row based processing times degrade much quicker than set based processing. That is, row based processing does not scale nearly as well as set based processing. We saw this in that the linear trend line for row based processing was 0.00259 compared to 0.00000 for set based processing.
Steven Feuerstein gives us a great and simple example of how to use bind variables to avoid concatenation of variable values into dynamic SQL strings and, at the same time, improve performance by order of magnitude:
When you use bind variables, you greatly simplify the task of writing the dynamic SQL string. You don’t have to write all that concatenating code and you don’t have to perform datatype conversions. The USING clause automatically performs native binding of the appropriate types.
He also notes:
You can bind only variable values. You can’t bind in the names of tables or columns, nor can you bind in parts of a SQL statement structure, such as the entire WHERE clause. In these cases, you must use concatenation.Comments Off | Filed in Oracle | Tags: performance, pl/sql