Several years ago I met Arjen Visser and Bertie Plaatsman from Dbvisit and they told me about their standby database product, which is a replacement for Data Guard. Now I don’t spend much time on non-Oracle products, but this one was interesting to me as it works on Standard Edition, unlike Data Guard which is an Enterprise Edition option. From that point onward I kept seeing Arjen and conferences and telling myself I really should take a look at the product.
Over last year I bumped into Arjen at a few conferences, along with some other members of the company (Eric, Mike and Vit). They are a cool group of people, so my interest in their products was ignited again. Finally, after several years of showing interest I tried out the standby product towards the end of last year, which resulted in the following article.
Chris Date is one of the founding fathers of relational databases. Having worked with Ted Codd at IBM during the time when relational databases were being defined gives Chris a perspective that most of us just don’t have. I’ve had the good fortune to hear him speak in the past (at the Hotsos Symposium) and thought I would do a quick post to highlight the fact that he is scheduled to speak in Dallas the week of Jan 30. Method-R is hosting the event in the Enkitec training facilities in Dallas. So maybe I’ll get to hang around with Chris and Cary that week – that would be cool! Anyway, there are actually 2 classes:
A few days ago I published an example of the optimizer failing to handle an updateable join view because it didn’t recognise that a particular type of aggregate subquery would guarantee key-preservation. Here’s another example where the human eye can see key-preservation, but the optimizer can’t (even in 18.104.22.168). As usual we’ll start with some sample data – in this case two tables since I want to update from one table to the other.
create table t1 ( id1 number, id2 number, val number, constraint t1_pk primary key (id1, id2) ); insert into t1 values (1,1,99); commit; create table t2 ( id1 number, id2 number, id3 number, val number, constraint t2_pk primary key (id1, id2, id3) ); insert into t2 values (1,1,1,200); insert into t2 values (1,1,2,200); commit;
How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.
Oracle clearly say,
ORDER BYclause to order rows returned by the statement. Without an
ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”
This also applies for the
GROUP BY clause, that haunted people during their 10g upgrades.
GROUP BYclause groups rows but does not guarantee the order of the result set. To order the groupings, use the
Both these statements can be found in the docs for the SELECT statements.
Here’s a follow-up to a post I did some time ago about estimating the size of an index before you create it. The note describes dbms_stats.create_index_cost() procedure, and how it depends on the results of a call to explain plan. A recent question on the OTN database forum highlighted a bug in explain plan, however, which I can demonstrate very easily. I’ll start with a small amount of data to demonstrate the basic content that is used to calculate the index cost.
A recent comment on a note I wrote some time ago about faking histograms asked about the calculations of selectivity in the latest versions of Oracle. As I read the question, I realised that I had originally supplied a formula for calculating cardinality, rather than selectivity, so I thought I’d supply a proper example.
We’ll start with a script to create some data and stats – and I’m going to start with a script I wrote in Jan 2001 (which is why it happens to use the analyze command rather than dbms_stats.gather_table_stats, even though this example comes from an instance of 22.214.171.124).
New functions introduced in SQL Server 2008 Release 2 let you assign rankings to rows in a result set. These functions are ROW_NUMBER, RANK, and DENSE_RANK. Look to them anytime you are faced with a business question involving words or phrases such as "topmost" or "bottommost", "top N" or "bottom N", or that is otherwise answerable by ranking the rows in a result set according to some criteria that you can apply to one or more columns of data.
Getting tired of typing and setting your ORACLE_HOME, ORACLE_SID and troubleshooting your . oraenv ?
going/connecting to different client sites with different platforms and server environments sometimes it is frustrating just how long it takes to get a proper SQL*Plus environment
So I always make use of this script
and for every server that I access I just have to do three bits of typing:
Here’s a funny little optimizer bug – though one that seems to have been fixed by at least 10.2.0.3. It showed up earlier on today in a thread on the OTN database forum. We’ll start (in 126.96.36.199) with a little table and two indexes – one normal, the other descending.
Here’s a simple update statement that identifies a few rows in a table then updates a column where a matching value can be derived from another table – it’s an example of an update by correlated subquery:
update t1 set small_vc = ( select max(small_vc) from t2 where t2.id = t1.id ) where mod(id,100) = 0 and exists ( select null from t2 where t2.id = t1.id ) ;