| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Myths
In article <3cecd5c9$0$238$ed9e5944_at_reading.news.pipex.net>, you said
(and I quote):
> > Perhaps I should try something similar on my 6.0.36 db (don't ask, at least > its read only) to see how things were in the bad old days!
Before you do, here is what I came up with in 8.0.6 ( condensed as much as possible so we both can finish reading in this lifetime!):
SQL> create table zot
2 (invno number(15) not null,
3 invval number(15,2) not null)
4 tablespace pstemp
5 storage (initial 1M next 1M);
Table created.
SQL> insert into zot values (1,100.50);
1 row created.
1 insert into zot select invno,invval+rownum
2* from zot
SQL> /
1 row created.
SQL> /
2 rows created.
SQL> /
4 rows created.
etc,etc.
. . .
. . .
1536
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'ZOT' (Cost=1 Card=1536)
Statistics
139 recursive calls
3 db block gets
27 consistent gets
0 physical reads
0 redo size
241 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Nuff data generation, let's do some werk:
SQL> create index lesszot on zot(invno,invval) compress;
Index created.
SQL> analyze table zot compute statistics;
Table analyzed.
SQL> select invno,count(*)
2 from zot
3 where invno between 30 and 40
4 group by invno;
INVNO COUNT(*)
---------- ----------
30 16
31 8
32 16
33 8
34 16
35 8
36 16
37 8
38 16
39 8
40 16
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=12 Bytes=1595
)
1 0 SORT (GROUP BY NOSORT) (Cost=1 Card=12 Bytes=1595)
2 1 INDEX (RANGE SCAN) OF 'LESSZOT' (NON-UNIQUE) (Cost=2 Car
d=145 Bytes=1595)
NOTE: get a load of that "Cost", it's relevant later!
Statistics
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
457 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
INVNO SUM(INVVAL)
---------- -----------
30 5548
31 3044
32 6700
33 860
34 1948
35 1052
36 2332
37 1280
38 2980
39 1664
40 3748
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=12 Bytes=1595
)
1 0 SORT (GROUP BY NOSORT) (Cost=1 Card=12 Bytes=1595)
2 1 INDEX (RANGE SCAN) OF 'LESSZOT' (NON-UNIQUE) (Cost=2 Car
d=145 Bytes=1595)
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
INVNO COUNT(*)
---------- ----------
30 16
31 8
32 16
33 8
34 16
35 8
36 16
37 8
38 16
39 8
40 16
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=12 Bytes=1595
)
1 0 SORT (GROUP BY) (Cost=5 Card=12 Bytes=1595)
2 1 INDEX (FULL SCAN) OF 'MOREZOT' (NON-UNIQUE) (Cost=1 Card
=145 Bytes=1595)
NOTE: see the difference in the "Cost" even though the actual consistent
gets difference is bugger all?
Not enough data to make a bigger diff, but the optimizer is smart nuff to
pick the diff in col sequence and call this one "heavier"? I wonder
how it does this without histograms...
Statistics
46 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
457 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed
INVNO SUM(INVVAL)
---------- -----------
30 5548
31 3044
32 6700
33 860
34 1948
35 1052
36 2332
37 1280
38 2980
39 1664
40 3748
11 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=12 Bytes=1595
)
1 0 SORT (GROUP BY) (Cost=5 Card=12 Bytes=1595)
2 1 INDEX (FULL SCAN) OF 'MOREZOT' (NON-UNIQUE) (Cost=1 Card
=145 Bytes=1595)
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed
NOTE: there is indeed increased I/O by using the more-to-less selective sequence of concatenation. Very slight at these volumes, guaranteed to make a significant difference in much larger volumes. Particularly if the SQL becomes proportionally more complex.
Also, note that I've used both sum(col) where all columns are in index as well as count(*) where the second col is not part of the index.
And I've used range scans, which IMHO are much more representative of what happens in a normal production shop. Where a range of "invoices" is usually selected to be worked upon: maybe the ones for the day, or accounting period.
Can you duplicate these results in 8i? Or anyone else game to do the same on 9i? I can only try 9i tomorrow at work.
-- Cheers Nuno Souto nsouto_at_optushome.com.au.nospamReceived on Thu May 23 2002 - 07:56:38 CDT
 |
 |