CREATE INDEX customers_idx ON customers(cust_id);         -- INDEX for CUSTOMERS table
------------------------------------------------------------------------------------------------
CREATE TABLE customers_iot                                -- INDEX ORGANIZED TABLE
(cust_id PRIMARY KEY,                                     -- Primary Key 
 cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_marital_status, cust_street_address, 
 cust_postal_code, cust_city, cust_state_province, cust_main_phone_number, cust_income_level, cust_credit_limit, cust_email
)
ORGANIZATION INDEX                                        -- Organization
AS
SELECT cust_id,
 cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_marital_status, cust_street_address, 
 cust_postal_code, cust_city, cust_state_province, cust_main_phone_number, cust_income_level, cust_credit_limit, cust_email
FROM customers;
------------------------------------------------------------------------------------------------
CREATE TABLE customers_part                               -- PARTITIONED TABLE
(cust_id,                             
 cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_marital_status, cust_street_address, 
 cust_postal_code, cust_city, cust_state_province, cust_main_phone_number, cust_income_level, cust_credit_limit, cust_email
)
PARTITION BY RANGE (cust_year_of_birth)  
 (PARTITION p1  VALUES LESS THAN (1920),
  PARTITION p2  VALUES LESS THAN (1930),
  PARTITION p3  VALUES LESS THAN (1940),
  PARTITION p4  VALUES LESS THAN (1950),
  PARTITION p5  VALUES LESS THAN (1960),
  PARTITION p6  VALUES LESS THAN (1970),
  PARTITION p7  VALUES LESS THAN (1980),
  PARTITION p8  VALUES LESS THAN (MAXVALUE)
 )
AS 
SELECT cust_id,
 cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_marital_status, cust_street_address, 
 cust_postal_code, cust_city, cust_state_province, cust_main_phone_number, cust_income_level, cust_credit_limit, cust_email
FROM customers;
------------------------------------------------------------------------------------------------
CREATE CLUSTER cust_hcl (yob  NUMBER(4)) SINGLE TABLE HASHKEYS 31;
CREATE TABLE cust_hclt                                               -- CLUSTERED TABLE
(cust_id,                             
 cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_marital_status, cust_street_address, 
 cust_postal_code, cust_city, cust_state_province, cust_main_phone_number, cust_income_level, cust_credit_limit, cust_email
)
CLUSTER cust_hcl(cust_year_of_birth)                                 -- Cluster
AS 
SELECT cust_id,
 cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_marital_status, cust_street_address, 
 cust_postal_code, cust_city, cust_state_province, cust_main_phone_number, cust_income_level, cust_credit_limit, cust_email
FROM customers; 
================================================================================================
CREATE OR REPLACE PROCEDURE run_time_test(p integer) IS
  rnd INTEGER; cnt INTEGER; start_time DATE; run_time NUMBER;
BEGIN 
  SELECT sysdate INTO start_time FROM dual;
  FOR I IN 1..20000 LOOP
    SELECT round(dbms_random.VALUE(1,10000)) INTO rnd FROM dual; 
    IF p = 1 THEN
      SELECT min(cust_year_of_birth) INTO cnt FROM customers_iot 
      WHERE cust_id BETWEEN rnd AND rnd + 500;                                           /* iot */
    ELSIF p=2 THEN
      SELECT /*+ index(customers) */ min(cust_year_of_birth) INTO cnt 
      FROM customers WHERE cust_id BETWEEN rnd AND rnd + 500;                            /* indexed table */
    ELSIF p=3 THEN
      SELECT /*+ no_index(customers) */ min(cust_year_of_birth) INTO cnt 
      FROM customers WHERE cust_id BETWEEN rnd AND rnd + 500;                            /* without index */
    ELSIF p=4 THEN
      SELECT round(dbms_random.VALUE(1913,1990)) INTO rnd FROM dual; 
      SELECT count(*) INTO cnt FROM customers_part WHERE cust_year_of_birth = rnd;       /* partitioned table */
    ELSIF p=5 THEN
      SELECT round(dbms_random.VALUE(1913,1990)) INTO rnd FROM dual; 
      SELECT count(*) INTO cnt FROM customers_part WHERE cust_year_of_birth + 1 = rnd;   /* partition ??? */  
    ELSIF p=6 THEN
      SELECT round(dbms_random.VALUE(1913,1990)) INTO rnd FROM dual; 
      SELECT count(*) INTO cnt FROM cust_hclt WHERE cust_year_of_birth = rnd;            /* clustered table */  
    END IF;
--  run time limit in seconds
    SELECT (sysdate-start_time)*24*60*60 INTO run_time FROM dual;
    IF run_time > 60 THEN dbms_output.put_line('Timeout!!!'); RETURN; END IF;
  END LOOP;
  dbms_output.put_line('Completed, no Timeout');
END;
/
set serveroutput on
execute run_time_test(1);
execute run_time_test(2);
execute run_time_test(3);
execute run_time_test(4);
execute run_time_test(5);
execute run_time_test(6);