Validate data compression

Concepts

A stored procedure is a collection of one or more SQL statements. You can use stored procedures to encapsulate complex database operations into a reusable code block, which helps reduce the workload for database developers. Because a stored procedure is compiled and saved in the database, you can execute it simply by calling its name and providing parameters. This can also speed up the execution of SQL statements. For more information, see Create a stored procedure.

You can use system variables and user-defined variables in stored programs. For example, you can use the `DECLARE` statement to define local variables. In addition, stored routines, such as procedures and functions, can declare parameters to pass values between the routine and its caller. For more information, see Variables in stored programs.

Procedure

1. Open a new SQL window.

2. Click Select a database and select the `tutorial_database` database.

3. Execute the following command to create a stored procedure and insert randomly generated data into the table.

   drop procedure if exists generate_orders;
   delimiter $$
   
   create procedure generate_orders ( IN `num_rows` int(11))
   BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE customer_id INT DEFAULT 1;
    DECLARE order_date DATETIME;
    DECLARE shipping_date DATETIME;
    DECLARE `status` VARCHAR(10);
    DECLARE subtotal DECIMAL(10, 2);
    DECLARE tax DECIMAL(10, 2);
    DECLARE shipping DECIMAL(10, 2);
    DECLARE total DECIMAL(10, 2);
    DECLARE `address` VARCHAR(200);
    DECLARE city VARCHAR(100);
    DECLARE `state` VARCHAR(50);
    DECLARE country VARCHAR(50);
    DECLARE zip_code VARCHAR(20);
    DECLARE phone VARCHAR(20);
    DECLARE email VARCHAR(100);
    DECLARE created_at DATETIME;
    DECLARE updated_at DATETIME;
   
    SET i = 1;
    SET order_date = NOW();
   
    WHILE i <= num_rows DO
    SET customer_id = FLOOR(RAND() * 100) + 1;
    SET shipping_date = ADDDATE(order_date, INTERVAL FLOOR(RAND() * 30) DAY);
    SET status = CASE FLOOR(RAND() * 4)
    WHEN 0 THEN 'Pending'
    WHEN 1 THEN 'Processing'
    WHEN 2 THEN 'Shipped'
    ELSE 'Delivered' END;
    SET subtotal = ROUND(RAND() * 1000, 2);
    SET tax = ROUND(subtotal * 0.1, 2);
    SET shipping = ROUND(RAND() * 100, 2);
    SET total = subtotal + tax + shipping;
    SET address = CONCAT('Address ', FLOOR(RAND() * 1000));
    SET city = 'City';
    SET state = 'State';
    SET country = 'Country';
    SET zip_code = '12345';
    SET phone = '1234567890';
    SET email = CONCAT('customer', customer_id, '@example.com');
    SET created_at = NOW();
    SET updated_at = NOW();
   
    INSERT INTO `orders` 
    (
    `customer_id`,
    `order_date`,
    `shipping_date`,
    `status`,
    `subtotal`,
    `tax`,
    `shipping`,
    `total`,
    `address`,
    `city`,
    `state`,
    `country`,
    `zip_code`,
    `phone`,
    `email`,
    `created_at`,
    `updated_at`
    ) 
   VALUES 
   (
   customer_id,
   order_date,
   shipping_date,
   status,
   subtotal,
   tax,
   shipping,
   total,
   address,
   city,
   state,
   country,
   zip_code,
   phone,
   email,
   created_at,
   updated_at
   );
   
   SET i = i + 1;
   END WHILE;
   
   END $$
   
   delimiter ;

4. Click the run button and view the result.

5. After the command runs successfully, open a new SQL window. Execute the following command to call the stored procedure and generate 20,000 rows of data.

call generate_orders(20000);

6. After the command runs successfully, open a new SQL window. Execute the following command to verify the number of rows in the table. If the command returns `20000`, the data was inserted successfully.

select count(*) from orders;

View the result:

7. Return to the tenant workbench and view the storage size used. The result is more accurate with a larger data volume. Your actual results provide the most accurate measurement.

Compression ratio analysis

To store the same amount of data, an OceanBase database uses only 1/4 to 1/3 of the storage resources required by a MySQL or Oracle database. This can reduce storage costs by 70% to 90%. The compression effect is more significant with a larger data volume. If you have more time, you can try inserting more data.