I copied the contents of DBA_TABLES into a table of my own called T1 and duplicated its contents repeatedly until it had over three million rows. Then I checked that every row had TABLE_LOCK set to ENABLED and that no rows had an owner called BLAH.
SQL> create table t1 as select * from dba_tables
2 /
Table created.
SQL> begin
2 for a in 1..11 loop
3 insert into t1 select * from t1;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit
2 /
Commit complete.
SQL> select count(*) from t1
2 /
COUNT(*)
----------
3217408
SQL> select count(*) from t1 where table_lock = 'ENABLED'
2 /
COUNT(*)
----------
3217408
SQL> select count(*) from t1 where owner = 'BLAH'
2 /
COUNT(*)
----------
0
Then I ran the first SELECT statement as follows. If the course notes were correct, the first condition should reject every row and the second condition should never be evaluated:
SQL> alter system flush shared_pool
2 /
System altered.
SQL> conn /
Connected.
SQL> select count(*) from t1
2 where owner = 'BLAH'
3 and table_lock = 'ENABLED'
4 /
COUNT(*)
----------
0
SQL> select a.value/100 "CPU Used"
2 from v$mystat a, v$sysstat b
3 where a.statistic# = b.statistic#
4 and name = 'CPU used by this session'
5 /
CPU Used
----------
13.83
Then I ran the second SELECT statement shown below. It was identical to the first but the conditions were swapped round. If the course notes were correct, the first
condition should accept each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times and got similar results each time. So far so good:
SQL> alter system flush shared_pool
2 /
System altered.
SQL> conn /
Connected.
SQL> select count(*) from t1
2 where table_lock = 'ENABLED'
3 and owner = 'BLAH'
4 /
COUNT(*)
----------
0
SQL> select a.value/100 "CPU Used"
2 from v$mystat a, v$sysstat b
3 where a.statistic# = b.statistic#
4 and name = 'CPU used by this session'
5 /
CPU Used
----------
14.27
The course notes also suggested that if you had conditions after a WHERE clause joined by OR,
you should put the test which was most likely to succeed first. This would
then save Oracle the bother of evaluating the subsequent condition(s). I decided to try this out too, using the table T1, which I created above.
I ran the third SELECT
statement like this. If the course notes were correct, the first
condition should accept every row and the second condition should never
be evaluated:
SQL> alter system flush shared_pool
2 /
System altered.
System altered.
SQL> conn /
Connected.
SQL> select count(*) from t1
2 where table_lock = 'ENABLED'
3 or owner = 'BLAH'
4 /
COUNT(*)
COUNT(*)
----------
3217408
SQL> select a.value/100 "CPU Used"
SQL> select a.value/100 "CPU Used"
2 from v$mystat a, v$sysstat b
3 where a.statistic# = b.statistic#
4 and name = 'CPU used by this session'
5 /
CPU Used
CPU Used
----------
14.17
SQL>
SQL>
Finally, I ran the fourth SELECT statement. It was identical to the third but the conditions were swapped round. If the course notes were correct, the first
condition should reject each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times as well and got similar results each time. I think this demonstrated that the course notes were correct:
SQL> alter system flush shared_pool
2 /
System altered.
SQL> conn /
Connected.
SQL> select count(*) from t1
2 where owner = 'BLAH'
3 or table_lock = 'ENABLED'
4 /
COUNT(*)
----------
3217408
SQL> select a.value/100 "CPU Used"
2 from v$mystat a, v$sysstat b
3 where a.statistic# = b.statistic#
4 and name = 'CPU used by this session'
5 /
CPU Used
----------
14.79
No comments:
Post a Comment