I'm researching everything SQL, and upon reading through about indexes I naturally wanted to produce a simple adhoc test to determine how indexes REALLY affect performance (as opposed to just using the reading through at face value). However, I'm getting some large issues with the tests I had been trying to setup. In a nutshell, regardless of how large I make my tables, without or with indexes, the queries finish very rapidly, normally .5 seconds each time. I had been wishing which i could create some tables so large that particular queries would have a very long time to complete, after which by creating indexes I possibly could reduce individuals execution occasions, and compare the cutbacks against various kinds of indexes. However, I'm getting no luck whatsoever! This is how I setup my sample table:
CREATE TABLE EMPLOYEE (EmpID NUMBER(6), Lname VARCHAR2(20), Fname VARCHAR2(20), Gender CHAR(1), HomeState CHAR(2), BirthDate DATE, HiredDate DATE, Occupation VARCHAR2(20), Salary NUMBER(6), NumDep NUMBER(1));
The Worker table has 10 characteristics, and that i produced another Java program to create SQL instructions to populate the table with random info. For instance, this is actually the output in the Java program by having an input size 3:
INSERT INTO EMPLOYEE VALUES (1, 'HENRY', 'VIRGINIA', 'F', 'MA', '14-NOV-1966', '27-APR-1987', 'MANAGER', '80514', '5'); INSERT INTO EMPLOYEE VALUES (2, 'PARSONS', 'KEVIN', 'M', 'KS', '11-DEC-1961', '14-JAN-2004', 'NURSE', '74416', '3'); INSERT INTO EMPLOYEE VALUES (3, 'GARRETT', 'JIMMY', 'M', 'NE', '24-MAR-1963', '20-MAY-1992', 'SERVICE', '87116', '2');
The surname is selected from 500 common last names at random. The name is selected from 100 gender-specific first names at random. The condition is selected at random (from a listing of 50, clearly). The dates are selected at random within certain reasonable limits. The occupation is selected at random from a listing of 20 common jobs. The wages are selected at random between 50000 and 99999 (which results in some impractical salary/job matches, but that's not necessarily the purpose here). The amount of loved ones is selected at random between and 5.
I believed that certain positive thing concerning the above setup is the plethora of domain dimensions. I.e. 500 last names possible, 100 first names possible, 50 states, 20 jobs, etc. I understand from reading through that indexes are more effective when the quantity of possible values is more compact in comparison towards the overall quantity of records, and that i was wishing to have the ability to prove this with my testing. However, when i stated before, my tests are failing horrible.
I began off inhabiting the Worker table with only 500 records, and located that each query Used to do (example below) required only .5 seconds in SQL Developer (the program I'm using for connecting to Oracle 11g).
SELECT * FROM EMPLOYEE WHERE salary BETWEEN 60000 and 62000;
So i quickly attempted with 1,000 records, then 10,000 records, then 100,000 records, then 500,000 records (that we needed to place in batches of 100,000 since my clipboard and/or SQL Developer can't appear to deal with such large script)... and also the results are the same. It solves every query ins5 seconds! Which blows my thoughts because it gets control a minute to place 100,000 records. Also SQL Developer can't appear to come back a lot more than 5,000 produces a single query script execution, and when I compare for this max it sometimes takes 1. seconds rather than .5 seconds, however i suspect this really is mainly due to time required to print all 5,000 leads to the script output window.
I've attempted my queries on various characteristics, and I have attempted with and without indexes on individuals characteristics, and also the indexes appear to create no difference. I'm very disappointed here. I had been expecting my testing to yield a lot more tangible results. I had been also wishing to make use of this subject, together with the outcomes of the testing for any class paper that I have to write soon, however with my testing yielding no results that's likely to be type of hard. Any suggestions?
P.S. Thank you for reading through this far should you did! Really sorry for that length...