Oracleにはどうしても我慢できない

NO IMAGE

Oracleの場合 inner join と書くとindexが効かないと言われたので検証してみました

■テーブル

mysql> SHOW INDEX FROM dtb_products;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dtb_products |          0 | PRIMARY  |            1 | product_id  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM dtb_products_class;
+--------------------+------------+-----------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name                          | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dtb_products_class |          0 | PRIMARY                           |            1 | product_class_id | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| dtb_products_class |          1 | dtb_products_class_product_id_key |            1 | product_id       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------+------------+-----------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

■MySQLの場合

mysql> explain
    -> select
    ->     dtb_products.product_id
    -> from
    ->     dtb_products
    ->     inner join dtb_products_class on dtb_products.product_id = dtb_products_class.product_id
    -> where
    ->     dtb_products.product_id = 1;
+----+-------------+--------------------+-------+-----------------------------------+-----------------------------------+---------+-----------------------------------+------+-------------+
| id | select_type | table              | type  | possible_keys                     | key                               | key_len | ref                               | rows | Extra       |
+----+-------------+--------------------+-------+-----------------------------------+-----------------------------------+---------+-----------------------------------+------+-------------+
|  1 | SIMPLE      | dtb_products       | index | PRIMARY                           | PRIMARY                           | 4       | NULL                              |    2 | Using index |
|  1 | SIMPLE      | dtb_products_class | ref   | dtb_products_class_product_id_key | dtb_products_class_product_id_key | 4       | eccube211.dtb_products.product_id |    2 | Using index |
+----+-------------+--------------------+-------+-----------------------------------+-----------------------------------+---------+-----------------------------------+------+-------------+
2 rows in set (0.00 sec)

indexが効いています

■Oracleの場合

set autotrace on
select 
    dtb_products.product_id
from
    dtb_products
    inner join dtb_products_class on dtb_products.product_id = dtb_products_class.product_id

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |   452 |  3616 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |                      |   452 |  3616 |     2   (0)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| IX_PRODUCTS_CLASS_01 |   452 |  1808 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN   | PK_PRODUCTS          |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

ん?効いてる・・・よね?
こう書かないと効かないよと言われたSQLも試してみる

set autotrace on
select 
    dtb_products.product_id
from
    dtb_products,
    dtb_products_class
where
    dtb_products.product_id = dtb_products_class.product_id

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |   452 |  3616 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |                      |   452 |  3616 |     2   (0)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| IX_PRODUCTS_CLASS_01 |   452 |  1808 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN   | PK_PRODUCTS          |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

全く一緒の結果ですね・・・
つまり、ちゃんと自分で試してみないとだめってことでしょうか

ホッテントリメーカーにお願いしたらあまりにもいいタイトルだったのでそのまま書いてみただけなのでしたw