Jonathan Lewis’s posts on index joins inspired me to run a few test cases of my own. I thought it was pretty neat that the optimizer could split a single table query into a 6 way self join. It really blew my mind when I found it could do the reverse. I also found it quite cute that it does the opposite of what I tell it too. That’s just how stuff works for me.
This was run on a fresh install of the developer vm in VirtualBox:
Edit – I really need to learn to preview. Here is the corrected script and output
set echo on
set linesize 200
drop table t1;
create table t1 (n1,n2,n3,n4,n5,n6,filler)
nocache
as
select trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
lpad(rownum, 1000, '0')
from dual connect by level <= 100000;
create bitmap index t1_n1 on t1(n1);
create bitmap index t1_n2 on t1(n2);
create bitmap index t1_n3 on t1(n3);
create bitmap index t1_n4 on t1(n4);
create bitmap index t1_n5 on t1(n5);
create bitmap index t1_n6 on t1(n6);
call dbms_stats.gather_table_stats('SCOTT','T1');
set timing on;
set autotrace traceonly;
set arraysize 5000;
select n1,n2,n3,n4,n5,n6 from t1;
select a.n1, b.n2, c.n3, d.n4, e.n5, f.n6
from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f
where a.rowid = all(b.rowid, c.rowid, d.rowid, e.rowid, f.rowid);
SQL> select n1,n2,n3,n4,n5,n6 from t1;
100000 rows selected.
Elapsed: 00:00:01.65
Execution Plan
———————————————————-
Plan hash value: 362797529
——————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT | | 100K| 2343K| 2018 (1)| 00:00:25 |
| 1 | VIEW | index$_join$_001 | 100K| 2343K| 2018 (1)| 00:00:25 |
|* 2 | HASH JOIN | | | | | |
|* 3 | HASH JOIN | | | | | |
|* 4 | HASH JOIN | | | | | |
|* 5 | HASH JOIN | | | | | |
|* 6 | HASH JOIN | | | | | |
| 7 | BITMAP CONVERSION TO ROWIDS| | 100K| 2343K| 11 (0)| 00:00:01 |
| 8 | BITMAP INDEX FULL SCAN | T1_N1 | | | | |
| 9 | BITMAP CONVERSION TO ROWIDS| | 100K| 2343K| 11 (0)| 00:00:01 |
| 10 | BITMAP INDEX FULL SCAN | T1_N2 | | | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | 100K| 2343K| 11 (0)| 00:00:01 |
| 12 | BITMAP INDEX FULL SCAN | T1_N3 | | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | 100K| 2343K| 11 (0)| 00:00:01 |
| 14 | BITMAP INDEX FULL SCAN | T1_N4 | | | | |
| 15 | BITMAP CONVERSION TO ROWIDS | | 100K| 2343K| 11 (0)| 00:00:01 |
| 16 | BITMAP INDEX FULL SCAN | T1_N5 | | | | |
| 17 | BITMAP CONVERSION TO ROWIDS | | 100K| 2343K| 11 (0)| 00:00:01 |
| 18 | BITMAP INDEX FULL SCAN | T1_N6 | | | | |
——————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(ROWID=ROWID)
3 – access(ROWID=ROWID)
4 – access(ROWID=ROWID)
5 – access(ROWID=ROWID)
6 – access(ROWID=ROWID)
Statistics
———————————————————-
1 recursive calls
0 db block gets
86 consistent gets
0 physical reads
0 redo size
526501 bytes sent via SQL*Net to client
628 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>
SQL> select a.n1, b.n2, c.n3, d.n4, e.n5, f.n6
2 from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f
3 where a.rowid = all(b.rowid, c.rowid, d.rowid, e.rowid, f.rowid);
100000 rows selected.
Elapsed: 00:00:07.78
Execution Plan
———————————————————-
Plan hash value: 3617692013
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100K| 3515K| 3919 (1)| 00:00:48 |
| 1 | TABLE ACCESS FULL| T1 | 100K| 3515K| 3919 (1)| 00:00:48 |
————————————————————————–
Statistics
———————————————————-
1 recursive calls
0 db block gets
14307 consistent gets
14286 physical reads
0 redo size
103737103 bytes sent via SQL*Net to client
628 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
