CREATE TABLE cars (
   units     NUMBER,
   make      VARCHAR2(20),
   model     VARCHAR2(30),
   year      NUMBER
)
INDEXING OFF
PARTITION BY RANGE (year)
  (PARTITION gen1 VALUES LESS THAN (1973) INDEXING ON,
   PARTITION gen2 VALUES LESS THAN (1978) INDEXING OFF,
   PARTITION gen3 VALUES LESS THAN (1993) INDEXING ON,
   PARTITION gen4 VALUES LESS THAN (2004) INDEXING OFF,
   PARTITION gen5 VALUES LESS THAN (2014) INDEXING OFF,
   PARTITION gen6 VALUES LESS THAN (MAXVALUE) INDEXING OFF);


/* First Generation */ 
INSERT INTO cars
SELECT ROUND(dbms_rANDom.value(1,10000)) units,
       'FORD','MUSTANG',
       ROUND(dbms_rANDom.value(1964,1973)) year
 FROM dual
 CONNECT BY LEVEL < 5000 ;

/* Second Generation */ 
INSERT INTO cars
SELECT ROUND(dbms_rANDom.value(1,10000)) units,
       'FORD','MUSTANG',
       ROUND(dbms_rANDom.value(1974,1978)) year
 FROM dual
 CONNECT BY LEVEL < 5000 ;

/* Third Generation */ 
INSERT INTO cars
SELECT ROUND(dbms_rANDom.value(1,10000)) units,
       'FORD','MUSTANG',
       ROUND(dbms_rANDom.value(1979,1993)) year
 FROM dual
 CONNECT BY LEVEL < 5000 ;

/* Fourth Generation */ 
INSERT INTO cars
SELECT ROUND(dbms_rANDom.value(1,10000)) units,
       'FORD','MUSTANG',
       ROUND(dbms_rANDom.value(1994,2004)) year
 FROM dual
 CONNECT BY LEVEL < 5000 ;

/* Fifth Generation */ 
INSERT INTO cars
SELECT ROUND(dbms_rANDom.value(1,10000)) units,
       'FORD','MUSTANG',
       ROUND(dbms_rANDom.value(2005,2014)) year
 FROM dual
 CONNECT BY LEVEL < 5000 ;

/* Sixth Generation */ 
INSERT INTO cars
SELECT ROUND(dbms_rANDom.value(1,10000)) units,
       'FORD','MUSTANG',
       2015
 FROM dual
 CONNECT BY LEVEL < 5000 ;

COMMIT;

CREATE INDEX car_make ON cars (make);

SELECT index_name,
       num_rows,
       leaf_blocks,
       indexing
  FROM user_indexes
 WHERE index_name = 'CAR_MAKE' ; 

SET AUTOTRACE ON
SELECT count(*)
  FROM cars 
 WHERE make = 'FORD' 
   AND year < 1974 ;

DROP INDEX car_make;

CREATE INDEX car_make_i ON cars (make) INDEXING PARTIAL;

SELECT index_name, 
       num_rows, 
       leaf_blocks, 
       indexing 
  FROM user_indexes 
 WHERE index_name = 'CAR_MAKE_I';

SELECT count(*)
  FROM cars
 WHERE make = 'FORD'
   AND year < 1974 ;

SELECT count(*)
  FROM cars 
 WHERE make = 'FORD' 
   AND year > 2005 ;

-- Clean-Up

DROP TABLE cars PURGE;