1 PACKAGE BODY SDO_ROUTER_PARTITION AS
2
3 -- Partitioning log file
4 part_log_file utl_file.file_type := NULL;
5 schema_name VARCHAR2(64) := NULL;
6 JAVA_ERROR EXCEPTION;
7 PARAMETER_ERROR EXCEPTION;
8
9 --
10 -- Logging
11 --
12
13 --- Log a message
14 PROCEDURE log_message(message IN VARCHAR2, show_time IN BOOLEAN DEFAULT TRUE)
15 IS
16 BEGIN
17
18 if ( utl_file.is_open(part_log_file) = FALSE ) then
19 return;
20 end if;
21 IF ( show_time ) THEN
22 utl_file.put_line (part_log_file, sdo_util.number_to_char(sysdate,'Dy fmMon DD HH24:MI:SS YYYY'));
23 END IF;
24
25 utl_file.put_line (part_log_file, message);
26 utl_file.fflush(part_log_file);
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 raise_application_error(-20002, 'PLSQL Error Writing Log File');
31 END log_message;
32
33 --- Open the log file
34 FUNCTION open_log_file(log_file_name IN VARCHAR2)
35 RETURN VARCHAR2 AS
36 full_file_name VARCHAR2(256);
37 stmt VARCHAR2(256);
38 BEGIN
39 BEGIN
40 -- Open the routers partition log file
41
42 IF (NOT utl_file.is_open(part_log_file)) THEN
43 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
44 END IF;
45
46 stmt := 'SELECT directory_path FROM all_directories
47 WHERE directory_name=''SDO_ROUTER_LOG_DIR''';
48 EXECUTE IMMEDIATE stmt into full_file_name;
49
50 -- Make sure the path is terminated with a /
51 if (instr(full_file_name, '/', length(full_file_name)) = 0)
52 then full_file_name := full_file_name || '/';
53 end if;
54
55 full_file_name := full_file_name || log_file_name;
56
57 EXCEPTION
58 WHEN utl_file.invalid_path THEN
59 raise_application_error(-20006, '[ERROR] SDO_ROUTER_LOG_DIR directory not found');
60 WHEN utl_file.invalid_operation THEN
61 raise_application_error(-20006, '[ERROR] bad directory path specified for SDO_ROUTER_LOG_DIR directory');
62 WHEN OTHERS THEN
63 raise_application_error(-20001, '[ERROR] could not open log file');
64 END;
65
66 RETURN full_file_name;
67 END open_log_file;
68
69 --- Create the SDO Router log directory
70 PROCEDURE create_sdo_router_log_dir(
71 router_schema IN VARCHAR2,
72 new_dir_path IN VARCHAR2 DEFAULT NULL)
73 IS
74 current_schema VARCHAR2(32) := sys_context('userenv', 'CURRENT_SCHEMA');
75 directory_path VARCHAR2(256) := NULL;
76 row_count NUMBER := 0;
77 stmt VARCHAR2(256);
78 BEGIN
79 -- Make sure current user isn't one of the two being granted privs
80 if (current_schema = UPPER(router_schema) OR current_schema = 'MDSYS')
81 then raise_application_error(-20010, '[ERROR] you may not GRANT privileges to yourself');
82 end if;
83
84 -- Make sure 2 schemas get access to SDO_ROUTER_LOG_DIR
85 if (UPPER(router_schema) = 'MDSYS')
86 then raise_application_error(-20014, '[ERROR] MDSYS schema specified, please use a Routeserver schema');
87 end if;
88
89 -- Make sure the Routeserver schema specified actually exists
90 SELECT COUNT(*) INTO row_count
91 FROM all_users
92 WHERE username = UPPER(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(router_schema));
93
94 if (row_count = 0)
95 then raise_application_error(-20011, '[ERROR] schema does not exist');
96 end if;
97
98 if (new_dir_path is NULL)
99 then
100 SELECT COUNT(*) INTO row_count
101 FROM all_directories
102 WHERE directory_name = 'SDO_ROUTER_LOG_DIR';
103
104 -- If SDO_ROUTER_LOG_DIR does not already exist the new_dir_path parameter
105 -- must be specified. Raise an error if the directory does not exist and
106 -- the parameter was not specified.
107 if (row_count = 0)
108 then raise_application_error(-20012, '[ERROR] SDO_ROUTER_LOG_DIR directory does not exist');
109 end if;
110
111 SELECT directory_path INTO directory_path
112 FROM all_directories
113 WHERE directory_name = 'SDO_ROUTER_LOG_DIR';
114
115 directory_path := replace(directory_path, '\', '/');
116 else
117 directory_path := replace(new_dir_path, '\', '/');
118
119 -- Be sure the path is not relative. The first character or
120 -- the first character following the : (Windows) must be /
121 -- if / is not the first character, the : must exist and
122 -- not be the very first character
123 if (SUBSTR(directory_path, 1, 1) != '/') AND
124 (INSTR(directory_path, ':') < 2 OR
125 SUBSTR(directory_path, INSTR(directory_path, ':')+1, 1) != '/')
126 then raise_application_error(-20013, '[ERROR] malformed directory path specified');
127 end if;
128 end if;
129
130 -- Make sure the path is terminated with a /
131 if (instr(directory_path, '/', length(directory_path)) = 0)
132 then directory_path := directory_path || '/';
133 end if;
134
135 -- Create the directory
136 stmt := 'CREATE OR REPLACE DIRECTORY sdo_router_log_dir as '||
137 SYS.DBMS_ASSERT.ENQUOTE_LITERAL(directory_path);
138 EXECUTE IMMEDIATE stmt;
139
140 directory_path := directory_path || '*';
141
142 -- Grant privileges to the directory to the Router user and MDSYS
143 stmt := 'GRANT read, write ON DIRECTORY sdo_router_log_dir TO ' || UPPER(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(router_schema));
144 EXECUTE IMMEDIATE stmt;
145 dbms_java.grant_permission(
146 UPPER(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(router_schema)), 'java.io.FilePermission',
147 directory_path, 'read,write');
148
149 EXECUTE IMMEDIATE 'GRANT read, write ON DIRECTORY sdo_router_log_dir TO MDSYS';
150 dbms_java.grant_permission(
151 'MDSYS', 'java.io.FilePermission',
152 directory_path, 'read,write');
153
154 END create_sdo_router_log_dir;
155
156 PROCEDURE validate_sdo_router_log_dir(log_file_name IN VARCHAR2 := 'sdo_router_partition.log')
157 IS
158 full_file_name VARCHAR2(256);
159 schema_name VARCHAR2(32):= sys_context('userenv', 'CURRENT_SCHEMA');
160 BEGIN
161 -- From PLSQL
162 full_file_name := open_log_file(log_file_name);
163 log_message('[INFO] PLSQL logging OK (' || schema_name || ')');
164 utl_file.fclose(part_log_file);
165
166 -- From Java
167 elocation_validate_logfile(full_file_name, schema_name);
168
169 END validate_sdo_router_log_dir;
170
171 --
172 -- check if the given constraint exists
173 --
174 FUNCTION constraint_exists(constraint_name IN VARCHAR2)
175 RETURN VARCHAR2
176 IS
177 stmt VARCHAR2(256);
178 no NUMBER := 0;
179 BEGIN
180 stmt := 'SELECT COUNT(*) FROM user_constraints WHERE CONSTRAINT_NAME = :name';
181 EXECUTE IMMEDIATE stmt into no using nls_upper(constraint_name);
182 IF (no = 1) THEN
183 RETURN 'TRUE';
184 ELSE
185 RETURN 'FALSE';
186 END IF;
187 END constraint_exists;
188
189 --
190 -- check if the given index exists
191 --
192 FUNCTION index_exists(index_name IN VARCHAR2)
193 RETURN VARCHAR2
194 IS
195 stmt VARCHAR2(256);
196 no NUMBER := 0;
197 BEGIN
198 stmt := 'SELECT COUNT(*) FROM IND WHERE INDEX_NAME = :name';
199 EXECUTE IMMEDIATE stmt into no using nls_upper(index_name);
200 IF (no = 1) THEN
201 RETURN 'TRUE';
202 ELSE
203 RETURN 'FALSE';
204 END IF;
205 END index_exists;
206
207 --
208 -- check if the given network exists
209 --
210 FUNCTION network_exists(network_name IN VARCHAR2)
211 RETURN VARCHAR2
212 IS
213 md_stmt VARCHAR2(256);
214 ud_stmt VARCHAR2(256);
215 no NUMBER := 0;
216 BEGIN
217 md_stmt := 'SELECT COUNT(*) FROM USER_SDO_NETWORK_METADATA WHERE NETWORK = :name';
218 EXECUTE IMMEDIATE md_stmt into no using nls_upper(network_name);
219 IF (no = 1) THEN
220 ud_stmt := 'SELECT COUNT(*) FROM USER_SDO_NETWORK_USER_DATA WHERE NETWORK = :name';
221 EXECUTE IMMEDIATE ud_stmt into no using nls_upper(network_name);
222 IF (no > 0) THEN
223 RETURN 'TRUE';
224 END IF;
225 END IF;
226
227 RETURN 'FALSE';
228 END network_exists;
229
230 --
231 -- check if the given table exists
232 --
233 FUNCTION table_exists(tab_name IN VARCHAR2)
234 RETURN VARCHAR2
235 IS
236 stmt VARCHAR2(256);
237 no NUMBER := 0;
238 BEGIN
239 stmt := 'SELECT COUNT(*) FROM TAB WHERE TNAME = :name';
240 EXECUTE IMMEDIATE stmt into no using nls_upper(tab_name);
241 IF (no = 1) THEN
242 RETURN 'TRUE';
243 ELSE
244 RETURN 'FALSE';
245 END IF;
246 END table_exists;
247
248 --
249 -- disk based graph partition functions/procedures
250 -- based on moment of inertia appraoch
251 --
252 FUNCTION min_eigenvector(sum_x2 IN NUMBER, sum_y2 IN NUMBER, sum_xy IN NUMBER)
253 RETURN mdsys.vector_2d
254 IS
255 lamda NUMBER := 0;
256 tmp_sum NUMBER := 0;
257 k NUMBER := 0;
258 eigenvector_1 NUMBER := 0;
259 eigenvector_2 NUMBER := 0;
260 BEGIN
261 tmp_sum := sum_x2 + sum_y2;
262 lamda := (tmp_sum -
263 sqrt(tmp_sum*tmp_sum -4.0*(sum_x2*sum_y2-sum_xy*sum_xy)))/2;
264
265 IF (sum_xy = 0 or (sum_x2-lamda) = 0 ) THEN
266 IF (sum_x2 > sum_y2) THEN
267 eigenvector_1 := 0;
268 eigenvector_2 := 1.0;
269 ELSE
270 eigenvector_1 := 1.0;
271 eigenvector_2 := 0;
272 END IF;
273 ELSE
274 k := -sum_xy/(sum_x2-lamda);
275 eigenvector_2 := 1.0/sqrt(k*k+1.0);
276 eigenvector_1 := k*eigenvector_2;
277 END IF;
278
279 RETURN mdsys.vector_2d(eigenvector_1,eigenvector_2);
280
281 END min_eigenvector;
282
283 --
284 -- Return a negative value if the value falls in the given range (start_m,end_m)
285 --
286 FUNCTION adjust_m(start_m IN NUMBER, end_m IN NUMBER, m IN NUMBER)
287 RETURN NUMBER
288 IS
289 BEGIN
290 IF (m >= start_m AND m <= end_m) THEN
291 RETURN -m;
292 ELSE
293 RETURN m;
294 END IF;
295 END adjust_m;
296
297 --
298 -- return the p_id based on the given m value (m > 0) ? pid : (pid+1)
299 --
300 FUNCTION get_pid(m IN NUMBER, pid IN NUMBER)
301 RETURN NUMBER
302 IS
303 BEGIN
304 IF ( m < 0 ) THEN
305 RETURN pid;
306 ELSE
307 RETURN pid+1;
308 END IF;
309
310 END get_pid;
311
312 --
313 --
314 --
315 PROCEDURE adjust_final_pid(p_tab_name IN VARCHAR2)
316 IS
317 min_pid NUMBER;
318 stmt VARCHAR2(256);
319 BEGIN
320
321 EXECUTE IMMEDIATE 'truncate table ' ||
322 SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name);
323
324 stmt := 'SELECT MIN(P_ID) FROM final_partition';
325 EXECUTE IMMEDIATE stmt into min_pid ;
326
327 stmt := 'INSERT /*+ APPEND */ into ' ||
328 SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name) || ' (vertex_id,p_id,x,y) ' ||
329 ' select vertex_id, (p_id-:min_pid+1),x,y from final_partition';
330 EXECUTE IMMEDIATE stmt USING min_pid;
331
332 COMMIT;
333 END adjust_final_pid;
334
335 --
336 -- move vertices along the principal axes to make two partition with equal size
337 --
338 PROCEDURE make_partition_equal(tab_name IN VARCHAR2,
339 pid IN NUMBER,
340 v_no IN NUMBER,
341 part_counter IN NUMBER)
342 IS
343 no INTEGER;
344 vno1 NUMBER;
345 vno2 NUMBER;
346 stmt VARCHAR2(256);
347 part_m NUMBER;
348 BEGIN
349 part_m := 0;
350
351 -- two partitions for partition pid is based on the m sign (<0 or >= 0 )
352 stmt := 'SELECT COUNT(*) FROM ' ||
353 SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(tab_name) || ' WHERE m < 0 ';
354 EXECUTE IMMEDIATE stmt into vno1;
355
356 vno2 := v_no - vno1;
357
358 IF (vno1 > vno2) THEN
359 -- move n vertices from set (m < 0) to set (m>= 0)
360 -- by flipping its sign of m
361 no := (vno1-vno2)/2;
362 stmt := 'SELECT min(m) FROM (SELECT m FROM ' ||
363 SYS.DBMS_ASSERT.ENQUOTE_NAME(tab_name) ||
364 ' WHERE m < 0 ORDER BY m DESC) ' || ' WHERE rownum <= :no ';
365 EXECUTE IMMEDIATE stmt into part_m USING no;
366
367 INSERT /*+ APPEND */ INTO partition_tmp_3 (vertex_id,p_id,x,y,m)
368 SELECT vertex_id,
369 mdsys.SDO_ROUTER_PARTITION.get_pid(
370 mdsys.SDO_ROUTER_PARTITION.adjust_m(part_m,0,m),part_counter),
371 x,y,mdsys.SDO_ROUTER_PARTITION.adjust_m(part_m,0,m)
372 FROM partition_tmp_2;
373 ELSE
374 -- move n vertices from set (m >= 0) to set ( m < 0) by
375 -- updating pid in vertex table
376 no := (vno2-vno1)/2;
377 stmt := 'SELECT max(m) FROM (SELECT m FROM ' ||
378 SYS.DBMS_ASSERT.ENQUOTE_NAME(tab_name) ||
379 ' WHERE m >= 0 ORDER BY m) ' || ' WHERE rownum <= :no ' ;
380 EXECUTE IMMEDIATE stmt into part_m USING no;
381
382 INSERT /*+ APPEND */ INTO partition_tmp_3 (vertex_id,p_id,x,y,m)
383 SELECT vertex_id,
384 mdsys.SDO_ROUTER_PARTITION.get_pid(
385 mdsys.SDO_ROUTER_PARTITION.adjust_m(0,part_m,m),part_counter),
386 x,y,mdsys.SDO_ROUTER_PARTITION.adjust_m(0,part_m,m)
387 FROM partition_tmp_2;
388 END IF;
389
390 COMMIT;
391
392 END make_partition_equal;
393
394 --
395 -- partition procedure
396 --
397 PROCEDURE new_partition_proc(p_tab_name IN VARCHAR2,
398 max_v_no IN NUMBER,
399 partition_id IN NUMBER,
400 make_equal IN BOOLEAN,
401 part_counter IN OUT NUMBER)
402 IS
403 m_mean NUMBER;
404 v_no NUMBER;
405 x_mean NUMBER;
406 y_mean NUMBER;
407 eigenvec vector_2d;
408 partition_tmp_1 VARCHAR2(256);
409 stmt VARCHAR2(256);
410 table_name VARCHAR2(256);
411 BEGIN
412 --
413 -- terminal condition for bisecting
414 -- if vertex no. smaller than max_v_no ,stops
415
416 IF (partition_id = 0) THEN
417 partition_tmp_1 := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name);
418 ELSE
419 partition_tmp_1 := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || partition_id;
420 END IF;
421
422 stmt := 'SELECT COUNT(*) FROM ' || partition_tmp_1;
423 EXECUTE IMMEDIATE stmt into v_no;
424
425 IF (v_no = 0) THEN
426 RETURN;
427 END IF;
428
429 --
430 -- prepare data for eigenvalue/eigenvector calculation
431 --
432
436 stmt := 'SELECT mdsys.SDO_ROUTER_PARTITION.min_eigenvector(
433 stmt := 'SELECT AVG(x), AVG(y) from ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(partition_tmp_1);
434 EXECUTE IMMEDIATE stmt into x_mean, y_mean;
435
437 sum(power(x-:1,2)), sum(power(y-:2,2)),
438 sum((x-:3)*(y-:4))) FROM ' || partition_tmp_1;
439 EXECUTE IMMEDIATE stmt INTO eigenvec USING x_mean, y_mean, x_mean, y_mean;
440
441 stmt := 'SELECT AVG(:1*(x - :2) + :3*(y-:4)) FROM ' || partition_tmp_1;
442 EXECUTE IMMEDIATE stmt INTO m_mean USING -eigenvec.y, x_mean, eigenvec.x, y_mean;
443
444 stmt := 'INSERT /*+ APPEND */ into partition_tmp_2 (vertex_id,p_id,x,y,m)
445 SELECT vertex_id,p_id,x,y, (:1*(x - :2) + :3*(y-:4) - :5)
446 FROM ' || partition_tmp_1;
447 EXECUTE IMMEDIATE stmt USING -eigenvec.y, x_mean, eigenvec.x, y_mean, m_mean;
448
449 COMMIT;
450
451 --
452 -- make equal size if required
453 --
454
455 IF (make_equal) THEN
456 make_partition_equal('partition_tmp_2',partition_id,v_no,part_counter);
457 ELSE
458 INSERT /*+ APPEND */ INTO partition_tmp_3 (vertex_id,p_id,x,y,m)
459 SELECT vertex_id,
460 mdsys.SDO_ROUTER_PARTITION.get_pid(m, part_counter),
461 x,y,m
462 FROM partition_tmp_2;
463
464 COMMIT;
465 END IF;
466
467 IF (partition_id = 0) THEN
468 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name);
469 ELSE
470 table_name := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || partition_id;
471 EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
472 END IF;
473
474
475 IF (v_no/2 > max_v_no) THEN
476
477 table_name := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || part_counter;
478 IF ( table_exists(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)) = 'TRUE') THEN
479 execute immediate 'DROP TABLE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(table_name);
480 END IF;
481
482 stmt := 'CREATE TABLE ' || table_name ||
483 ' STORAGE (maxextents unlimited), NOLOGGING as
484 SELECT * FROM partition_tmp_3 WHERE p_id=' || part_counter;
485 EXECUTE IMMEDIATE stmt;
486
487 table_name := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || sdo_util.number_to_char(part_counter+1);
488
489 IF ( table_exists(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)) = 'TRUE') THEN
490 execute immediate 'DROP TABLE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(table_name);
491 END IF;
492 stmt := 'CREATE TABLE ' || table_name ||
493 ' STORAGE (maxextents unlimited), NOLOGGING as
494 SELECT * FROM partition_tmp_3 WHERE p_id=' || sdo_util.number_to_char(part_counter+1);
495 EXECUTE IMMEDIATE stmt;
496
497 COMMIT;
498 ELSE
499 INSERT /*+ APPEND */ INTO final_partition (vertex_id,p_id,x,y)
500 SELECT vertex_id,p_id,x,y FROM partition_tmp_3;
501
502 COMMIT;
503 END IF;
504
505 part_counter := part_counter+2;
506
507 EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_2';
508 EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_3';
509
510 END new_partition_proc;
511
512 --
513 -- main pl/sql procedure to partition a graph with coordinate information
514 --
515 PROCEDURE graph_partition(p_tab_name IN VARCHAR2,
516 max_v_no IN NUMBER,
517 make_equal IN BOOLEAN)
518 IS
519 p_level INTEGER;
520 stmt VARCHAR2(256);
521 v_no NUMBER;
522 min_pid NUMBER;
523 max_pid NUMBER;
524 pid NUMBER;
525 p_counter NUMBER;
526 p_date date;
527 show_time BOOLEAN := FALSE;
528 BEGIN
529 IF (table_exists(p_tab_name) = 'FALSE' ) THEN
530 log_message('ERROR: ' || p_tab_name || ' table not found');
531 RETURN ;
532 END IF;
533
534 stmt := 'SELECT MIN(p_id), MAX(p_id) FROM ' ||
535 SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name);
536 EXECUTE IMMEDIATE stmt INTO min_pid, max_pid;
537
538 stmt := 'SELECT COUNT(*) FROM ' ||
539 SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name) || ' WHERE p_id = :min_pid';
540 EXECUTE IMMEDIATE stmt INTO v_no USING min_pid;
541
542 p_level := floor(LN(v_no/max_v_no)/LN(2.0));
543
544
545 -- issue warning if the no of nodes in the table is smaller than max_v_no
546 if ( p_level < 0 ) THEN
547 log_message('WARNING: no. of nodes: ' || v_no || ' in table: ' || p_tab_name ||
548 ' is smaller than the given max_v_no: ' || max_v_no);
549 RETURN;
550 end if;
551
552
553 p_counter := max_pid+1; -- starting partition counter
554 pid := min_pid;
555
556 log_message('INFO: begin partitioning of '|| p_tab_name ||
557 ' partition level: ' || p_level || ' min(partition id): ' ||
558 min_pid || ' max(partition id): ' || max_pid);
559
560 log_message('INFO: generating ' || power(2,p_level+1) ||
561 ' partitions from level:0 to level: ' || p_level ||' ...', show_time);
562
563 FOR k IN min_pid..max_pid LOOP
564 FOR i IN 0..p_level LOOP
565 p_date := sysdate;
566 FOR j IN 1..power(2,i) LOOP
567 new_partition_proc(SYS.DBMS_ASSERT.NOOP(p_tab_name), max_v_no, pid, make_equal, p_counter);
568 pid := pid +1;
569 END LOOP;
573 log_message('INFO: partitioning level: ' || i || ' with ' || power(2,i+1) || ' partitions '|| ' took ' ||
570 log_message('INFO: partitioning '|| p_tab_name ||
571 ' level: ' || i || ' partition id: ' || pid);
572 -- add computation time form each level
574 sdo_util.number_to_char((sysdate-p_date)*24*60,'99999.999') || ' min.',show_time) ;
575 END LOOP;
576 END LOOP;
577
578 -- Copy the result back to original table and ajust the pids
579 adjust_final_pid(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name));
580
581 log_message('INFO: completed partitioning of '|| p_tab_name);
582
583 EXCEPTION
584 WHEN OTHERS THEN
585 log_message('Exception processing partition '|| pid ||
586 ' of the ' || p_tab_name || ' table');
587 log_message(SQLERRM);
588 raise_application_error(-20009, 'Error Graphing Partitions');
589
590 END graph_partition;
591
592 --
593 -- drop all temporary tables for partitioning
594 --
595 PROCEDURE clean_tables (cleanup IN BOOLEAN DEFAULT TRUE)
596
597 IS
598 BEGIN
599 -- Tables that should always be cleaned up
600 --
601 IF (table_exists('node_tmp') = 'TRUE') THEN
602 EXECUTE IMMEDIATE 'rename node_tmp to node';
603
604 IF (constraint_exists('FK_EDGE_START_NODE_ID') = 'TRUE') THEN
605 EXECUTE IMMEDIATE 'ALTER TABLE edge DROP CONSTRAINT FK_EDGE_START_NODE_ID';
606 END IF;
607
608 IF (constraint_exists('FK_EDGE_END_NODE_ID') = 'TRUE') THEN
609 EXECUTE IMMEDIATE 'ALTER TABLE edge DROP CONSTRAINT FK_EDGE_END_NODE_ID';
610 END IF;
611
612 IF (constraint_exists('PK_NODE') = 'TRUE') THEN
613 EXECUTE IMMEDIATE 'ALTER TABLE node DROP CONSTRAINT PK_NODE';
614 END IF;
615
616 IF (index_exists('node_id_index') = 'TRUE') THEN
617 EXECUTE IMMEDIATE 'DROP INDEX node_id_index';
618 END IF;
619 IF (index_exists('node_id_idx') = 'TRUE') THEN
620 EXECUTE IMMEDIATE 'DROP INDEX node_id_idx';
621 END IF;
622
623 IF (index_exists('node_partition_index') = 'TRUE') THEN
624 EXECUTE IMMEDIATE 'DROP INDEX node_partition_index';
625 END IF;
626 IF (index_exists('node_partition_idx') = 'TRUE') THEN
627 EXECUTE IMMEDIATE 'DROP INDEX node_partition_idx';
628 END IF;
629
630 -- rebuild node index
631 log_message('INFO: rebuilding node_id_idx on node');
632 EXECUTE IMMEDIATE 'CREATE INDEX node_id_idx on node(node_id)';
633
634 -- rebuild primary key
635 EXECUTE IMMEDIATE 'ALTER TABLE node ADD CONSTRAINT pk_node PRIMARY KEY(node_id)';
636
637 -- rebuild foreign keys
638 EXECUTE IMMEDIATE 'ALTER TABLE edge ADD CONSTRAINT fk_edge_start_node_id FOREIGN KEY (start_node_id) REFERENCES node(node_id)';
639 EXECUTE IMMEDIATE 'ALTER TABLE edge ADD CONSTRAINT fk_edge_end_node_id FOREIGN KEY (end_node_id) REFERENCES node(node_id)';
640
641 -- rebuild node/partition index
642 log_message('INFO: rebuild node_partition_idx on node');
643 EXECUTE IMMEDIATE 'CREATE INDEX node_partition_idx on node(partition_id)';
644 END IF;
645
646 IF (table_exists('edge_array_tmp') = 'TRUE') THEN
647 EXECUTE IMMEDIATE 'DROP TABLE edge_array_tmp';
648 END IF;
649
650 IF (table_exists('final_partition') = 'TRUE') THEN
651 EXECUTE IMMEDIATE 'DROP TABLE final_partition';
652 END IF;
653
654 IF (table_exists('partition_tmp_2') = 'TRUE') THEN
655 EXECUTE IMMEDIATE 'DROP TABLE partition_tmp_2';
656 END IF;
657
658 IF (table_exists('partition_tmp_3') = 'TRUE') THEN
659 EXECUTE IMMEDIATE 'DROP TABLE partition_tmp_3';
660 END IF;
661
662 -- Tables we may want to keep for debugging purposes.
663 --
664 IF (cleanup) THEN
665 IF (table_exists('edge_part') = 'TRUE') THEN
666 EXECUTE IMMEDIATE 'DROP TABLE edge_part';
667 END IF;
668
669 IF (table_exists('node_part') = 'TRUE') THEN
670 EXECUTE IMMEDIATE 'DROP TABLE node_part';
671 END IF;
672
673 IF (table_exists('restricted_nodes')= 'TRUE') THEN
674 EXECUTE IMMEDIATE 'DROP TABLE restricted_nodes';
675 END IF;
676
677 IF (table_exists('restricted_edges')= 'TRUE') THEN
678 EXECUTE IMMEDIATE 'DROP TABLE restricted_edges';
679 END IF;
680
681 IF (table_exists('super_edge_ids') = 'TRUE') THEN
682 EXECUTE IMMEDIATE 'DROP TABLE super_edge_ids';
683 END IF;
684
685 IF (table_exists('super_node_ids') = 'TRUE') THEN
686 EXECUTE IMMEDIATE 'DROP TABLE super_node_ids';
687 END IF;
688 -- Trucking intermediate data
689 --
690 IF (table_exists('router_partitioned_truck_data') = 'TRUE') THEN
691 EXECUTE IMMEDIATE 'DROP TABLE router_partitioned_truck_data';
692 END IF;
693 IF (table_exists('partitioned_router_transport') = 'TRUE') THEN
694 EXECUTE IMMEDIATE 'DROP TABLE partitioned_router_transport';
695 END IF;
696 IF (table_exists('new_trucking_data') = 'TRUE') THEN
697 EXECUTE IMMEDIATE 'DROP TABLE new_trucking_data';
698 END IF;
699 -- Turn restriction intermediate data
700 IF (table_exists('partitioned_router_nav_strand') = 'TRUE') THEN
701 EXECUTE IMMEDIATE 'DROP TABLE partitioned_router_nav_strand';
702 END IF;
703 IF (table_exists('router_max_seq') = 'TRUE') THEN
707 EXECUTE IMMEDIATE 'DROP TABLE new_turn_restriction_data';
704 EXECUTE IMMEDIATE 'DROP TABLE router_max_seq';
705 END IF;
706 IF (table_exists('new_turn_restriction_data') = 'TRUE') THEN
708 END IF;
709 END IF;
710 END;
711 --
712 -- setup all temporary tables for partitioning
713 --
714 PROCEDURE setup_tables
715 IS
716 BEGIN
717 log_message('INFO: setting up partitioning temporary tables');
718
719 IF (table_exists('partition_tmp_2') = 'TRUE') THEN
720 EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_2';
721 ELSE
722 EXECUTE IMMEDIATE 'CREATE TABLE partition_tmp_2 (vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER,m NUMBER)
723 STORAGE (maxextents unlimited), NOLOGGING';
724 END IF;
725
726 IF (table_exists('partition_tmp_3') = 'TRUE') THEN
727 EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_3';
728 ELSE
729 EXECUTE IMMEDIATE 'CREATE TABLE partition_tmp_3 (vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER,m NUMBER)
730 STORAGE (maxextents unlimited), NOLOGGING';
731 END IF;
732
733 IF (table_exists('final_partition')= 'TRUE') THEN
734 EXECUTE IMMEDIATE 'TRUNCATE TABLE final_partition';
735 ELSE
736 EXECUTE IMMEDIATE 'CREATE TABLE final_partition (vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER)
737 STORAGE (maxextents unlimited), NOLOGGING';
738 END IF;
739
740 IF (table_exists('edge_part') = 'TRUE') THEN
741 EXECUTE IMMEDIATE 'DROP TABLE edge_part';
742 END IF;
743
744 IF (table_exists('node_part') = 'TRUE') THEN
745 EXECUTE IMMEDIATE 'DROP TABLE node_part';
746 END IF;
747
748 IF (table_exists('restricted_nodes')= 'TRUE') THEN
749 EXECUTE IMMEDIATE 'DROP TABLE restricted_nodes';
750 END IF;
751
752 IF (table_exists('restricted_edges')= 'TRUE') THEN
753 EXECUTE IMMEDIATE 'DROP TABLE restricted_edges';
754 END IF;
755
756 IF (table_exists('super_edge_ids') = 'TRUE') THEN
757 EXECUTE IMMEDIATE 'DROP TABLE super_edge_ids';
758 END IF;
759
760 IF (table_exists('super_node_ids') = 'TRUE') THEN
761 EXECUTE IMMEDIATE 'DROP TABLE super_node_ids';
762 END IF;
763 END setup_tables;
764
765
766 --
767 -- Create the node_part table needed for partitioning
768 --
769 PROCEDURE create_node_part
770 IS
771 stmt VARCHAR2(256);
772 BEGIN
773 IF (table_exists('node_part') = 'TRUE') THEN
774 EXECUTE IMMEDIATE 'DROP TABLE node_part';
775 END IF;
776
777 log_message('INFO: create and load node_part table');
778
779 EXECUTE IMMEDIATE 'CREATE TABLE node_part(vertex_id NUMBER, x NUMBER, y NUMBER, p_id NUMBER, outedges mdsys.num_array, inedges mdsys.num_array)
780 STORAGE (maxextents unlimited), NOLOGGING';
781
782 EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ into node_part
783 SELECT n.node_id, n.geometry.sdo_point.x, n.geometry.sdo_point.y, 0, null, null
784 FROM node n';
785
786 COMMIT;
787
788 -- Create an index on the node_part table
789 --
790 log_message('INFO: create index np_vp_idx on node_part');
791 EXECUTE IMMEDIATE 'CREATE INDEX np_vp_idx on node_part(vertex_id, p_id)';
792
793 -- gather table and index stats
794 log_message('INFO: gather node_part table statistics');
795 gather_table_stats('NODE_PART');
796
797 END create_node_part;
798
799 --
800 -- Create and load the restricted_edges table.
801 --
802 PROCEDURE create_restricted_edges
803 IS
804 TYPE CURSOR_TYPE IS REF CURSOR;
805 coords MDSYS.SDO_ORDINATE_ARRAY;
806 divider VARCHAR2(1);
807 edge_id NUMBER;
808 ins_stmt VARCHAR(256);
809 p_cursor CURSOR_TYPE;
810 BEGIN
811 IF (table_exists('restricted_edges') = 'TRUE') THEN
812 EXECUTE IMMEDIATE 'DROP TABLE restricted_edges';
813 END IF;
814
815 log_message('INFO: Create and load the restricted_edges table');
816
817 EXECUTE IMMEDIATE 'CREATE TABLE restricted_edges(edge_id NUMBER,
818 divider VARCHAR2(1),
819 startx1 NUMBER, starty1 NUMBER,
820 startx2 NUMBER, starty2 NUMBER,
821 endx1 NUMBER, endy1 NUMBER,
822 endx2 NUMBER, endy2 NUMBER)
823 STORAGE (maxextents unlimited), NOLOGGING';
824
825 ins_stmt := 'INSERT INTO restricted_edges VALUES ' ||
826 '(:eid, :div, :sx1, :sy1, :sx2, :sy2,:ex1, :ey1, :ex2, :ey2)';
827
828 -- Find all edges attached to nodes that have restricted edges either inbound
829 -- or outbound from the node.
830 OPEN p_cursor FOR 'SELECT t.edge_id, t.divider, t.geometry.sdo_ordinates' ||
831 ' FROM edge t WHERE t.edge_id in ' ||
832 '(SELECT edge_id FROM EDGE WHERE start_node_id IN (SELECT node_id FROM restricted_nodes) UNION' ||
833 ' SELECT edge_id FROM EDGE WHERE end_node_id IN (SELECT node_id FROM restricted_nodes))';
834
835 LOOP
836 FETCH p_cursor INTO edge_id, divider, coords;
837 EXIT WHEN p_cursor%NOTFOUND;
838
839 -- Find and store the edges first and last segments
843 coords(3), coords(4),
840 EXECUTE IMMEDIATE ins_stmt USING
841 edge_id, divider,
842 coords(1), coords(2),
844 coords(coords.count-3), coords(coords.count-2),
845 coords(coords.count-1), coords(coords.count);
846 END LOOP;
847 CLOSE p_cursor;
848
849 COMMIT;
850
851 log_message('INFO: create index restricted_edges_idx');
852 EXECUTE IMMEDIATE 'CREATE INDEX restricted_edges_idx on restricted_edges(edge_id)';
853
854 log_message('INFO: gather restricted_edges table statistics');
855 gather_table_stats('RESTRICTED_EDGES');
856 END create_restricted_edges;
857
858 --
859 -- Create the restricted_nodes table needed for turn restriction generation
860 --
861 PROCEDURE create_restricted_nodes
862 IS
863 BEGIN
864 IF (table_exists('restricted_nodes') = 'TRUE') THEN
865 EXECUTE IMMEDIATE 'DROP TABLE restricted_nodes';
866 END IF;
867
868 log_message('INFO: Create and load the restricted_nodes table');
869
870 EXECUTE IMMEDIATE 'CREATE TABLE restricted_nodes(node_id NUMBER, inedges mdsys.num_array, outedges mdsys.num_array)
871 STORAGE (maxextents unlimited), NOLOGGING';
872
873 -- Find all nodes that are the start or end point of a restricted edge.
874 EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO restricted_nodes
875 SELECT vertex_id, inedges, outedges FROM NODE_PART where vertex_id IN
876 (SELECT source_id FROM edge_part WHERE divider IN (''1'', ''A'') union
877 SELECT target_id FROM edge_part WHERE divider IN (''2'', ''A''))';
878
879 COMMIT;
880
881 END create_restricted_nodes;
882
883 PROCEDURE update_node_part_edge_arrays
884 IS
885 TYPE CURSOR_TYPE IS REF CURSOR;
886 e_cursor CURSOR_TYPE;
887 e_id NUMBER;
888 e_array1 mdsys.num_array;
889 e_array2 mdsys.num_array;
890 n_cursor CURSOR_TYPE;
891 n_id NUMBER;
892 stmt VARCHAR2(512);
893 BEGIN
894 -- Create a temporary table to store the results of the array builds
895 IF (table_exists('edge_array_tmp') = 'TRUE') THEN
896 EXECUTE IMMEDIATE 'DROP TABLE edge_array_tmp';
897 END IF;
898
899 stmt := 'CREATE TABLE edge_array_tmp(vertex_id NUMBER, outedges mdsys.num_array, inedges mdsys.num_array)
900 STORAGE (maxextents unlimited), NOLOGGING';
901
902 EXECUTE IMMEDIATE stmt;
903
904 -- For every vertex in the node_part table build an in and out edge array.
905 -- The where clause forces a fast index only scan
906 OPEN n_cursor FOR 'SELECT vertex_id FROM node_part where vertex_id>0';
907 LOOP
908 FETCH n_cursor into n_id;
909 EXIT WHEN n_cursor%NOTFOUND;
910
911 e_array1 := mdsys.num_array();
912
913 -- Build an array of outedges for a particular node
914 OPEN e_cursor for 'select edge_id from edge_part where source_id=:id' using n_id;
915 LOOP
916 FETCH e_cursor INTO e_id;
917 EXIT WHEN e_cursor%NOTFOUND;
918
919 e_array1.extend(1);
920 e_array1(e_array1.count) := e_id;
921 END LOOP;
922 CLOSE e_cursor;
923
924 e_array2 := mdsys.num_array();
925
926 -- Build an array of inedges for a particular node
927 OPEN e_cursor for 'select edge_id from edge_part where target_id=:id' using n_id;
928 LOOP
929 FETCH e_cursor INTO e_id;
930 EXIT WHEN e_cursor%NOTFOUND;
931
932 e_array2.extend(1);
933 e_array2(e_array2.count) := e_id;
934 END LOOP;
935 CLOSE e_cursor;
936
937 -- Store the results
938 stmt := 'INSERT INTO edge_array_tmp VALUES (:node_id, :outedges, :inedges)';
939 EXECUTE IMMEDIATE stmt USING n_id, e_array1, e_array2;
940 END LOOP;
941 CLOSE n_cursor;
942
943 COMMIT;
944
945 EXECUTE IMMEDIATE 'CREATE INDEX eat_idx on edge_array_tmp(vertex_id)';
946 gather_table_stats('EDGE_ARRAY_TMP');
947
948 -- Rebuild the node_part table from scratch using the edge array information
949 stmt := 'CREATE TABLE new_node_part
950 STORAGE (maxextents unlimited), NOLOGGING AS
951 SELECT n.vertex_id, n.x, n.y, n.p_id, e.outedges, e.inedges
952 FROM node_part n, edge_array_tmp e
953 WHERE n.vertex_id=e.vertex_id';
954 EXECUTE IMMEDIATE stmt;
955
956 -- Replace the old node_part table with the new one containing
957 -- the in and out edge arrays
958 --
959 EXECUTE IMMEDIATE 'DROP TABLE node_part';
960 EXECUTE IMMEDIATE 'RENAME new_node_part to node_part';
961
962 -- Add needed indexes to the node_part table.
963 --
964 log_message('INFO: rebuild index np_vp_idx on node_part');
965 EXECUTE IMMEDIATE 'CREATE INDEX np_vp_idx on node_part(vertex_id, p_id)';
966
967 log_message('INFO: create index node_part_p_idx on node_part');
968 EXECUTE IMMEDIATE 'CREATE INDEX node_part_p_idx on node_part(p_id)';
969
970 -- rebuild the table and index stats
971 log_message('INFO: rebuild node_part table statistics');
972 gather_table_stats('NODE_PART');
973
974 EXECUTE IMMEDIATE 'DROP TABLE edge_array_tmp';
975 END update_node_part_edge_arrays;
976
977 --
978 -- Create the node_part table needed for partitioning
979 --
980 PROCEDURE create_super_tables
984 --
981 IS
982 stmt VARCHAR2(256);
983 BEGIN
985 -- Drop any constraints on the EDGE and NODE tables
986 IF (constraint_exists('FK_EDGE_START_NODE_ID') = 'TRUE') THEN
987 EXECUTE IMMEDIATE 'ALTER TABLE edge DROP CONSTRAINT FK_EDGE_START_NODE_ID';
988 END IF;
989 IF (constraint_exists('FK_EDGE_END_NODE_ID') = 'TRUE') THEN
990 EXECUTE IMMEDIATE 'ALTER TABLE edge DROP CONSTRAINT FK_EDGE_END_NODE_ID';
991 END IF;
992
993 IF (constraint_exists('PK_NODE') = 'TRUE') THEN
994 EXECUTE IMMEDIATE 'ALTER TABLE node DROP CONSTRAINT PK_NODE';
995 ELSE
996 EXECUTE IMMEDIATE 'ALTER TABLE node DROP PRIMARY KEY';
997 END IF;
998
999 -- Drop and indexes on the NODE table
1000 IF (index_exists('node_id_index') = 'TRUE') THEN
1001 EXECUTE IMMEDIATE 'DROP INDEX node_id_index';
1002 END IF;
1003 IF (index_exists('node_id_idx') = 'TRUE') THEN
1004 EXECUTE IMMEDIATE 'DROP INDEX node_id_idx';
1005 END IF;
1006
1007 IF (index_exists('node_partition_index') = 'TRUE') THEN
1008 EXECUTE IMMEDIATE 'DROP INDEX node_partition_index';
1009 END IF;
1010 IF (index_exists('node_partition_idx') = 'TRUE') THEN
1011 EXECUTE IMMEDIATE 'DROP INDEX node_partition_idx';
1012 END IF;
1013
1014 -- Rename the node table so we can use CTAS to rebuild it
1015 EXECUTE IMMEDIATE 'RENAME node to node_tmp';
1016
1017 -- Create and populate the edge_part table.
1018 --
1019 IF (table_exists('edge_part') = 'TRUE') THEN
1020 EXECUTE IMMEDIATE 'DROP TABLE edge_part';
1021 END IF;
1022
1023 log_message('INFO: create and load edge_part');
1024 EXECUTE IMMEDIATE 'CREATE TABLE edge_part(edge_id NUMBER, source_id NUMBER,
1025 target_id NUMBER, source_p_id NUMBER, target_p_id NUMBER,
1026 func_class NUMBER, length NUMBER, speed_limit NUMBER, divider VARCHAR2(1),
1027 turn_restrictions mdsys.num_array)
1028 STORAGE (maxextents unlimited), NOLOGGING';
1029
1030 EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO edge_part
1031 SELECT edge_id, start_node_id, end_node_id,
1032 (SELECT p_id FROM node_part WHERE vertex_id = start_node_id),
1033 (SELECT p_id FROM node_part WHERE vertex_id = end_node_id),
1034 func_class, length, speed_limit, divider, null
1035 FROM edge';
1036
1037 COMMIT;
1038
1039 -- Create useful indices on edge_part.
1040 --
1041 log_message('INFO: create index edge_part_e_idx on edge_part');
1042 EXECUTE IMMEDIATE 'CREATE INDEX edge_part_e_idx on edge_part(edge_id)';
1043
1044 log_message('INFO: create index edge_part_s_e_idx on edge_part');
1045 EXECUTE IMMEDIATE 'CREATE INDEX edge_part_s_e_idx on edge_part(source_id, edge_id)';
1046
1047 log_message('INFO: create index edge_part_t_e_idx on edge_part');
1048 EXECUTE IMMEDIATE 'CREATE INDEX edge_part_t_e_idx on edge_part(target_id, edge_id)';
1049
1050 log_message('INFO: create index edge_part_st_p_idx on edge_part');
1051 EXECUTE IMMEDIATE 'CREATE INDEX edge_part_st_p_idx on edge_part(source_p_id, target_p_id)';
1052
1053 log_message('INFO: create index edge_part_ts_p_idx on edge_part');
1054 EXECUTE IMMEDIATE 'CREATE INDEX edge_part_ts_p_idx on edge_part(target_p_id, source_p_id)';
1055
1056 -- build the table and index stats
1057 log_message('INFO: gather edge_part table statistics');
1058 gather_table_stats('EDGE_PART');
1059
1060 -- Populate the inedges and outedges fields to the node_part table.
1061 --
1062 log_message('INFO: create and load outedge and inedge columns in node_part table');
1063 update_node_part_edge_arrays;
1064
1065 create_restricted_nodes;
1066
1067 -- Recreate the node table and load it from the node_tmp table.
1068 --
1069 log_message('INFO: recreating node table with partitioning information');
1070 EXECUTE IMMEDIATE 'CREATE TABLE node STORAGE (maxextents unlimited), NOLOGGING AS
1071 SELECT nt.node_id, nt.geometry, np.p_id partition_id
1072 FROM node_tmp nt, node_part np
1073 WHERE nt.node_id = np.vertex_id';
1074 COMMIT;
1075
1076 EXECUTE IMMEDIATE 'DROP TABLE node_tmp CASCADE CONSTRAINTS';
1077
1078 -- Create an index on the node and partition id fields in the NODE table.
1079 --
1080 log_message('INFO: create index node_id_idx on node');
1081 EXECUTE IMMEDIATE 'CREATE INDEX node_id_idx on node(node_id)';
1082
1083 -- Make node_id a Primary key
1084 EXECUTE IMMEDIATE 'ALTER TABLE node ADD CONSTRAINT pk_node PRIMARY KEY(node_id)';
1085
1086 log_message('INFO: create foreign keys');
1087 EXECUTE IMMEDIATE 'ALTER TABLE edge ADD CONSTRAINT fk_edge_start_node_id FOREIGN KEY (start_node_id) REFERENCES node(node_id)';
1088 EXECUTE IMMEDIATE 'ALTER TABLE edge ADD CONSTRAINT fk_edge_end_node_id FOREIGN KEY (end_node_id) REFERENCES node(node_id)';
1089
1090 log_message('INFO: create index node_partition_idx on node');
1091 EXECUTE IMMEDIATE 'CREATE INDEX node_partition_idx on node(partition_id)';
1092
1093 log_message('INFO: rebuild node table statistics');
1094 gather_table_stats('NODE');
1095
1096 -- Set the partition ids in the edge table.
1097 --
1098 EXECUTE IMMEDIATE 'ALTER TABLE edge nologging';
1099 IF (index_exists('edge_partition_index') = 'TRUE') THEN
1100 EXECUTE IMMEDIATE 'DROP INDEX edge_partition_index';
1101 END IF;
1102
1106 WHERE vertex_id = start_node_id)';
1103 log_message('INFO: updating edge table with partitioning information');
1104 EXECUTE IMMEDIATE 'UPDATE edge
1105 SET partition_id = (SELECT p_id from node_part
1107 COMMIT;
1108
1109 -- Create an index on the partition id field in the EDGE table.
1110 --
1111 log_message('INFO: create index edge_partition_index on edge');
1112 EXECUTE IMMEDIATE 'CREATE INDEX edge_partition_index on edge(partition_id)';
1113
1114 -- build the table and index stats
1115 log_message('INFO: gather edge table statistics');
1116 gather_table_stats('EDGE');
1117
1118 -- Create and populate the super_node and super_edge tables.
1119 --
1120 log_message('INFO: creating and loading super_node_ids table');
1121 IF (table_exists('super_node_ids') = 'TRUE') THEN
1122 EXECUTE IMMEDIATE 'TRUNCATE TABLE super_node_ids';
1123 ELSE
1124 EXECUTE IMMEDIATE 'CREATE TABLE super_node_ids (node_id number)';
1125 END IF;
1126
1127 EXECUTE IMMEDIATE 'INSERT /*+APPEND */ into super_node_ids
1128 (SELECT source_id FROM edge_part WHERE func_class = 1 or func_class=2
1129 UNION
1130 SELECT target_id FROM edge_part WHERE func_class = 1 or func_class=2)';
1131
1132 log_message('INFO: creating and loading super_edge_ids table');
1133 IF (table_exists('super_edge_ids') = 'TRUE') THEN
1134 EXECUTE IMMEDIATE 'TRUNCATE TABLE super_edge_ids';
1135 ELSE
1136 EXECUTE IMMEDIATE 'CREATE TABLE super_edge_ids (edge_id number)';
1137 END IF;
1138
1139 EXECUTE IMMEDIATE 'INSERT /*+APPEND */ into super_edge_ids
1140 SELECT edge_id FROM edge_part WHERE func_class =1 or func_class = 2';
1141
1142 COMMIT;
1143 END create_super_tables;
1144
1145 ---
1146 ---
1147 ---
1148 PROCEDURE gather_table_stats(table_name IN VARCHAR2)
1149 IS
1150 stmt VARCHAR2(256):= 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(:schema_name, :table_name, NULL, DBMS_STATS.AUTO_SAMPLE_SIZE)';
1151 BEGIN
1152 IF (table_exists(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)) = 'FALSE') THEN
1153 log_message('ERROR: ' || table_name || ' not found for gather table statistics');
1154 RETURN ;
1155 END IF;
1156
1157 IF (schema_name IS NULL) THEN
1158 stmt := 'SELECT username FROM user_users';
1159 EXECUTE IMMEDIATE stmt into schema_name;
1160 END IF;
1161
1162 -- Gather table and index stats for the specified table
1163 DBMS_STATS.GATHER_TABLE_STATS(
1164 ownname => schema_name,
1165 tabname => table_name,
1166 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
1167 degree => DBMS_STATS.AUTO_DEGREE);
1168
1169 END gather_table_stats;
1170
1171 ---
1172 ---
1173 ---
1174 FUNCTION get_edge_info(edge_ids IN sdo_list_type,
1175 to_edge_ids OUT sdo_list_type,
1176 rets OUT mdsys.string_array,
1177 angle_segments OUT sdo_list_type)
1178 RETURN mdsys.string_array AS
1179 n INTEGER;
1180 i INTEGER;
1181 k INTEGER;
1182 base INTEGER := 1;
1183 to_edge_id INTEGER;
1184 coords mdsys.sdo_ordinate_array;
1185 names mdsys.string_array;
1186 name_query VARCHAR2(2000);
1187 sign_query VARCHAR2(2000);
1188 ret VARCHAR2(200);
1189 TYPE cursor_type IS REF CURSOR;
1190 sign_cursor cursor_type;
1191 BEGIN
1192 IF (edge_ids IS NULL) THEN
1193 RETURN NULL;
1194 END IF;
1195
1196 -- Initialize varrays
1197 n := edge_ids.count;
1198 names := mdsys.string_array();
1199 to_edge_ids := sdo_list_type();
1200 rets := mdsys.string_array();
1201 angle_segments := sdo_list_type();
1202 names.extend(n);
1203 to_edge_ids.extend(n);
1204 rets.extend(n);
1205 -- Need 4 points to describe a start and end segment for each edge id
1206 angle_segments.extend(n*8);
1207
1208 -- Initialize name query
1209 name_query := 'SELECT t.name,t.geometry.sdo_ordinates FROM edge t WHERE t.edge_id = :1';
1210 -- Initialize sign query
1211 sign_query := 'SELECT to_edge_id, ' ||
1212 'ramp || '':'' || exit || '':'' || toward '||
1213 'FROM sign_post ' ||
1214 'WHERE from_edge_id = :1';
1215
1216
1217 -- Iterate through route edge_ids and find info for each.
1218 FOR i IN 1..n LOOP
1219 EXECUTE IMMEDIATE name_query
1220 INTO names(i), coords
1221 USING edge_ids(i);
1222 IF (names(i) IS NULL) THEN
1223 names(i) := 'RAMP';
1224 END IF;
1225 -- Get sign information, if any.
1226 to_edge_ids(i) := 0;
1227 rets(i) := NULL;
1228 -- Get the coordinates for the start and end segments of the edge
1229 angle_segments(base) := coords(1);
1230 angle_segments(base+1) := coords(2);
1231 angle_segments(base+2) := coords(3);
1232 angle_segments(base+3) := coords(4);
1233 angle_segments(base+4) := coords(coords.count-3);
1234 angle_segments(base+5) := coords(coords.count-2);
1235 angle_segments(base+6) := coords(coords.count-1);
1236 angle_segments(base+7) := coords(coords.count);
1237 base := base + 8;
1238 -- We have to use ABS(edge_ids(i)) since sign_post
1242 OPEN sign_cursor FOR sign_query USING ABS(edge_ids(i));
1239 -- table contains only NAVSTREETS edge ids
1240 -- (positive only) not routeserver edge ids (which
1241 -- can be negative).
1243 LOOP
1244 FETCH sign_cursor INTO to_edge_id, ret;
1245 EXIT WHEN sign_cursor%NOTFOUND;
1246 FOR k IN i+1..n LOOP
1247 IF (to_edge_id = edge_ids(k) OR
1248 (-1*to_edge_id) = edge_ids(k)) THEN
1249 -- Make sure we assign router edge id:
1250 -- (negative or positive)!!!
1251 to_edge_ids(i) := edge_ids(k);
1252 rets(i) := ret;
1253 EXIT;
1254 END IF;
1255 END LOOP;
1256 IF (to_edge_ids(i) <> 0) THEN
1257 EXIT;
1258 END IF;
1259 END LOOP;
1260 CLOSE sign_cursor;
1261 END LOOP;
1262 RETURN names;
1263 END get_edge_info;
1264
1265 ---
1266 ---
1267 ---
1268 FUNCTION get_geometry_info (edge_ids IN sdo_list_type,
1269 merged_coords OUT sdo_list_type)
1270 RETURN NUMBER AS
1271 coords MDSYS.SDO_ORDINATE_ARRAY;
1272 j NUMBER;
1273 k NUMBER;
1274 BEGIN
1275 IF (edge_ids IS NULL) THEN
1276 RETURN 0;
1277 END IF;
1278
1279 k := 1;
1280 merged_coords := sdo_list_type();
1281
1282 -- For each input edge id, get the list of coordinates for the edge and
1283 -- build a list of all coordinates for the edges.
1284 FOR i in 1 .. edge_ids.count
1285 LOOP
1286 EXECUTE IMMEDIATE
1287 'select t.geometry.sdo_ordinates from edge t ' ||
1288 'where edge_id=:i'
1289 INTO coords USING edge_ids(i);
1290
1291 j := 1;
1292
1293 merged_coords.extend(coords.count + 1);
1294 merged_coords(k) := coords.count;
1295 k := k + 1;
1296
1297 WHILE j <= coords.count
1298 LOOP
1299 merged_coords(k) := coords(j);
1300 merged_coords(k+1) := coords(j+1);
1301
1302 j := j + 2;
1303 k := k + 2;
1304 END LOOP;
1305 END LOOP;
1306
1307 RETURN merged_coords.count;
1308
1309 END get_geometry_info;
1310
1311 ---
1312 --- Find and validate the Routeservers data version
1313 ---
1314 FUNCTION get_version_info
1315 RETURN VARCHAR2
1316 IS
1317 data_version VARCHAR2(32) := '10.2.0.4.0';
1318 major_version VARCHAR2(16);
1319 stmt VARCHAR2(256);
1320 v_count NUMBER;
1321 BEGIN
1322 IF (table_exists('SDO_ROUTER_DATA_VERSION')= 'TRUE') THEN
1323 stmt := 'SELECT COUNT(*) FROM SDO_ROUTER_DATA_VERSION';
1324 EXECUTE IMMEDIATE stmt INTO v_count;
1325
1326 IF (v_count != 1) THEN
1327 log_message('ERROR: Routeserver data version table corrupted, ' ||
1328 'multiple versions found');
1329 raise_application_error(-20005, 'Error getting data version, multiple versions found');
1330 END IF;
1331
1332 stmt := 'SELECT data_version FROM SDO_ROUTER_DATA_VERSION';
1333 EXECUTE IMMEDIATE stmt INTO data_version;
1334
1335 major_version := SUBSTR(data_version, 0, INSTR(data_version, '.')-1);
1336
1337 IF (INSTR(data_version, '.', 1, 4) = 0 OR
1338 (major_version!='12' AND major_version!='11' AND major_version!='10')) THEN
1339 log_message('ERROR: Routeserver data version table corrupted, ' ||
1340 'unsupported data version ' || data_version);
1341 raise_application_error(-20005,
1342 'Error getting data version, unsupported data version ' || data_version);
1343 END IF;
1344 END IF;
1345
1346 RETURN data_version;
1347 END get_version_info;
1348
1349 ---
1350 ---
1351 ---
1352 FUNCTION is_10g(version IN VARCHAR2)
1353 RETURN BOOLEAN
1354 IS
1355 BEGIN
1356 RETURN (SUBSTR(version, 0, INSTR(version, '.')-1) = '10');
1357 END;
1358
1359 --
1360 -- Entry point to used to create a network on the Router data
1361 --
1362 PROCEDURE create_router_network(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
1363 network_name IN VARCHAR2 := 'ROUTER_NETWORK')
1364 IS
1365 expression VARCHAR2(1024);
1366 full_file_name VARCHAR2(256);
1367 l_network_name VARCHAR2(256);
1368 link_view_name VARCHAR2(256);
1369 node_view_name VARCHAR2(256);
1370 part_view_name VARCHAR2(256);
1371 pblob_view_name VARCHAR2(256);
1372 stmt VARCHAR2(1024);
1373 BEGIN
1374 full_file_name := open_log_file(log_file_name);
1375
1376 -- Sanity check the passed in network name
1377 l_network_name := nls_upper(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(network_name));
1378
1379 -- Build the view names based on the network names
1380 link_view_name := l_network_name || '_LINK$';
1381 node_view_name := l_network_name || '_NODE$';
1382 part_view_name := l_network_name || '_PART$';
1383 pblob_view_name := l_network_name || '_PBLOB$';
1384
1385 log_message('INFO: creating the Routeserver network: ' || l_network_name);
1386
1387 -- if the network already exists, delete it first
1388 IF (network_exists(l_network_name) = 'TRUE') THEN
1392 -- create index for edge func_class
1389 delete_router_network(log_file_name, l_network_name, FALSE);
1390 END IF;
1391
1393 IF (index_exists('EDGE_FUNC_CLASS_IDX') = 'FALSE') THEN
1394 log_message(' creating function class index', FALSE);
1395 EXECUTE IMMEDIATE 'create index EDGE_FUNC_CLASS_IDX on EDGE (FUNC_CLASS)';
1396 END IF;
1397
1398 -- create a function based index for link level
1399 IF (index_exists('EDGE_LEVEL_IDX') = 'FALSE') THEN
1400 log_message(' creating link level index', FALSE);
1401 EXECUTE IMMEDIATE 'create index EDGE_LEVEL_IDX on EDGE(elocation_edge_link_level(FUNC_CLASS))';
1402 ELSE
1403 stmt := 'SELECT COLUMN_EXPRESSION FROM USER_IND_EXPRESSIONS WHERE INDEX_NAME = :name';
1404 EXECUTE IMMEDIATE stmt into expression using 'EDGE_LEVEL_IDX';
1405
1406 IF(substr(expression, 10, 25) <> 'ELOCATION_EDGE_LINK_LEVEL') THEN
1407 log_message(' dropping current link level index', FALSE);
1408 EXECUTE IMMEDIATE 'DROP INDEX EDGE_LEVEL_IDX';
1409
1410 log_message(' creating link level index', FALSE);
1411 EXECUTE IMMEDIATE 'create index EDGE_LEVEL_IDX on EDGE(elocation_edge_link_level(FUNC_CLASS))';
1412 END IF;
1413 END IF;
1414
1415 -- rebuild the table and index stats
1416 log_message('INFO: rebuild edge table statistics');
1417 gather_table_stats('EDGE');
1418
1419 log_message(' creating views', FALSE);
1420
1421 -- create a view on the NODE table
1422 stmt:=
1423 'create or replace view ' || node_view_name ||
1424 ' as select n.node_id node_id,
1425 n.geometry geometry,
1426 n.geometry.sdo_point.x x,
1427 n.geometry.sdo_point.y y
1428 from NODE n';
1429
1430 EXECUTE IMMEDIATE stmt;
1431
1432 -- create a view on the EDGE table
1433 stmt :=
1434 'create or replace view ' || link_view_name ||
1435 ' as select edge_id link_id,
1436 start_node_id start_node_id,
1437 end_node_id end_node_id,
1438 elocation_edge_link_level(FUNC_CLASS) link_level,
1439 length length,
1440 speed_limit s,
1441 func_class f,
1442 geometry geometry,
1443 name name,
1444 divider divider
1445 from EDGE';
1446
1447 EXECUTE IMMEDIATE stmt;
1448
1449 --create a view on the NODE table node_id and partition_id information
1450 stmt :=
1451 'create or replace view ' || part_view_name ||
1452 ' as select node_id node_id,
1453 partition_id partition_id,
1454 1 link_level
1455 from NODE';
1456
1457 EXECUTE IMMEDIATE stmt;
1458
1459 -- create a view on the PARTITION table adding link level and changing
1460 -- the format of the edge counts
1461 stmt := 'create or replace view ' || pblob_view_name ||
1462 ' as select link_level link_level,
1463 a.partition_id partition_id,
1464 subnetwork blob,
1465 num_nodes num_inodes,
1466 num_outgoing_boundary_edges+num_incoming_boundary_edges num_enodes,
1467 num_non_boundary_edges num_ilinks,
1468 num_outgoing_boundary_edges+num_incoming_boundary_edges num_elinks,
1469 num_incoming_boundary_edges num_inlinks,
1470 num_outgoing_boundary_edges num_outlinks, ' ||
1471 SYS.DBMS_ASSERT.ENQUOTE_LITERAL('Y') || ' user_data_included
1472 from
1473 (select 1 link_level, partition_id partition_id
1474 from PARTITION
1475 where partition_id > 0
1476 union all
1477 select 2 link_level, partition_id partition_id
1478 from PARTITION
1479 where partition_id = 0) a,
1480 PARTITION b
1481 where a.partition_id = b.partition_id';
1482
1483 EXECUTE IMMEDIATE stmt;
1484
1485 log_message(' generating metadata', FALSE);
1486
1487 --insert network metadata
1488 stmt := 'insert into USER_SDO_NETWORK_METADATA
1489 (network,
1490 network_category,
1491 geometry_type,
1492 node_table_name,
1493 node_geom_column,
1494 link_table_name,
1495 link_geom_column,
1496 link_cost_column,
1497 link_direction,
1498 partition_table_name,
1499 partition_blob_table_name,
1500 user_defined_data)
1501 values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)';
1502
1503 EXECUTE IMMEDIATE stmt USING l_network_name, 'SPATIAL', 'SDO_GEOMETRY',
1504 node_view_name, 'GEOMETRY', link_view_name, 'GEOMETRY', 'LENGTH',
1505 'DIRECTED', part_view_name, pblob_view_name, 'Y';
1506
1507 -- insert user data metadata
1508 -- node x coordinate
1509 stmt := 'insert into user_sdo_network_user_data
1510 (network, table_type, data_name,data_type)
1511 values (:1, :2, :3, :4)';
1512 EXECUTE IMMEDIATE stmt USING l_network_name, 'NODE', 'X', 'NUMBER';
1513
1514 -- node y coordinate
1515 stmt := 'insert into user_sdo_network_user_data
1516 (network, table_type, data_name, data_type)
1517 values (:1, :2, :3, :4)';
1521 stmt := 'insert into user_sdo_network_user_data
1518 EXECUTE IMMEDIATE stmt USING l_network_name, 'NODE', 'Y', 'NUMBER';
1519
1520 -- link speed limit
1522 (network, table_type, data_name, data_type)
1523 values (:1, :2, :3, :4)';
1524 EXECUTE IMMEDIATE stmt USING l_network_name, 'LINK', 'S', 'NUMBER';
1525
1526 -- link function class
1527 stmt := 'insert into user_sdo_network_user_data
1528 (network, table_type, data_name, data_type)
1529 values (:1, :2, :3, :4)';
1530 EXECUTE IMMEDIATE stmt USING l_network_name, 'LINK', 'F', 'NUMBER';
1531
1532 COMMIT;
1533
1534 utl_file.fclose(part_log_file);
1535 END;
1536
1537 --
1538 -- Entry point to used to create a network on the Router data
1539 --
1540 PROCEDURE delete_router_network(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
1541 network_name IN VARCHAR2 := 'ROUTER_NETWORK',
1542 log_delete IN BOOLEAN := TRUE)
1543 IS
1544 full_file_name VARCHAR2(256);
1545 l_network_name VARCHAR2(256);
1546 link_view_name VARCHAR2(256);
1547 node_view_name VARCHAR2(256);
1548 part_view_name VARCHAR2(256);
1549 pblob_view_name VARCHAR2(256);
1550 stmt VARCHAR2(512);
1551 BEGIN
1552 full_file_name := open_log_file(log_file_name);
1553 l_network_name := nls_upper(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(network_name));
1554
1555 -- raise an error if the network doesn't exist
1556 IF (network_exists(l_network_name) = 'FALSE') THEN
1557 log_message('ERROR: network delete failed, ' || l_network_name || ' network not found');
1558 raise_application_error(-20020, 'Network delete failed, ' || l_network_name || ' network not found');
1559 END IF;
1560
1561 IF (log_delete) THEN
1562 log_message('INFO: deleting the Routeserver network: ' || l_network_name);
1563 ELSE
1564 log_message('Ping');
1565 END IF;
1566
1567 stmt := 'select node_table_name, link_table_name,
1568 partition_table_name, partition_blob_table_name
1569 from USER_SDO_NETWORK_METADATA
1570 where NETWORK = :name';
1571
1572 EXECUTE IMMEDIATE stmt
1573 INTO node_view_name, link_view_name, part_view_name, pblob_view_name
1574 USING l_network_name;
1575
1576 -- cleanup metadata
1577 stmt := 'delete from USER_SDO_NETWORK_METADATA where NETWORK = :name';
1578 EXECUTE IMMEDIATE stmt using l_network_name;
1579
1580 stmt := 'delete from USER_SDO_NETWORK_USER_DATA where NETWORK = :name';
1581 EXECUTE IMMEDIATE stmt using l_network_name;
1582
1583 -- cleanup views
1584 IF (table_exists(link_view_name) = 'TRUE') THEN
1585 EXECUTE IMMEDIATE 'DROP VIEW ' ||
1586 SYS.DBMS_ASSERT.ENQUOTE_NAME(link_view_name);
1587 END IF;
1588
1589 IF (table_exists(node_view_name) = 'TRUE') THEN
1590 EXECUTE IMMEDIATE 'DROP VIEW ' ||
1591 SYS.DBMS_ASSERT.ENQUOTE_NAME(node_view_name);
1592 END IF;
1593
1594 IF (table_exists(part_view_name) = 'TRUE') THEN
1595 EXECUTE IMMEDIATE 'DROP VIEW ' ||
1596 SYS.DBMS_ASSERT.ENQUOTE_NAME(part_view_name);
1597 END IF;
1598
1599 IF (table_exists(pblob_view_name) = 'TRUE') THEN
1600 EXECUTE IMMEDIATE 'DROP VIEW ' ||
1601 SYS.DBMS_ASSERT.ENQUOTE_NAME(pblob_view_name);
1602 END IF;
1603
1604 COMMIT;
1605
1606 utl_file.fclose(part_log_file);
1607 END;
1608
1609 --
1610 -- Entry point and driver of the entire partitioning process.
1611 -- high level procedure for partitioning graph based on coordinate
1612 -- information (inertia bisecting). The parameters are defaulted
1613 -- so the customers don't have to worry about them.
1614 --
1615 PROCEDURE partition_router(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
1616 max_v_no IN NUMBER DEFAULT 10000,
1617 driving_side IN VARCHAR2 := 'R',
1618 network_name IN VARCHAR := 'ROUTER_NETWORK',
1619 max_memory IN NUMBER := 1.75,
1620 cleanup IN BOOLEAN DEFAULT TRUE,
1621 use_securefiles IN BOOLEAN DEFAULT TRUE,
1622 generate_11g_restrictions IN BOOLEAN DEFAULT TRUE)
1623 IS
1624 full_file_name VARCHAR2(256);
1625 max_memory_in_bytes NUMBER := (max_memory*1073741824);
1626 stmt VARCHAR2(256);
1627 msg_cleanup VARCHAR2(10) := 'TRUE';
1628 msg_blob_format VARCHAR2(16) := 'SECUREFILE';
1629 msg_restrictions VARCHAR2(10) := 'TRUE';
1630 BEGIN
1631 IF (NOT cleanup) THEN
1632 msg_cleanup := 'FALSE';
1633 END IF;
1634
1635 IF (NOT use_securefiles) THEN
1636 msg_blob_format := 'BASICFILE';
1637 END IF;
1638
1639 IF (NOT generate_11g_restrictions) THEN
1640 msg_restrictions := 'FALSE';
1641 END IF;
1642
1643 full_file_name := open_log_file(log_file_name);
1644
1645 -- Query the schema name for gathering statistics
1646 stmt := 'SELECT username FROM user_users';
1647 EXECUTE IMMEDIATE stmt into schema_name;
1648
1649 log_message('******** Beginning SDO Router partitioning');
1650 log_message('** Schema: ' || schema_name, FALSE);
1651 log_message('** Logfile location: ' || full_file_name, FALSE);
1652 log_message('** Nodes per partition: ' || max_v_no, FALSE);
1656 log_message('** Cleanup temporary files: ' || msg_cleanup, FALSE);
1653 log_message('** Driving side: ' || driving_side, FALSE);
1654 log_message('** Router network name: ' || network_name, FALSE);
1655 log_message('** Max JVM Memory Size: ' || max_memory || 'GB (' || max_memory_in_bytes || ' bytes)', FALSE);
1657 log_message('** BLOBs stored in ' || msg_blob_format || ' format' , FALSE);
1658 log_message('** Generating 11g turn restrictions: ' || msg_restrictions, FALSE);
1659
1660 setup_tables;
1661
1662 create_node_part;
1663
1664 graph_partition('NODE_PART', max_v_no, TRUE);
1665
1666 create_super_tables;
1667
1668 IF (generate_11g_restrictions) THEN
1669 -- Table of nodes that are either the start or end node of a restricted edge.
1670 create_restricted_nodes;
1671
1672 -- Table of edges that that have either a start or end node in the
1673 -- restricted_nodes table. We store the first and last segment of each edge.
1674 -- These segments are far more accurate than the edge as a whole for
1675 -- computing turn angles.
1676 create_restricted_edges;
1677 END IF;
1678
1679 -- Close the log file so the Java code can use it
1680 utl_file.fclose(part_log_file);
1681
1682 -- Adjust the Oracle JVM maximum memory size to 1.5g.
1683 -- This is the equivelent to specifying -Xmx1536m to
1684 -- java outside the database. Memory size is specified in bytes.
1685 ElocationSetJVMHeapSize(max_memory_in_bytes);
1686
1687 IF (generate_11g_restrictions) THEN
1688 -- Generate the 11g turn restrictions for compatability.
1689 -- This MUST be done after the NODE_PART and EDGE_PART tables have
1690 -- been populated and before the creation of the partition table.
1691 build_turn_restrictions(full_file_name, driving_side);
1692 END IF;
1693
1694 BEGIN
1695 -- Java code to create the partiton table
1696 elocation_partition_router(full_file_name, msg_blob_format);
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
1700 log_message(SQLERRM);
1701 utl_file.fclose(part_log_file);
1702 RAISE JAVA_ERROR;
1703 END;
1704
1705 -- Reopen the logfile
1706 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
1707
1708 log_message('INFO: creating the final partition table');
1709
1710 -- Rename the new partition table and build an index on it.
1711 IF (table_exists('partition')= 'TRUE') THEN
1712 EXECUTE IMMEDIATE 'DROP TABLE partition';
1713 END IF;
1714
1715 EXECUTE IMMEDIATE 'RENAME new_partition to partition';
1716
1717 log_message('INFO: create index partition_p_idx on partition table');
1718 EXECUTE IMMEDIATE 'CREATE INDEX partition_p_idx on partition(partition_id)';
1719
1720 log_message('INFO: gather partition table statistics');
1721 gather_table_stats('PARTITION');
1722
1723 -- Create the indexes, views and metadata needed by the NDM on top of
1724 -- the Router data
1725 create_router_network(log_file_name, network_name);
1726
1727 -- Generate user data based turn restrictions.
1728 -- This must be done after the NODE_PART and EDGE_PART tables
1729 create_turn_restriction_data(log_file_name, cleanup);
1730
1731 -- If trucking user data exists, partition it.
1732 IF (table_exists('router_transport') = 'TRUE') THEN
1733 create_trucking_data(log_file_name, TRUE);
1734 END IF;
1735
1736 -- Reopen the logfile
1737 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
1738 clean_tables(cleanup);
1739 log_message('******** Completed SDO Router partitioning');
1740
1741 -- Close the log file
1742 utl_file.fclose(part_log_file);
1743 EXCEPTION
1744 WHEN JAVA_ERROR THEN
1745 raise_application_error(-20000, 'Oracle Router partitioning failed');
1746 WHEN OTHERS THEN
1747 IF (utl_file.is_open(part_log_file) = FALSE) THEN
1748 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
1749 END IF;
1750 log_message(SQLERRM);
1751 utl_file.fclose(part_log_file);
1752 raise_application_error(-20000, 'Oracle Router partitioning failed');
1753 END partition_router;
1754
1755 --
1756 -- Entry point to used to produce a dump of the partition BLOBs
1757 --
1758 PROCEDURE dump_partitions(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
1759 start_pid IN NUMBER DEFAULT 0,
1760 end_pid IN NUMBER DEFAULT -1,
1761 verbose IN BOOLEAN DEFAULT FALSE)
1762 IS
1763 full_file_name VARCHAR2(256);
1764 l_end_pid NUMBER := end_pid;
1765 max_pid NUMBER;
1766 stmt VARCHAR2(256);
1767 version VARCHAR2(32);
1768 BEGIN
1769 full_file_name := open_log_file(log_file_name);
1770
1771 -- Make sure the table is actually there
1772 IF (table_exists('PARTITION') = 'FALSE' ) THEN
1773 log_message('ERROR: Partition dump failed, PARTITION table not found');
1774 utl_file.fclose(part_log_file);
1775
1776 RAISE PARAMETER_ERROR;
1777 END IF;
1778
1779 stmt := 'SELECT MAX(PARTITION_ID) FROM PARTITION';
1780 EXECUTE IMMEDIATE stmt INTO max_pid;
1781
1782 -- The default value for the end partition id is max(partition_id)
1786
1783 IF (l_end_pid = -1) THEN
1784 l_end_pid := max_pid;
1785 END IF;
1787 -- Validate the starting partition id.
1788 IF ((start_pid < 0) OR (start_pid > max_pid)) THEN
1789 log_message('ERROR: Invald Start Partition ID '||start_pid||', Valid Range (0,'||max_pid||')');
1790 utl_file.fclose(part_log_file);
1791
1792 RAISE PARAMETER_ERROR;
1793 END IF;
1794
1795 -- Validate the ending partition id.
1796 IF ((l_end_pid < start_pid) OR (l_end_pid > max_pid) OR (l_end_pid < 0)) THEN
1797 log_message('ERROR: Invald End Partition ID '||sdo_util.number_to_char(end_pid)||', Valid Range ('||start_pid||','||max_pid||')');
1798 utl_file.fclose(part_log_file);
1799
1800 RAISE PARAMETER_ERROR;
1801 END IF;
1802
1803 version := get_version_info();
1804 log_message('******** Beginning partition dump');
1805 log_message('** Logfile location: ' || full_file_name, FALSE);
1806 log_message('** Routeserver data version: ' || version, FALSE);
1807 log_message('** Start partition id: ' || start_pid, FALSE);
1808 log_message('** End partition id: ' || l_end_pid, FALSE);
1809 IF (verbose) THEN
1810 log_message('** Verbose mode: TRUE', FALSE);
1811 ELSE
1812 log_message('** Verbose mode: FALSE', FALSE);
1813 END IF;
1814
1815 log_message('', FALSE);
1816
1817 -- Close the log file so the Java code can use it
1818 utl_file.fclose(part_log_file);
1819
1820 elocation_dump_partition(full_file_name, start_pid, l_end_pid,
1821 verbose, is_10g(version));
1822
1823 EXCEPTION
1824 WHEN PARAMETER_ERROR THEN
1825 raise_application_error(-20004, 'Error, partition dump failed, see log file.');
1826 WHEN OTHERS THEN
1827 IF (utl_file.is_open(part_log_file) = FALSE) THEN
1828 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
1829 END IF;
1830 log_message(SQLERRM);
1831 utl_file.fclose(part_log_file);
1832 raise_application_error(-20004, 'Error, partition dump failed, see log file.');
1833 END dump_partitions;
1834
1835 --
1836 -- Entry point to used to validate the partition BLOBs
1837 --
1838 PROCEDURE validate_partitions(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
1839 start_pid IN NUMBER DEFAULT 0,
1840 end_pid IN NUMBER DEFAULT -1,
1841 verbose IN BOOLEAN DEFAULT FALSE)
1842 IS
1843 full_file_name VARCHAR2(256);
1844 l_end_pid NUMBER := end_pid;
1845 max_pid NUMBER;
1846 stmt VARCHAR2(256);
1847 version VARCHAR2(32);
1848 BEGIN
1849 full_file_name := open_log_file(log_file_name);
1850
1851 -- Make sure the table is actually there
1852 IF (table_exists('PARTITION') = 'FALSE' ) THEN
1853 log_message('ERROR: Partition validate failed, PARTITION table not found');
1854 utl_file.fclose(part_log_file);
1855
1856 RAISE PARAMETER_ERROR;
1857 END IF;
1858
1859 stmt := 'SELECT MAX(PARTITION_ID) FROM PARTITION';
1860 EXECUTE IMMEDIATE stmt INTO max_pid;
1861
1862 -- The default value for the end partition id is max(partition_id)
1863 IF (l_end_pid = -1) THEN
1864 l_end_pid := max_pid;
1865 END IF;
1866
1867 -- Validate the starting partition id.
1868 IF ((start_pid < 0) OR (start_pid > max_pid)) THEN
1869 log_message('ERROR: Invald Start Partition ID '||start_pid||
1870 ', Valid Range (0,'||max_pid||')');
1871 utl_file.fclose(part_log_file);
1872
1873 RAISE PARAMETER_ERROR;
1874 END IF;
1875
1876 -- Validate the ending partition id.
1877 IF ((l_end_pid < start_pid) OR (l_end_pid > max_pid) OR (l_end_pid < 0)) THEN
1878 log_message('ERROR: Invald End Partition ID '||sdo_util.number_to_char(end_pid)||
1879 ', Valid Range ('||start_pid||','||max_pid||')');
1880 utl_file.fclose(part_log_file);
1881
1882 RAISE PARAMETER_ERROR;
1883 END IF;
1884
1885 version := get_version_info();
1886 log_message('******** Beginning partition validation');
1887 log_message('** Logfile location: ' || full_file_name, FALSE);
1888 log_message('** Routeserver data version: ' || version, FALSE);
1889
1890 log_message('** Start partition id: ' || start_pid, FALSE);
1891 log_message('** End partition id: ' || l_end_pid, FALSE);
1892 IF (verbose) THEN
1893 log_message('** Verbose mode: TRUE', FALSE);
1894 ELSE
1895 log_message('** Verbose mode: FALSE', FALSE);
1896 END IF;
1897 log_message('', FALSE);
1898
1899 -- Close the log file so the Java code can use it
1900 utl_file.fclose(part_log_file);
1901
1902 -- Adjust the Oracle JVM maximum memory size to 800M.
1903 -- This is the equivelent to specifying -Xmx800m to
1904 -- java outside the database. Memory size is specified in bytes.
1905 ElocationSetJVMHeapSize(838860800);
1906
1907 elocation_validate_partition(full_file_name, start_pid, l_end_pid,
1908 verbose, is_10g(version));
1909
1910 EXCEPTION
1911 WHEN PARAMETER_ERROR THEN
1912 raise_application_error(-20003, 'Error, partition validation failed, see log file.');
1913 WHEN OTHERS THEN
1914 IF (utl_file.is_open(part_log_file) = FALSE) THEN
1915 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
1916 END IF;
1917 log_message(SQLERRM);
1918 utl_file.fclose(part_log_file);
1919 raise_application_error(-20003, 'Error, partition validation failed, see log file.');
1920 END;
1921
1922
1923 ---
1924 --- Entry point to partition the Routers trucking data
1925 ---
1926 PROCEDURE create_trucking_data(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
1927 cleanup IN BOOLEAN DEFAULT TRUE)
1928 IS
1929 TYPE CURSOR_TYPE IS REF CURSOR;
1930 full_file_name VARCHAR2(256);
1931 stmt VARCHAR2(256);
1932 BEGIN
1933 full_file_name := open_log_file(log_file_name);
1934
1935 log_message('******** Begin generation of trucking user data');
1936 log_message('** Logfile location: ' || full_file_name, FALSE);
1937
1938 -- Make sure the raw truck data exists and is in Router format
1939 IF (table_exists('router_transport') = 'FALSE') THEN
1940 log_message('ERROR: ROUTER_TRANSPORT table not found');
1941 RETURN ;
1942 END IF;
1943
1944 -- Make sure the Edge table exists
1945 IF (table_exists('EDGE') = 'FALSE') THEN
1946 log_message('ERROR: EDGE table not found');
1947 RETURN ;
1948 END IF;
1949
1950 -- Make sure the data version table exists
1951 IF (table_exists('sdo_router_data_version') = 'FALSE') THEN
1952 log_message('ERROR: SDO_ROUTER_DATA_VERSION table not found');
1953 RETURN ;
1954 END IF;
1955
1956 -- Cleanup the old intermediate truck data partitioning table
1957 IF (table_exists('router_partitioned_truck_data') = 'TRUE') THEN
1958 execute immediate 'DROP TABLE router_partitioned_truck_data';
1959 END IF;
1960
1961 -- Cleanup the intermediate truck data partitioning table
1962 IF (table_exists('partitioned_router_transport') = 'TRUE') THEN
1963 execute immediate 'DROP TABLE partitioned_router_transport';
1964 END IF;
1965
1966 -- Create truck data intermediate partitioning table
1967 stmt := 'CREATE TABLE partitioned_router_transport PARALLEL UNRECOVERABLE AS
1968 SELECT r.edge_id, partition_id, maintype, subtype, value
1969 FROM edge e, router_transport r
1970 WHERE e.edge_id=r.edge_id';
1971
1972 EXECUTE IMMEDIATE stmt;
1973
1974 -- Add highway edges to partition 0, the highway partition
1975 stmt := 'INSERT /*+ APPEND */ INTO partitioned_router_transport
1976 SELECT edge_id, 0, maintype, subtype, value
1977 FROM router_transport
1978 WHERE edge_id IN
1979 (SELECT edge_id FROM edge WHERE func_class=1 OR func_class=2)';
1980
1981 EXECUTE IMMEDIATE stmt;
1982
1983 COMMIT;
1984
1985 -- create an index on the partitioned datas partition_id field
1986 EXECUTE IMMEDIATE 'CREATE INDEX prt_p_idx ON partitioned_router_transport(partition_id)';
1987
1988 -- Close the log file so the Java code can use it
1989 utl_file.fclose(part_log_file);
1990
1991 -- Adjust the Oracle JVM maximum memory size to 800M.
1992 -- This is the equivelent to specifying -Xmx800m to
1993 -- java outside the database. Memory size is specified in bytes.
1994 ElocationSetJVMHeapSize(838860800);
1995
1996 BEGIN
1997 -- Java code to partition the trucking data
1998 elocation_trucking_data(full_file_name);
1999 EXCEPTION
2000 WHEN OTHERS THEN
2001 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2002 log_message(SQLERRM);
2003 utl_file.fclose(part_log_file);
2004 RAISE JAVA_ERROR;
2005 END;
2006
2007 -- Reopen the logfile
2008 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2009
2010 log_message('INFO: creating the final trucking user data table');
2011
2012 -- Rename the new partition table and build an index on it.
2013 IF (table_exists('trucking_user_data')= 'TRUE') THEN
2014 EXECUTE IMMEDIATE 'DROP TABLE trucking_user_data';
2015 END IF;
2016
2017 -- Rename the new partition table and build an index on it.
2018 IF (table_exists('router_trucking_data')= 'TRUE') THEN
2019 EXECUTE IMMEDIATE 'DROP TABLE router_trucking_data';
2020 END IF;
2021
2022 EXECUTE IMMEDIATE 'RENAME new_trucking_data to router_trucking_data';
2023
2024 log_message('INFO: create index rtud_p_idx on router_trucking_data table');
2025 EXECUTE IMMEDIATE 'CREATE INDEX rtud_p_idx on router_trucking_data(partition_id)';
2026
2027 -- Trucking specific intermediate table cleanup
2028 IF (cleanup) THEN
2029 IF (table_exists('partitioned_router_transport') = 'TRUE') THEN
2030 EXECUTE IMMEDIATE 'DROP TABLE partitioned_router_transport';
2031 END IF;
2032 IF (table_exists('new_trucking_data') = 'TRUE') THEN
2033 EXECUTE IMMEDIATE 'DROP TABLE new_trucking_data';
2034 END IF;
2035 END IF;
2036
2037 log_message('******** Completed generation of trucking user data ');
2038
2039 -- Close the log file
2040 utl_file.fclose(part_log_file);
2041 EXCEPTION
2042 WHEN JAVA_ERROR THEN
2046 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2043 raise_application_error(-20015, 'Oracle Router trucking user data generation failed');
2044 WHEN OTHERS THEN
2045 IF (utl_file.is_open(part_log_file) = FALSE) THEN
2047 END IF;
2048 log_message(SQLERRM);
2049 utl_file.fclose(part_log_file);
2050 raise_application_error(-20015, 'Oracle Router trucking user data generation failed');
2051 END create_trucking_data;
2052
2053 --
2054 -- Entry point to used to produce a dump of the trucking data BLOBs
2055 --
2056 PROCEDURE dump_trucking_data(
2057 log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
2058 start_pid IN NUMBER DEFAULT 0,
2059 end_pid IN NUMBER DEFAULT -1,
2060 skip_unsupported IN BOOLEAN DEFAULT TRUE)
2061 IS
2062 full_file_name VARCHAR2(256);
2063 l_end_pid NUMBER := end_pid;
2064 max_pid NUMBER;
2065 stmt VARCHAR2(256);
2066 version VARCHAR2(32);
2067 BEGIN
2068 full_file_name := open_log_file(log_file_name);
2069
2070 -- Make sure the table is actually there
2071 IF (table_exists('ROUTER_TRUCKING_DATA') = 'FALSE' ) THEN
2072 log_message('ERROR: Trucking data dump failed, ROUTER_TRUCKING_DATA table not found');
2073 utl_file.fclose(part_log_file);
2074
2075 RAISE PARAMETER_ERROR;
2076 END IF;
2077
2078 stmt := 'SELECT MAX(PARTITION_ID) FROM ROUTER_TRUCKING_DATA';
2079 EXECUTE IMMEDIATE stmt INTO max_pid;
2080
2081 -- The default value for the end partition id is max(partition_id)
2082 IF (l_end_pid = -1) THEN
2083 l_end_pid := max_pid;
2084 END IF;
2085
2086 -- Validate the starting partition id.
2087 IF ((start_pid < 0) OR (start_pid > max_pid)) THEN
2088 log_message('ERROR: Invald Start Partition ID '||start_pid||', Valid Range (0,'||max_pid||')');
2089 utl_file.fclose(part_log_file);
2090
2091 RAISE PARAMETER_ERROR;
2092 END IF;
2093
2094 -- Validate the ending partition id.
2095 IF ((l_end_pid < start_pid) OR (l_end_pid > max_pid) OR (l_end_pid < 0)) THEN
2096 log_message('ERROR: Invald End Partition ID '||sdo_util.number_to_char(end_pid)||', Valid Range ('||start_pid||','||max_pid||')');
2097 utl_file.fclose(part_log_file);
2098
2099 RAISE PARAMETER_ERROR;
2100 END IF;
2101
2102 version := get_version_info();
2103 log_message('******** Beginning trucking data dump');
2104 log_message('** Logfile location: ' || full_file_name, FALSE);
2105 log_message('** Routeserver data version: ' || version, FALSE);
2106 log_message('** Start partition id: ' || start_pid, FALSE);
2107 log_message('** End partition id: ' || l_end_pid, FALSE);
2108
2109 log_message('', FALSE);
2110
2111 -- Close the log file so the Java code can use it
2112 utl_file.fclose(part_log_file);
2113
2114 elocation_dump_trucking_data(full_file_name, start_pid, l_end_pid, skip_unsupported);
2115
2116 EXCEPTION
2117 WHEN PARAMETER_ERROR THEN
2118 raise_application_error(-20004, 'Error, trucking data dump failed, see log file.');
2119 WHEN OTHERS THEN
2120 IF (utl_file.is_open(part_log_file) = FALSE) THEN
2121 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2122 END IF;
2123 log_message(SQLERRM);
2124 utl_file.fclose(part_log_file);
2125 raise_application_error(-20004, 'Error, trucking data dump failed, see log file.');
2126 END dump_trucking_data;
2127
2128 ---
2129 --- Entry point to partition the Routers turn restriction data
2130 ---
2131 PROCEDURE create_turn_restriction_data(log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
2132 cleanup IN BOOLEAN DEFAULT TRUE)
2133 IS
2134 TYPE CURSOR_TYPE IS REF CURSOR;
2135 full_file_name VARCHAR2(256);
2136 stmt VARCHAR2(512);
2137 BEGIN
2138 full_file_name := open_log_file(log_file_name);
2139
2140 log_message('******** Begin generation of turn restriction user data');
2141 log_message('** Logfile location: ' || full_file_name, FALSE);
2142
2143 -- Make sure the raw turn restriction data exists
2144 IF (table_exists('ROUTER_NAV_STRAND') = 'FALSE') THEN
2145 log_message('ERROR: ROUTER_NAV_STRAND table not found');
2146 RETURN ;
2147 END IF;
2148
2149 -- Make sure the Edge table exists.
2150 IF (table_exists('EDGE') = 'FALSE') THEN
2151 log_message('ERROR: EDGE table not found');
2152 RETURN ;
2153 END IF;
2154
2155 -- Make sure the data version table exists
2156 IF (table_exists('SDO_ROUTER_DATA_VERSION') = 'FALSE') THEN
2157 log_message('ERROR: SDO_ROUTER_DATA_VERSION table not found');
2158 RETURN ;
2159 END IF;
2160
2161 -- Make sure the router_condition table exists
2162 IF (table_exists('ROUTER_CONDITION') = 'FALSE') THEN
2166
2163 log_message('ERROR: ROUTER_CONDITION table not found');
2164 RETURN ;
2165 END IF;
2167 -- Drop the intermediate partitioned turn restriction table
2168 IF (table_exists('PARTITIONED_ROUTER_NAV_STRAND') = 'TRUE') THEN
2169 execute immediate 'DROP TABLE partitioned_router_nav_strand';
2170 END IF;
2171
2172 -- Create the intermediate restricted driving maneuvers partitioning table
2173 stmt := 'CREATE TABLE partitioned_router_nav_strand PARALLEL UNRECOVERABLE AS
2174 SELECT nav_strand_id, seq_num, link_id, node_id, partition_id
2175 FROM edge, router_nav_strand
2176 WHERE edge_id=link_id';
2177
2178 EXECUTE IMMEDIATE stmt;
2179
2180 -- Add highway edges to partition 0, the highway partition
2181 stmt := 'INSERT /*+ APPEND */ INTO partitioned_router_nav_strand
2182 SELECT nav_strand_id, seq_num, link_id, node_id, 0
2183 FROM router_nav_strand
2184 WHERE link_id IN
2185 (SELECT edge_id FROM edge WHERE func_class=1 OR func_class=2)';
2186
2187 EXECUTE IMMEDIATE stmt;
2188
2189 COMMIT;
2190
2191 -- create an index on the partitioned datas partition_id field
2192 EXECUTE IMMEDIATE 'CREATE INDEX prns_pi_idx ON
2193 partitioned_router_nav_strand(partition_id) PARALLEL (DEGREE 4)';
2194 EXECUTE IMMEDIATE 'CREATE INDEX prns_li_idx ON
2195 partitioned_router_nav_strand(link_id) PARALLEL (DEGREE 4)';
2196 EXECUTE IMMEDIATE 'CREATE INDEX prns_nsi_sn_idx ON
2197 partitioned_router_nav_strand(nav_strand_id, seq_num) PARALLEL (DEGREE 4)';
2198
2199 -- Drop the current look up table if it exists
2200 IF (table_exists('router_max_seq') = 'TRUE') THEN
2201 execute immediate 'DROP TABLE router_max_seq';
2202 END IF;
2203
2204 -- create a lookup table to associate the max sequence number to a strand id
2205 stmt := 'CREATE TABLE router_max_seq
2206 (nav_strand_id, max_seq_num,
2207 CONSTRAINT pk_max_seq PRIMARY KEY(nav_strand_id))
2208 ORGANIZATION INDEX
2209 PARALLEL UNRECOVERABLE AS
2210 SELECT nav_strand_id, max(seq_num)
2211 FROM router_nav_strand GROUP BY nav_strand_id';
2212
2213 EXECUTE IMMEDIATE stmt;
2214
2215 -- Close the log file so the Java code can use it
2216 utl_file.fclose(part_log_file);
2217
2218 -- Adjust the Oracle JVM maximum memory size to 800M.
2219 -- This is the equivelent to specifying -Xmx800m to
2220 -- java outside the database. Memory size is specified in bytes.
2221 ElocationSetJVMHeapSize(838860800);
2222
2223 BEGIN
2224 -- Java code to partition the restricted driving maneuver data
2225 elocation_turn_restrict_data(full_file_name);
2226 EXCEPTION
2227 WHEN OTHERS THEN
2228 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2229 log_message(SQLERRM);
2230 utl_file.fclose(part_log_file);
2231 RAISE JAVA_ERROR;
2232 END;
2233
2234 -- Reopen the logfile
2235 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2236
2237 log_message('INFO: creating the final turn restriction user data table');
2241 EXECUTE IMMEDIATE 'DROP TABLE router_turn_restriction_data';
2238
2239 -- Rename the new partition table and build an index on it.
2240 IF (table_exists('router_turn_restriction_data')= 'TRUE') THEN
2242 END IF;
2243
2244 EXECUTE IMMEDIATE
2245 'RENAME new_turn_restriction_data to router_turn_restriction_data';
2246
2247 log_message('INFO: create index rtrud_p_idx on router_turn_restriction_data table');
2248 EXECUTE IMMEDIATE 'CREATE INDEX rtrud_p_idx on router_turn_restriction_data(partition_id)';
2249
2250 -- Turn restriction specific intermediate table cleanup
2251 IF (cleanup) THEN
2252 IF (table_exists('new_turn_restriction_data') = 'TRUE') THEN
2253 EXECUTE IMMEDIATE 'DROP TABLE new_turn_restriction_data';
2254 END IF;
2255 IF (table_exists('partitioned_router_nav_strand') = 'TRUE') THEN
2256 EXECUTE IMMEDIATE 'DROP TABLE partitioned_router_nav_strand';
2257 END IF;
2258 IF (table_exists('router_max_seq') = 'TRUE') THEN
2259 EXECUTE IMMEDIATE 'DROP TABLE router_max_seq';
2260 END IF;
2261 END IF;
2262
2263 log_message('******** Completed generation of turn restriction user data ');
2264
2265 -- Close the log file
2266 utl_file.fclose(part_log_file);
2267 EXCEPTION
2268 WHEN JAVA_ERROR THEN
2269 raise_application_error(-20015, 'Oracle Router turn restriction user data generation failed');
2270 WHEN OTHERS THEN
2271 IF (utl_file.is_open(part_log_file) = FALSE) THEN
2272 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2273 END IF;
2274 log_message(SQLERRM);
2275 utl_file.fclose(part_log_file);
2276 raise_application_error(-20015, 'Oracle Router turn restriction user data generation failed');
2277 END create_turn_restriction_data;
2278
2279 --
2280 -- Entry point to used to produce a dump of the turn restriction BLOBs
2281 --
2282 PROCEDURE dump_turn_restriction_data(
2283 log_file_name IN VARCHAR2 := 'sdo_router_partition.log',
2284 start_pid IN NUMBER DEFAULT 0,
2285 end_pid IN NUMBER DEFAULT -1,
2286 dump_soft_restrictions IN BOOLEAN DEFAULT FALSE)
2287 IS
2288 full_file_name VARCHAR2(256);
2289 l_end_pid NUMBER := end_pid;
2290 max_pid NUMBER;
2291 stmt VARCHAR2(256);
2292 version VARCHAR2(32);
2293 BEGIN
2294 full_file_name := open_log_file(log_file_name);
2295
2296 -- Make sure the table is actually there
2297 IF (table_exists('ROUTER_TURN_RESTRICTION_DATA') = 'FALSE' ) THEN
2298 log_message('ERROR: Turn restriction dump failed, ROUTER_TURN_RESTRICTION_DATA table not found');
2299 utl_file.fclose(part_log_file);
2300
2301 RAISE PARAMETER_ERROR;
2302 END IF;
2303
2304 stmt := 'SELECT MAX(PARTITION_ID) FROM ROUTER_TURN_RESTRICTION_DATA';
2305 EXECUTE IMMEDIATE stmt INTO max_pid;
2306
2307 -- The default value for the end partition id is max(partition_id)
2308 IF (l_end_pid = -1) THEN
2309 l_end_pid := max_pid;
2310 END IF;
2311
2312 -- Validate the starting partition id.
2313 IF ((start_pid < 0) OR (start_pid > max_pid)) THEN
2314 log_message('ERROR: Invald Start Partition ID '||start_pid||', Valid Range (0,'||max_pid||')');
2315 utl_file.fclose(part_log_file);
2316
2317 RAISE PARAMETER_ERROR;
2318 END IF;
2319
2320 -- Validate the ending partition id.
2321 IF ((l_end_pid < start_pid) OR (l_end_pid > max_pid) OR (l_end_pid < 0)) THEN
2322 log_message('ERROR: Invald End Partition ID '||sdo_util.number_to_char(end_pid)||', Valid Range ('||start_pid||','||max_pid||')');
2323 utl_file.fclose(part_log_file);
2324
2325 RAISE PARAMETER_ERROR;
2326 END IF;
2327
2328 version := get_version_info();
2332 log_message('** Start partition id: ' || start_pid, FALSE);
2329 log_message('******** Beginning turn restriction dump');
2330 log_message('** Logfile location: ' || full_file_name, FALSE);
2331 log_message('** Routeserver data version: ' || version, FALSE);
2333 log_message('** End partition id: ' || l_end_pid, FALSE);
2334 IF (dump_soft_restrictions) THEN
2335 log_message('** Dumping soft restrictions: TRUE', FALSE);
2336 ELSE
2337 log_message('** Dumping soft restrictions: FALSE', FALSE);
2338 END IF;
2339
2340 log_message('', FALSE);
2341
2342 -- Close the log file so the Java code can use it
2343 utl_file.fclose(part_log_file);
2344
2345 elocation_dump_turn_restrict(full_file_name, start_pid, l_end_pid, dump_soft_restrictions);
2346
2347 EXCEPTION
2348 WHEN PARAMETER_ERROR THEN
2349 raise_application_error(-20004, 'Error, turn restrictions dump failed, see log file.');
2350 WHEN OTHERS THEN
2351 IF (utl_file.is_open(part_log_file) = FALSE) THEN
2352 part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A');
2353 END IF;
2354 log_message(SQLERRM);
2355 utl_file.fclose(part_log_file);
2356 raise_application_error(-20004, 'Error, turn restriction dump failed, see log file.');
2357 END dump_turn_restriction_data;
2358
2359 --
2360 -- Entry point to cleanup tables used for debugging
2361 --
2362 PROCEDURE cleanup_router(all_tables IN BOOLEAN)
2363 IS
2364 BEGIN
2365 -- Cleanup all temporary tables.
2366 clean_tables(all_tables);
2367 END;
2368
2369 --
2370 -- Entry point to used to get the Router data version
2371 --
2372 PROCEDURE get_version(log_file_name IN VARCHAR2 := 'sdo_router_partition.log')
2373 IS
2374 data_version VARCHAR2(32);
2375 full_file_name VARCHAR2(256);
2376 BEGIN
2377 full_file_name := open_log_file(log_file_name);
2378
2379 data_version := get_version_info();
2380
2381 dbms_output.put_line('Routeserver: data version '|| data_version);
2382 log_message('INFO: Routeserver data version: ' || data_version);
2383
2384 utl_file.fclose(part_log_file);
2385 END;
2386
2387 --
2388 -- main pl/sql procedure to partition a graph with coordinate information
2389 -- for recovering with
2390 --
2391 PROCEDURE recover_graph_partition(p_tab_name IN VARCHAR2,
2392 min_pid IN NUMBER,
2393 max_pid IN NUMBER,
2394 p_level IN NUMBER,
2395 max_v_no IN NUMBER,
2396 make_equal IN BOOLEAN)
2397 IS
2398 stmt VARCHAR2(256);
2399 v_no NUMBER;
2400 pid NUMBER;
2401 p_counter NUMBER;
2402 tmp_p_tab_name VARCHAR2(32);
2403 BEGIN
2404 p_counter := max_pid+1; -- starting partition counter
2405 pid := min_pid;
2406
2407 -- start logging
2408 log_message('INFO: starting recovery of '|| p_tab_name || ' partitioning' ||
2409 ' partition level:' || p_level || ' min(partition id):' || min_pid ||
2410 ' max(partition id)' || max_pid);
2411
2412 FOR k IN min_pid..max_pid LOOP
2413 FOR i IN 0..p_level LOOP
2414 FOR j IN 1..power(2,i) LOOP
2415 tmp_p_tab_name := p_tab_name || '_' || pid;
2416
2417 IF (table_exists(tmp_p_tab_name) = 'TRUE' ) THEN
2418 new_partition_proc(p_tab_name, max_v_no,pid,make_equal,p_counter);
2419
2420 log_message('INFO: partitioning '|| p_tab_name ||
2421 ' level: ' || j || ' partition id: ' || pid);
2422 ELSE
2423 p_counter := p_counter + 2;
2424 END IF;
2425
2426 pid := pid +1;
2427 end loop;
2428 end loop;
2429 end loop;
2430
2431 -- copy the result back to original table and ajust the pids
2432 adjust_final_pid(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name));
2433
2434 log_message('INFO: completed recovery of '|| p_tab_name || ' partitioning');
2435
2436 EXCEPTION
2437 WHEN OTHERS THEN
2438 log_message(SQLERRM);
2439 log_message('ERROR: exception recovering partition '|| pid ||
2440 ' of the ' || p_tab_name || ' table');
2441 raise_application_error(-20010, 'Error Recovering Graph Partitioning');
2442 END recover_graph_partition;
2443
2444 --
2445 -- Unused procedures and functions to delete
2446 --
2447 --
2448
2449 --
2450 -- End unused procedures and functions
2451 --
2452
2453 END SDO_ROUTER_PARTITION;
[Home] [Help]