数据库设计 – 在数据库中存储总线路径
发布时间：2021-01-14 00:35:09 所属栏目：MsSql教程 来源：网络整理
导读：我做了一些研究,发现我应该将路线存储为一系列停靠点.就像是： Start - Stop A - Stop B - Stop C - End 我创建了三个表： 路线 停止 RouteStops …其中RouteStops是一个联结表. 我有类似的东西： 路线 +---------+| routeId |+---------+| 1 |+---------+| 2
|
现在是有趣的部分,让我们在路由表中添加一个列名. ALTER TABLE ROUTES
ADD (name VARCHAR2(50));
update routes set name='Old Town' where routeId=1;
update routes set name='North County' where routeId=2;
commit;
explain plan for select r.name as routeName,s.Name as stationName
from routeStations rs
join
routes r
on r.routeId=rs.routeId
join stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;
set linesize 500
set pages 500
select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3368128430
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 119 | 1 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 119 | 1 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 119 | 0 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 79 | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ROUTES | 1 | 40 | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ROUTES_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS | 1 | 39 | 0 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | ROUTESTATIONS_PK | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | STATIONS_PK | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | STATIONS | 1 | 40 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("R"."ROUTEID"=1)
8 - access("RS"."ROUTEID"=1)
9 - access("RS"."STATIONID"="S"."STATIONID") （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
