Untitled
unknown
sql
9 days ago
2.5 kB
2
Indexable
-- PackageDestinations Table Creation (Simulation) CREATE OR REPLACE PROCEDURE simulate_dummy_package_destinations AS -- Variables v_batch_size INT := 100000; -- Collections for bulk insert TYPE t_package_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_destination_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; l_package_ids t_package_ids; l_destination_ids t_destination_ids; -- Preloaded PackageIDs and DestinationIDs package_ids t_package_ids; destination_ids t_destination_ids; -- Timing variables start_time NUMBER; end_time NUMBER; elapsed_time NUMBER; BEGIN -- Record the start time start_time := DBMS_UTILITY.GET_TIME; -- Preload all PackageIDs into memory SELECT PackageID BULK COLLECT INTO package_ids FROM TravelPackages; -- Preload all DestinationIDs into memory SELECT DestinationID BULK COLLECT INTO destination_ids FROM Destinations; -- Check if data exists IF package_ids.COUNT = 0 OR destination_ids.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No PackageIDs or DestinationIDs found in the respective tables.'); RETURN; END IF; -- Loop to create 100,000 PackageDestinations FOR batch IN 1..100 LOOP -- Generate v_batch_size records FOR i IN 1..v_batch_size LOOP -- Generate random PackageID and DestinationID l_package_ids(i) := package_ids(TRUNC(DBMS_RANDOM.VALUE(1, package_ids.COUNT + 1))); l_destination_ids(i) := destination_ids(TRUNC(DBMS_RANDOM.VALUE(1, destination_ids.COUNT + 1))); END LOOP; -- Simulate progress report DBMS_OUTPUT.PUT_LINE('Simulated batch ' || batch || ' of 100 (' || (batch * v_batch_size) || ' records)'); -- Commented out actual bulk insert /* FORALL i IN l_package_ids.FIRST..l_package_ids.LAST INSERT INTO PackageDestinations ( PackageID, DestinationID ) VALUES ( l_package_ids(i), l_destination_ids(i) ); -- Commit each batch COMMIT; */ END LOOP; -- Record the end time end_time := DBMS_UTILITY.GET_TIME; -- Calculate elapsed time in seconds elapsed_time := (end_time - start_time) / 100; DBMS_OUTPUT.PUT_LINE('Execution Time (Simulation): ' || elapsed_time || ' seconds'); END; / -- Execute the procedure BEGIN simulate_dummy_package_destinations(); END; /
Editor is loading...
Leave a Comment