Another case where the order of the tables within the FROM clause has an impact.
Two tables, with the same column names (and data types).
> desc t2 Name Null? Type --------- -------- --------------- 1 ID NUMBER 2 VAL VARCHAR2(10)
> desc t1 Name Null? Type --------- -------- --------------- 1 ID NUMBER 2 VAL VARCHAR2(10)
Put together a SELECT * query. Now, which table has the value 'Blue' and which is 'Red'. > select * from (select * from t2 join t1 on t1.id = t2.id); QCSJ_C000000000400000 QCSJ_C0000 QCSJ_C000000000400001 QCSJ_C0000 --------------------- ---------- --------------------- ---------- 1.00 Blue 1.00 Red
It appears to be the order of the tables in the where clause (so the first two columns are from T2 and the latter from T1). Sensibly, that happens irrespective of the path the optimizer takes, so a change in plans won't suddenly change the results.
Yes, it is another reason NOT to use a SELECT *.
That said, do you notice the automatic column aliasing ?
SQL*Plus has truncated some of the column headers, and the actual column names (and datatypes) are :
It only happens with the ANSI join, with the old-style join throwing an error.
select * from (select * from t2 , t1 where t1.id = t2.id)
ERROR at line 1:
ORA-00918: column ambiguously defined
FAILURE IS NOT AN OPTION…IT'S INCLUDED AS STANDARD
Failsafe does NOT mean that the item cannot fail. What it does mean is that, in the event of a failure, it won't go BOOM. As far as databases go, it means that, when a statement fails, any work done by that statement is undone. When a transaction fails, any work done by that transaction is undone. When a recovery is done, it leaves the database in a consistent state (ie no transactions partly done).
Oracle rates pretty highly on the failsafe scale. When a SQL statement, or a top-level anonymous PL/SQL block, fails it will rollback all the TRANSACTIONAL data changes made. It won't reset non-transactional items, such as PL/SQL package level variables or any stuff done outside the database (OS files, web service calls, FTP transfers etc). It doesn't work miracles.
Failures are a fact of life. The "not an option" declaration can only be valid at a system level, where multiple components are present to cover for the failure of individual components. The only way to mitigate against failure is to anticipate and understand that anything and everything can fail, and to have put in place steps to remediate the situation.
The power can fail and the server crash, so we have the redo logs. The media can fail, so we can backup the data files and multiplex the redo logs. Servers fail, so we have RAC and DataGuard.
Your software will fail. The question you've got to ask yourself is, will it fail safe ?
Last week, I attended the Developer Day in Sydney.
One of the presentations was about Apex, and I was hoping for more information on Apex 4.0.I was disappointed as there wasn't a lot of detail, and nothing new to me. While I've had a Workspace in the EAs, I haven't really had a chance to use it. I'll have to wait for an installable version, so I can play with it on the train home.
However one of the other talks was about the TimesTen / In-Memory Database Cache. While I'd heard of it, it hasn't had the same level of spruiking as Exadata (or Iron Man, come to that ). Since it had previously escaped my attention, it was the presentation from which I gained the most.
Firstly, it is an Enterprise Edition feature. That's sort of okay, since it is aimed at very high throughput environments. I know not all of those are awash with money, but I don't expect Oracle to give everything away for free and it is reasonable to equate demanding requirements with a preparedness to pay for them.
Secondly, it gives Oracle scale-out capability. For most Oracle techies, scaling means either a bigger database box (scaling up) or RAC. All those mid-tier happy developers just want the ability to throw boxes at a problem, and RAC isn't that simple. TimesTen would fit their mindset better.Think of it as half proxy/half cache. Most importantly, the developers treat a TimesTen node as the database, and Oracle will keep all the tricky stuff hidden from them.
In some ways it should be simpler than RAC, as the "hardcopy" database (the one with real disks) would treat the cache as a client rather than as an integrated component. That means that it would be easier to throw up TimesTen caches as and when required (very cloud-like).
Multiple TimesTen caches can duplicate data or shard it (eg one for images, one for audio).A duplicate would be good for reducing latency issues by having a cache co-located on an app-server. That is "on" not just "near". Apparently the removal of the network is part of the performance boost.
Sharding could be effective in 'boosting' memory. If you've got a machine maxxed out on 32 GB, then having 20 Gb of 'Northern' data in one shard and 20 Gb of 'Southern' data in another means the hardcopy database can offload the frequently queried read-only stuff to the caches.
It isn't just read-only though. There'scapability for the data to be written, either directly to the hardcopy database, or with 'batching' done by the cache for improved performance.
Of course, I was just sitting through the sales pitch. I've had a flick through the manuals too. SQL and PL/SQL both work in the cache layer, though with some bits missing. Still all the general stuff that you use for applications is there. I'm going to have to dig around and see what real-life experiences have had to say about it. And see if I can throw up an instance (though I suspect VirtualBox on a net book might be somewhat challenging).
In some ways there is an overlap with RAC. There would be a set of problems where either additional RAC nodes or TimesTen caches would be a valid solution. There's also definitely issues where the cache would benefit where RAC would not (eg co-location with an app server).
[PS. The whole TimesTen / In Memory Database Cache terminology is a bit clunky. Oracle on speed or CrackOracle might have a bit more life in it. ]
There was a further talk on the GoldenGate product also acquired by Oracle a while back. More on that later….
Today I'm talking uncertainty. Not the "I don't know" uncertainty, but the "If I look at it then I get a different answer than if I don't look at it" type. And that becomes important when trying to put together automated tests which work by looking at things.
Here's a simple table and a procedure to insert into it.
create table test_1 (val number(2,0));
create or replace procedure proc_ins (p_val in number) is begin insert into test_1 (val) values (p_val); insert into test_1 (val) values (p_val * 100); end proc_ins; /
What is the state of test_1 after I execute proc_ins with a value of 5 ?
(Assuming no other session activity, hidden triggers, magic doves)
The answer is, it depends.
call proc_ins(5); select * from test_1;
The first insert into test_1 (which would have succeeded) was rolled back. This is called a statement level rollback. The documentation states that "An anonymous block is a SQL statement." and that an SQL statement that causes an error during execution will execute a statement level rollback and "The effect of the rollback is as if the statement had never been run."
An automated test
So what happens if I want to create an automated test to confirm the results.
declare e_too_big exception; PRAGMA EXCEPTION_INIT(e_too_big, -1438); begin proc_ins(1); exception when e_too_big then for c_rec in (select val from test_1) loop dbms_output.put_line(c_rec.val); end loop; end; /
Because the statement (the anonymous block) now includes the exception handler, the statement itself doesn't fail and the first insert is still there. By testing for the exception, I seem to change the behaviour. I say "seem" because the statement rollback is not explicitly part of the code I am testing, but is an artifact of the way the statement is called.
If proc_ins were to be part of a larger PL/SQL program, the tested behaviour would represent the actual run time behavior. But if it is to be called as a top level procedure, for example directly from a client application, the tested behaviour would be inaccurate.
Changing the results
I can change the unit test so that it represents a 'independent statement' rather than 'embedded call' using dynamic SQL. A dynamic SQL call will set a new level of SQL scope.
truncate table test_1;
declare e_too_big exception; PRAGMA EXCEPTION_INIT(e_too_big, -1438); begin execute immediate 'call proc_ins(2)'; exception when e_too_big then for c_rec in (select val from test_1) loop dbms_output.put_line(c_rec.val); end loop; end; /
Within the scope of the SQL statement (the CALL) in which the exception occurs, there is no exception handler. The SQL statement is rolled back, as if it never happened. When we re-enter the outer scope, the exception is handled, but the statement rollback has already happened.
It doesn't make a difference whether we use the CALL statement (which is SQL), or a BEGIN...END; PL/SQL construction.
truncate table test_1; declare e_too_big exception; PRAGMA EXCEPTION_INIT(e_too_big, -1438); begin execute immediate 'begin proc_ins(3); end;'; exception when e_too_big then for c_rec in (select val from test_1) loop dbms_output.put_line(c_rec.val); end loop; end; /
I will own up and say this revisits a post a few months back but I felt the automated test aspect of it needed an emphahsis. Also I was prompted by the PL/SQL Challenge of May 13th.
The answer states that "Exceptions do not roll back DML statements unless the exception goes unhandled to the host environment, in which case most such environments (like SQL*Plus) will automatically perform a rollback in your session."
I didn't like the statetment about host environments as it is the database engine which performs the statement-level rollback whenever an SQL call raises an exception. The host can do whatever it likes (though it is generally safer to rollback when you get an exception you don't expect).
It depends on the order of the tables in the FROM clause....sometimes.
Okay, it's a trick question. the real one is "How many rows does the CBO estimate when querying a view over a DB link ?"
Lets see it in action.
create table small as select level id, 'small' val from dual connect by level <= 10; create table large as select level id, 'large' val from dual connect by level <= 10000; create or replace view small_large as select s.id small_id, l.id large_id, s.val small_val, l.val large_val from small s, large l where s.id = l.id; create or replace view large_small as select s.id small_id, l.id large_id, s.val small_val, l.val large_val from large l, small s where s.id = l.id;
Distributed select with local and remote objects
The only difference is the join order in the view definitions and that difference only comes into play with a distributed query over the DB link.
Anyone with a eidetic memory may realise I'm just reprising a post from my blog a few years back. [That was one of my first - I haven't been going quite as long as Tom or Doug.] Really I just wanted to see if anything had changed in the past couple of database releases. It hadn't, and 11gr2 does the same thing.
To take it one step further, I tried a trace. It isn't easy picking trhough the recursive SQL, but there was one query that looked for object dependencies for the view. It queried sys.dependency$ with an order by on a column order# (which isn't exposed in the dba_dependecies view). As long as the dependency is in the FROM clause (and I'm still working on how it determines that) it seems the entry with the highest order# is the one used to determine the cardinality used by the optimizer.
So, sometimes, the order of tables in the FROM clause is important. But not very often.