SQL> select count(*) from country;
COUNT(*)
----------
239
mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
| 239 |
+----------+
1 row in set (0.00 sec)
OK, that’s cool, that’s what I expected. Let’s do a simple aggregate function:
SQL> select continent, sum(population) TotalPop
2 from country
3 group by continent
4 order by continent
5 ;
CONTINENT TOTALPOP
------------------------------ ----------
Africa 784475000
Antarctica 0
Asia 3705025700
Europe 730074600
North America 482993000
Oceania 30401150
South America 345780000
7 rows selected.
OK, that’s what we expected. Let’s try the same query in MySQL:
mysql> select continent, sum(population) TotalPop
-> from country
-> group by continent
-> order by continent
-> ;
+---------------+------------+
| continent | TotalPop |
+---------------+------------+
| Asia | 3705025700 |
| Europe | 730074600 |
| North America | 482993000 |
| Africa | 784475000 |
| Oceania | 30401150 |
| Antarctica | 0 |
| South America | 345780000 |
+---------------+------------+
7 rows in set (0.00 sec)
Hmm, that’s interesting. Maybe it has something to do with default sorting order or something. I’ll have to ask Tobias (my Instructor) about that in the morning. Let’s try sorting by a number field:
SQL> select continent, sum(population) totalpop
2 from country
3 group by continent
4 order by totalpop desc
5 ;
CONTINENT TOTALPOP
------------------------------ ----------
Asia 3705025700
Africa 784475000
Europe 730074600
North America 482993000
South America 345780000
Oceania 30401150
Antarctica 0
7 rows selected.
mysql> select continent, sum(population) totalpop
-> from country
-> group by continent
-> order by totalpop desc
-> ;
+---------------+------------+
| continent | totalpop |
+---------------+------------+
| Asia | 3705025700 |
| Africa | 784475000 |
| Europe | 730074600 |
| North America | 482993000 |
| South America | 345780000 |
| Oceania | 30401150 |
| Antarctica | 0 |
+---------------+------------+
7 rows in set (0.02 sec)
Yeah, that’s we expected. Let’s try another query:
mysql> select continent, population totalpop
-> from country
-> group by continent
-> order by totalpop desc
-> ;
+---------------+----------+
| continent | totalpop |
+---------------+----------+
| South America | 37032000 |
| Africa | 31471000 |
| Asia | 22720000 |
| Europe | 15864000 |
| North America | 217000 |
| Oceania | 68000 |
| Antarctica | 0 |
+---------------+----------+
7 rows in set (0.00 sec)
Hmm, what’s weird about this query (assuming the data is correct)? Let’s try it in Oracle:
SQL> select continent, population totalpop
2 from country
3 group by continent
4 order by totalpop desc
5 ;
select continent, population totalpop
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
That’s right, our old friend didn’t even run the query for us.
Another interesting tidbit was with the DISTINCT Operator. In Oracle, the optimizer may determine DISTINCT values by sorting, whereas MySQL can use one of two algorithms. For example:
SQL> select distinct continent from country;
CONTINENT
------------------------------
Africa
Antarctica
Asia
Europe
North America
Oceania
South America
7 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=239 Bytes=7
648)
1 0 SORT (UNIQUE) (Cost=6 Card=239 Bytes=7648)
2 1 TABLE ACCESS (FULL) OF 'COUNTRY' (TABLE) (Cost=5 Card=23
9 Bytes=7648)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
665 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7 rows processed
mysql> select distinct continent from country;
+---------------+
| continent |
+---------------+
| Asia |
| Europe |
| North America |
| Africa |
| Oceania |
| South America |
| Antarctica |
+---------------+
7 rows in set (0.00 sec)
BTW, another participant in the class suggested that Oracle always sends back the results of DISTINCT in sorted order. I didn’t think so, but wasn’t able to disprove it. Maybe I learned something new about Oracle today.
Edit: 09/19/2005 23:33 -
Tom correctly points out in the comments that Oracle doesn't always use a sort to determine duplicates. If I had that looked on metalink at doc 655027.999 or 249919.999 I would have confirmed my suspicians about always using ORDER BY. Bang head on desk, Bang head on desk, ...
