数据库设计 – 在数据库中存储总线路径

现在是有趣的部分,让我们在路由表中添加一个列名.
现在我们在“路线”中确实需要一个专栏.
CBO使用索引查找路由1的rowID,然后访问该表(通过索引rowid访问表)并获取列“routes.name”.

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")

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!