SQLize | PHPize | SQLtest

-- Create the customers table CREATE TABLE customers ( customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255) NOT NULL ); -- Insert 25 random customer records INSERT INTO customers (name) VALUES ('Alice Smith'), ('Bob Johnson'), ('Charlie Brown'), ('Diana Prince'), ('Ethan Hunt'), ('Fiona Gallagher'), ('George Miller'), ('Hannah Abbott'), ('Ian Fleming'), ('Jane Austen'), ('Kevin Spacey'), ('Laura Croft'), ('Michael Jordan'), ('Nancy Drew'), ('Oliver Twist'), ('Penelope Cruz'), ('Quentin Tarantino'), ('Rachel Green'), ('Steve Rogers'), ('Tina Turner'), ('Ursula K. Le Guin'), ('Victor Hugo'), ('Wendy Darling'), ('Xavier Roberts'), ('Yvonne Craig'); -- Create the orders table CREATE TABLE orders ( order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, customer_id INT, order_date TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ); -- Fill orders table by 1000 records INSERT INTO orders (customer_id, order_date) SELECT FLOOR(DBMS_RANDOM.VALUE * 25) + 1, SYSTIMESTAMP - NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE * 365), 'DAY') FROM (SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 1000); CREATE INDEX idx_orders_customer_id ON orders(customer_id); SELECT customers.customer_id, COUNT(order_id) AS orders_count FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id; SELECT customers.customer_id, ( SELECT COUNT(order_id) FROM orders WHERE orders.customer_id = customers.customer_id ) AS orders_count FROM customers; -- Find queries execution time SELECT sql_text, (elapsed_time / 1000 / executions) AS "Среднее время (ms)" FROM V$SQLAREA WHERE executions > 0 AND UPPER(sql_text) LIKE 'SELECT CUSTOMERS%'; -- Generate query plan EXPLAIN PLAN FOR SELECT c.customer_id, COUNT(o.order_id) AS orders_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- Generate query plan EXPLAIN PLAN FOR SELECT c.customer_id, (SELECT COUNT(o.order_id) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count FROM customers c; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);