Llayland’s Food, Oracle, and Haskell

December 10, 2010

crazy rewrites

Filed under: Oracle — llayland @ 4:48 am

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

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: