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 184.108.40.206). 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.
The below link takes you to an absolutely fantastic interactive demonstration of the relative size of everything. Everything. Stop reading this and go look at it, when it finishes loading, move the blue blob at the bottom of the screen left and right.
The raw web link is:
The web page says scale_of_the_universe but it should be relative_scale_of_everything_in_the_universe. Did you go look at it? NO!?! If it’s because you have seen it before then fair enough – otherwise stop reading this stupid blog and Look At It! NOW! GO ON!!!
Yes, I do think it is good.
Yesterday I talked about some of the things to consider when adding space to a tablespace using auto-extend for datafiles. This of course isn’t the only way to add space to a tablespace. You could simply add more datafiles. Indeed nearly a decade ago (scary!) I wrote The final reason for multiple datafiles (to spread […]
Mission: Impossible – Ghost Protocol is OK for a Tom Cruise action vehicle. It is more or less the same as all the others in the franchise, which is not a bad thing. It just doesn’t bring anything new to the table. I knew it would be like this which is why it took me some time to work myself up to seeing it.
If you like the others go to see it. If you have not been won over to the franchise yet, I don’t think this one will do it.
Based on the excellent comments in the Quiz post, we have some clever cookies out there I guess the first thing to point out is that based in the basic scenario provided, the index shouldn’t ordinarily be continually growing in this fashion. Although the index values are monotonically increasing, the deletions are leaving behind fully emptied leaf blocks which [...]
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.