PL/SQL Challenge 每日一题：2016-10-19 索引设计

kelis2004 · 发表于 2016-10-27 10:21

感觉newkid大神的答案分析没有考虑到原先的主键唯一索引-- primary key ( olympic_year, country_name )
就A选项来讲，此处再对年份列单独建索引就是多余的了，因为olympic_year列检索的时候主键索引本身会用到

〇〇 · 发表于 2016-10-27 14:22

wo.sql (26.88 KB, 下载次数: 17)

〇〇 · 发表于 2016-10-27 15:04

SQL> set lines 120
SQL> /

COUNTRY_NAME
--------------------------------------------------------------------------------------------------------------
United States of America

执行计划
----------------------------------------------------------
Plan hash value: 458564893

-----------------------------------------------------------------------------------------
| Id  | Operation       | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |                   |    1 | 118 |    3 (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PLCH_OLYMPIC_MEDALS |    1 | 118 |    3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OLYMPIC_YEAR"='Vancouver 2010' AND
            "GOLD_MEDALS"+"SILVER_MEDALS"+"BRONZE_MEDALS">30)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      0  physical reads
      0  redo size
      439  bytes sent via SQL*Net to client
      384  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> create index plch_olme_i on plch_olympic_medals (
  2    olympic_year, gold_medals + silver_medals + bronze_medals
  3 );

索引已创建。

SQL> select /*+ index ( om plch_olme_i ) */country_name
  2 from plch_olympic_medals om
  3 where  olympic_year = 'Vancouver 2010'
  4 and ( gold_medals + silver_medals + bronze_medals ) > 30;

COUNTRY_NAME
--------------------------------------------------------------------------------------------------------------
United States of America

执行计划
----------------------------------------------------------
Plan hash value: 4156525829

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                   |    1 | 118 |    3 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| PLCH_OLYMPIC_MEDALS |    1 | 118 |    3 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | PLCH_OLME_I       | 26 |    |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OLYMPIC_YEAR"='Vancouver 2010' AND
            "GOLD_MEDALS"+"SILVER_MEDALS"+"BRONZE_MEDALS">30 AND
            "GOLD_MEDALS"+"SILVER_MEDALS"+"BRONZE_MEDALS" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      42  recursive calls
      0  db block gets
      17  consistent gets
      1  physical reads
      0  redo size
      439  bytes sent via SQL*Net to client
      384  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

〇〇 · 发表于 2016-10-27 16:27

wo1.sql (76.4 KB, 下载次数: 20)

〇〇 · 发表于 2016-10-27 16:28

〇〇发表于 2016-10-27 16:27

SQL> select /*+ index ( om plch_olme_i ) */country_name
  2 from plch_olympic_medals om
  3 where  olympic_year = 'Sydney 2000'
  4 and ( gold_medals + silver_medals + bronze_medals ) > 30;

COUNTRY_NAME
-------------------------------------------------------------------------------------------------------------
Italy
France
People's Republic of China
Germany
Australia
Russian Federation
United States of America

已选择7行。

执行计划
----------------------------------------------------------
Plan hash value: 4156525829

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                   |    7 | 826 |    3 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| PLCH_OLYMPIC_MEDALS |    7 | 826 |    3 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | PLCH_OLME_I       | 80 |    |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OLYMPIC_YEAR"='Sydney 2000' AND
            "GOLD_MEDALS"+"SILVER_MEDALS"+"BRONZE_MEDALS">30 AND
            "GOLD_MEDALS"+"SILVER_MEDALS"+"BRONZE_MEDALS" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      19  recursive calls
      0  db block gets
      21  consistent gets
      0  physical reads
      0  redo size
      577  bytes sent via SQL*Net to client
      384  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      7  rows processed