
BUILD TABLE people (name CHAR(100) NOT NULL,age INTEGER,iq INTEGER,amount INTEGER NOT NULL);

! Original table:
POPULATE people (name,age,iq,amount)
	{
	'George',	34,	200,	10000
	'Billy' ,	40,	150,	90000
	'Queen',	62,	20,		80000
	'Ronald',	80,	2,		70000
	'Demmie',	1,	312,	10000
	'Wally' ,	28,	99,		60000
	'Wammy',	29,	112,	50000
	'Wappy',	30,	89,		30000
	'Sandy',	33,	77,		40000
	'Colette',	33,	195,	20000
	}

PRINT TABLE people;	

CREATE INDEX index1 ON people(amount);
CREATE INDEX index2 ON people(amount,age);
CREATE INDEX index3 ON people(age);

!____________________________________________________________
!

! 1. No index, no restriction, no order by ->A
QUERY * FROM people;
RESULTS {0 1 2 3 4 5 6 7 8 9}

! 2a. No index, restriction, no order by ->B
QUERY * FROM people WHERE iq>150;
RESULTS {0 4 9}

! 2b. No index, restriction, no order by ->B
QUERY * FROM people WHERE iq>=150;
RESULTS {0 1 4 9}

! 3. No index, no restriction, order by ->C
QUERY * FROM people ORDER BY name;
RESULTS {1,9,4,0,2,3,8,5,6,7}

! 4. No index, restriction, order by ->D
QUERY * FROM people WHERE iq<112 ORDER BY name;
RESULTS {2,3,8,5,7}

! 4b. No index, restriction, order by ->D
QUERY * FROM people WHERE iq<=112 ORDER BY name;
RESULTS {2,3,8,5,6,7}

!____________________________________________________________
!

! 5. One index, no restriction, order by ->E
QUERY * FROM people ORDER BY age;
RESULTS {4,5,6,7,8,9,0,1,2,3}

! 6. One index, no restriction, order by ->F
QUERY * FROM people ORDER BY age DESC;
RESULTS {3,2,1,0,9,8,7,6,5,4}

! 7. Two indexes, no restriction, order by ->best E
QUERY * FROM people ORDER BY amount;
RESULTS {0,4,9,7,8,6,5,3,2,1}

! 8. Two indexes, no restriction, order by ->best F
QUERY * FROM people ORDER BY amount DESC;
RESULTS {1,2,3,5,6,8,7,9,4,0}

!____________________________________________________________
!

WINDOW ON;

! 9. One index, no restriction, order by ->E
QUERY * FROM people ORDER BY age;
RESULTS {4,5,6,7,8,9,0,1,2,3}

! 10. One index, no restriction, order by ->F
QUERY * FROM people ORDER BY age DESC;
RESULTS {3,2,1,0,9,8,7,6,5,4}

! 11. Two indexes, no restriction, order by ->best E
QUERY * FROM people ORDER BY amount;
RESULTS {0,4,9,7,8,6,5,3,2,1}

! 12. Two indexes, no restriction, order by ->best F
QUERY * FROM people ORDER BY amount DESC;
RESULTS {1,2,3,5,6,8,7,9,4,0}

WINDOW OFF;

!____________________________________________________________
!

! 13a. One index, 25% restriction ->I
QUERY * FROM people WHERE age>=62; 
RESULTS {2 3}

! 13b. One index, <25% restriction ->I
QUERY * FROM people WHERE age>62; 
RESULTS {3}

! 14. One index, 63% restriction ->B
QUERY * FROM people WHERE age>29;
RESULTS {0 1 2 3 7 8 9}

! 15a. One index, 35% restriction ->J
QUERY * FROM people WHERE age<29; 
RESULTS {4 5}

! 15b. One index, >35% restriction ->J
QUERY * FROM people WHERE age<=29; 
RESULTS {4 5 6}

! 16. One index, 73% restriction ->B
QUERY * FROM people WHERE age<62;
RESULTS {0 1 4 5 6 7 8 9}

!____________________________________________________________
!

WINDOW ON;

! 17. One index, 25% restriction ->I
QUERY * FROM people WHERE age>60; 
RESULTS {2 3}

! 18. One index, 63% restriction ->I
QUERY * FROM people WHERE age>29;
RESULTS {0 1 2 3 7 8 9}

! 19. One index, 35% restriction ->J
QUERY * FROM people WHERE age<29; 
RESULTS {4 5}

! 20. One index, 73% restriction ->J
QUERY * FROM people WHERE age<60;
RESULTS {0 1 4 5 6 7 8 9}

WINDOW OFF;

!____________________________________________________________
!

! 21. Two indexes, 24% restriction ->Best I
QUERY * FROM people WHERE amount>70000; 
RESULTS {1 2}

! 22. Two indexes, 87% restriction ->B
QUERY * FROM people WHERE amount>20000;
RESULTS {1 2 3 5 6 7 8}

! 23. Two indexes, 12% restriction ->Best J
QUERY * FROM people WHERE amount<20000; 
RESULTS {0 4}

! 24. Two indexes, 87% restriction ->B
QUERY * FROM people WHERE amount<80000;
RESULTS {0 3 4 5 6 7 8 9}

!____________________________________________________________
!

! 25a. Two indexes on amount (24%), one on age ->Best O
QUERY * FROM people WHERE amount>70000 AND age>33; 
RESULTS {1,2}

! 25b. Two indexes on amount, one on age ->Best O
QUERY * FROM people WHERE amount>=70000 AND age>40; 
RESULTS {2 3}

! 25c. Two indexes on amount, one on age ->Best O
QUERY * FROM people WHERE amount>70000 AND age>=40; 
RESULTS {1 2}

! 25d. Two indexes on amount, one on age ->Best O
QUERY * FROM people WHERE amount>=70000 AND age>=40; 
RESULTS {1 2 3}

! 26a. Two indexes on amount, one on age ->Best P
QUERY * FROM people WHERE amount<30000 AND age>30;
RESULTS {0,9}

! 26b. Two indexes on amount, one on age ->Best P
QUERY * FROM people WHERE amount<=30000 AND age>30;
RESULTS {0 9}

! 26c. Two indexes on amount, one on age ->Best P
QUERY * FROM people WHERE amount<30000 AND age>=30;
RESULTS {0 9}

! 26d. Two indexes on amount, one on age ->Best P
QUERY * FROM people WHERE amount<=30000 AND age>=30;
RESULTS {0 7 9}

! 27. Two indexes on amount (74%), one on age (58%) ->B
QUERY * FROM people WHERE amount>30000 AND age>33; 
RESULTS {1 2 3}

!____________________________________________________________
!

WINDOW ON;

! 28. Two indexes on amount (24%), one on age (58%) ->Best O
QUERY * FROM people WHERE amount>70000 AND age>33; 
RESULTS {1 2}

! 29. Two indexes on amount (12%), one on age (58%) ->Best P
QUERY * FROM people WHERE amount<20000 AND age>33;
RESULTS {0}

! 30. Two indexes on amount (74%), one on age (58%) ->Best O
QUERY * FROM people WHERE amount>30000 AND age>33; 
RESULTS {1 2 3}

WINDOW OFF;

!____________________________________________________________
!

ACCESS READ;

! 31a. Two indexes, 24% restriction, order by index ->Best M
QUERY * FROM people WHERE amount>70000 ORDER BY amount; 
RESULTS {2,1}

! 31b. Two indexes, restriction, order by index ->Best M
QUERY * FROM people WHERE amount>=70000 ORDER BY amount; 
RESULTS {3,2,1}

! 31c. Two indexes, restriction, order by index ->Best M
QUERY * FROM people WHERE amount<70000 ORDER BY amount; 
RESULTS {0,4,9,7,8,6,5}

! 31d. Two indexes, restriction, order by index ->Best M
QUERY * FROM people WHERE amount<=70000 ORDER BY amount; 
RESULTS {0,4,9,7,8,6,5,3}

!____________________________________________________________
!

ACCESS READ;

! 32a. Two indexes, 24% restriction, order by index ->Best N
QUERY * FROM people WHERE amount>70000 ORDER BY amount DESC; 
RESULTS {1,2}

! 32b. Two indexes, restriction, order by index ->Best N
QUERY * FROM people WHERE amount>=70000 ORDER BY amount DESC; 
RESULTS {1,2,3}

! 32c. Two indexes, restriction, order by index ->Best N
QUERY * FROM people WHERE amount<70000 ORDER BY amount DESC; 
RESULTS {5,6,8,7,9,4,0}

! 32d. Two indexes, restriction, order by index ->Best N
QUERY * FROM people WHERE amount<=70000 ORDER BY amount DESC; 
RESULTS {3,5,6,8,7,9,4,0}

!____________________________________________________________
!

ACCESS UPDATE;

! 33. Two indexes, 24% restriction, order by index ->Best M +window
QUERY * FROM people WHERE amount>70000 ORDER BY amount; 
RESULTS {2,1}

ACCESS READ;

!____________________________________________________________
!

! 34a. Three indexes, 24% restriction, order by index (2K's, 1 G)->Best K
QUERY * FROM people WHERE amount>70000 ORDER BY age; 
RESULTS {1,2}

! 34b. Three indexes,restriction, order by index (2K's, 1 G)->Best K
QUERY * FROM people WHERE amount>=70000 ORDER BY age; 
RESULTS {1,2,3}

! 34c. Three indexes, restriction, order by index (2K's, 1 G)->Best L
QUERY * FROM people WHERE amount<20000 ORDER BY age; 
RESULTS {4,0}

! 34d. Three indexes, restriction, order by index (2K's, 1 G)->Best L
QUERY * FROM people WHERE amount<=20000 ORDER BY age; 
RESULTS {4,9,0}

!____________________________________________________________
!

! 35. Three indexes, 74% restriction, order by index (2K's, 1 G)->D
QUERY * FROM people WHERE amount>30000 ORDER BY age; 
RESULTS {5,6,8,1,2,3}

!____________________________________________________________
!

! 36. Three indexes, 87% restriction, order by index (2K's, 1 G)->G
QUERY * FROM people WHERE amount<80000 ORDER BY age; 
RESULTS {4,5,6,7,8,9,0,3}

!____________________________________________________________
!

WINDOW ON;

! 37. Three indexes, 87% restriction, order by index (2K's, 1Gw - discarded)->Best K
QUERY * FROM people WHERE amount<80000 ORDER BY age; 
RESULTS {4,5,6,7,8,9,0,3}

!____________________________________________________________
!

WINDOW OFF;

! 38. Two indexes, order by index (2G's)->D
QUERY * FROM people WHERE iq>=150 ORDER BY amount; 
RESULTS {0,4,9,1}

!____________________________________________________________
!

WINDOW ON;

! 39. Two indexes, order by index (2Gw's - discarded)->D
QUERY * FROM people WHERE iq>=150 ORDER BY amount; 
RESULTS {0,4,9,1}

!____________________________________________________________
!

! 40a. Two indexes on amount (24%), one on age (58%), order by non-index ->Best Q
QUERY * FROM people WHERE amount>70000 AND age>33 ORDER BY iq; 
RESULTS {2,1}

! 40b. Two indexes on amount, one on age, order by non-index ->Best Q
QUERY * FROM people WHERE amount>=70000 AND age>40 ORDER BY iq; 
RESULTS {3,2}

! 40c. Two indexes on amount, one on age, order by non-index ->Best Q
QUERY * FROM people WHERE amount>70000 AND age>=40 ORDER BY iq; 
RESULTS {2,1}

! 40d. Two indexes on amount, one on age, order by non-index ->Best Q
QUERY * FROM people WHERE amount>=70000 AND age>=40 ORDER BY iq; 
RESULTS {3,2,1}

! 41. Two indexes on amount (87%), one on age (63%), order by non-index ->D
QUERY * FROM people WHERE amount<80000 AND age>29 ORDER BY iq;
RESULTS {3,8,7,9,0}

!____________________________________________________________
!

DROP INDEX index1 FROM people;
DROP INDEX index2 FROM people;
DROP INDEX index3 FROM people;