Hi, All
I made 3rd puzzle for sql tuning Beginner.(Not for SQL Beginner!)
Which is better performance? NO, This puzzle is just thinking tranining.
If you have interest, try this!
※Recommended Version : above 10g
drop table t1 purge;
drop table t2 purge;
create table t1
(a number primary key);
create table t2
(b number primary key);
insert into t1
select level a1
from dual
connect by level<=3;
insert into t2
select level+2
from dual
connect by level<=3;
commit;
Select1
set autot on stat
select count(*)
from (
select a
from t1
union
select b
from t2
);
COUNT(*)
-----------------
5
Statistics
------------------
・
・
・
1 sorts (memory)
0 sorts (disk)
1 rows processed
Select2
set autot on stat
select count(*)
・・・
;
COUNT(*)
----------------
5
Statistics
-------------------
・
・
・
0 sorts (memory)
0 sorts (disk)
1 rows processed
Puzzle3
You can make Select2 to be No sorts and to be same query result.
0 sorts (memory)
0 sorts (disk)
Requirements ;
・Always Select1 and Select2 result to be same.
If Select1's result is 100, Select2's reslut should to be 100.
・You can use Optimizer Hint.
Puzzle4
I added one more requirement. Try this!
Select2
alter session set OPTIMIZER_FEATURES_ENABLE='10.1.0';
set autot on stat
select count(*)
・・・
;
COUNT(*)
----------------
5
Statistics
-------------------
・
・
・
0 sorts (memory)
0 sorts (disk)
1 rows processed
Regards eqon