本文通过在Oracle 19c中创建主键和主外键约束，探讨索引对TPC-H查询性能的影响。在创建索引并查看优化后的执行计划后，发现性能显著提升，特别是在执行'Q16.TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)'时，明显感受到速度加快。后续计划将导入更大规模数据进行进一步分析。
「续基于Oracle 19c 的TPCH性能分析（三）查看执行计划（Q12 - Q22）」
feihongInk：基于Oracle 19c 的TPCH性能分析（三）查看执行计划（Q12 - Q22）zhuanlan.zhihu.com
5. 创建主键及其各种主外键约束条件，重新查询Oracle优化后的执行计划,看看与前面的没有数据时的执行计划是否相同。另外索引是性能优化的一种有效方法，结合上面的查询计划，看看能否通过索引优化TPC-H查询的性能
创建索引
修改索引文件dss.ri
-- Sccsid: @(#)dss.ri 2.1.8.1
-- TPCH Benchmark Version 8.0
--CONNECT TO TPCH;
--ALTER TABLE TPCH.REGION DROP PRIMARY KEY;
--ALTER TABLE TPCH.NATION DROP PRIMARY KEY;
--ALTER TABLE TPCH.PART DROP PRIMARY KEY;
--ALTER TABLE TPCH.SUPPLIER DROP PRIMARY KEY;
--ALTER TABLE TPCH.PARTSUPP DROP PRIMARY KEY;
--ALTER TABLE TPCH.ORDERS DROP PRIMARY KEY;
--ALTER TABLE TPCH.LINEITEM DROP PRIMARY KEY;
--ALTER TABLE TPCH.CUSTOMER DROP PRIMARY KEY;
-- For table REGION
ALTER TABLE TPCH.REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE TPCH.NATION
ADD PRIMARY KEY (N_NATIONKEY);
--ALTER TABLE TPCH.NATION
--ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCH.NATION;
ALTER TABLE TPCH.NATION ADD CONSTRAINT NATION_FK1 FOREIGN KEY (N_REGIONKEY) references TPCH.REGION(R_REGIONKEY);
COMMIT WORK;
-- For table PART
ALTER TABLE TPCH.PART
ADD PRIMARY KEY (P_PARTKEY);
COMMIT WORK;
-- For table SUPPLIER
ALTER TABLE TPCH.SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
--ALTER TABLE TPCH.SUPPLIER
--ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCH.NATION;
ALTER TABLE TPCH.SUPPLIER ADD CONSTRAINT SUPPLIER_FK1 FOREIGN KEY (S_NATIONKEY) references TPCH.NATION(N_NATIONKEY);
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE TPCH.PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
COMMIT WORK;
-- For table CUSTOMER
ALTER TABLE TPCH.CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
--ALTER TABLE TPCH.CUSTOMER
--ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCH.NATION;
ALTER TABLE TPCH.CUSTOMER ADD CONSTRAINT CUSTOMER_FK1 FOREIGN KEY(C_NATIONKEY) REFERENCES TPCH.NATION(N_NATIONKEY);
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE TPCH.LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
COMMIT WORK;
-- For table ORDERS
ALTER TABLE TPCH.ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
COMMIT WORK;
-- For table PARTSUPP
--ALTER TABLE TPCH.PARTSUPP
--ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCH.SUPPLIER;
ALTER TABLE TPCH.PARTSUPP ADD CONSTRAINT PARTSUPP_FK1 FOREIGN KEY(PS_SUPPKEY) REFERENCES TPCH.SUPPLIER(S_SUPPKEY);
COMMIT WORK;
--ALTER TABLE TPCH.PARTSUPP
--ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCH.PART;
ALTER TABLE TPCH.PARTSUPP ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY(PS_PARTKEY) REFERENCES TPCH.PART(P_PARTKEY);
COMMIT WORK;
-- For table ORDERS
--ALTER TABLE TPCH.ORDERS
--ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCH.CUSTOMER;
ALTER TABLE TPCH.ORDERS ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY(O_CUSTKEY) REFERENCES TPCH.CUSTOMER(C_CUSTKEY);
COMMIT WORK;
-- For table LINEITEM
--ALTER TABLE TPCH.LINEITEM
--ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references TPCH.ORDERS;
ALTER TABLE TPCH.LINEITEM ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) REFERENCES TPCH.ORDERS(O_ORDERKEY);
COMMIT WORK;
--ALTER TABLE TPCH.LINEITEM
--ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references TPCH.PARTSUPP;
ALTER TABLE TPCH.LINEITEM ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY(L_PARTKEY,L_SUPPKEY) REFERENCES TPCH.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
COMMIT WORK;查看优化后的执行计划
选取“Q16.TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)”查看执行计划，能够看到在第9步中使用了索引检索，而且在执行过程中能够明显感觉到速度的提升：
执行计划
----------------------------------------------------------
Plan hash value: 2894800762
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 15000 | 717K| | 288
1 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 15000 | 717K| 1648K| 288
1 (2)| 00:00:01 |
| 2 | HASH GROUP BY | | 15000 | 717K| 1648K| 288
1 (2)| 00:00:01 |
| 3 | VIEW | VM_NWVW_1 | 25957 | 1242K| | 241
6 (2)| 00:00:01 |
| 4 | HASH GROUP BY | | 25957 | 2991K| 3312K| 241
6 (2)| 00:00:01 |
|* 5 | HASH JOIN RIGHT ANTI | | 25957 | 2991K| | 172
5 (2)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | SUPPLIER | 500 | 34000 | | 6
8 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 27324 | 1334K| | 165
7 (2)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | PART | 6898 | 276K| | 106
9 (2)| 00:00:01 |
| 9 | INDEX FAST FULL SCAN| SYS_C0016642 | 800K| 7031K| | 58
1 (2)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PS_SUPPKEY"="S_SUPPKEY")
6 - filter("S_COMMENT" LIKE '%Customer%Complaints%')
7 - access("P_PARTKEY"="PS_PARTKEY")
8 - filter(("P_SIZE"=56 OR "P_SIZE"=63 OR "P_SIZE"=64 OR "P_SIZE"=75 OR "P_SI
ZE"=77 OR
"P_SIZE"=83 OR "P_SIZE"=91 OR "P_SIZE"=93) AND "P_TYPE" NOT LIKE '
MEDIUM POLISHED%' AND
"P_BRAND"<>'Brand#41')
统计信息
----------------------------------------------------------
2 recursive calls
0 db block gets
4064 consistent gets
0 physical reads
0 redo size
586 bytes sent via SQL*Net to client
822 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed写在最后
后续可以导入10G的数据继续查看执行计划：
- 没有创建索引的执行计划
- 创建索引的执行计划
- 区分添加索引后的执行计划情况。
相关检索
基于Oracle 19c 的TPCH性能分析（一）数据导入
feihongInk：基于Oracle 19c 的TPCH性能分析（一）数据导入zhuanlan.zhihu.com
基于Oracle 19c 的TPCH性能分析（二）查看执行计划（Q1 - Q11）
feihongInk：基于Oracle 19c 的TPCH性能分析（二）查看执行计划（Q1 - Q11）zhuanlan.zhihu.com
基于Oracle 19c 的TPCH性能分析（三）查看执行计划（Q12 - Q22）
feihongInk：基于Oracle 19c 的TPCH性能分析（三）查看执行计划（Q12 - Q22）zhuanlan.zhihu.com
扫一扫
364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
