So I have been stuck on this for 6 hours now and I have no clue what to do. I am doing university homework that requires us to create a unoptomized sql query (does not have to make sense) and then apply index’s and see if it makes it faster (which it did for me, from 0.70 elapsed time to 0.66) and then we had to apply clusters.
I applied clusters and it has now almost doubled the amount taken to finish the query. From 0.70 to 1.15. Below is how I specified my cluster:
CREATE CLUSTER customer2_custid25 (custid NUMBER(8)) SIZE 270 TABLESPACE student_ts;
I tried all my previous times with INITIAL and NEXT but that seemed not to make a difference. Below are the tables:
CREATE TABLE CUSTOMER18 ( CustID NUMBER(8) NOT NULL, FIRST_NAME VARCHAR2(15), SURNAME VARCHAR2(15), ADDRESS VARCHAR2(20), PHONE_NUMBER NUMBER(12)) CLUSTER customer2_custid25(CustID); CREATE TABLE product18( ProdID NUMBER(10) NOT NULL, PName Varchar2(6), PDesc Varchar2(15), Price Number(8), QOH Number(5)); CREATE TABLE sales18( SaleID NUMBER(10) NOT NULL, SaleDate DATE, Qty Number(5), SellPrice Number(10), CustID NUMBER(8), ProdID NUMBER(10)) CLUSTER customer2_custid25(CustID); CREATE INDEX customer2_custid_clusterindxqg ON CLUSTER customer2_custid25 TABLESPACE student_ts ;
I also tried taking the tablespace section in the cluster index away.
I followed this formula to help calculate cluster sizes:
"Size of a cluster is the size of a parent row + (size of Child row * average number of children). "
This brought me to the size of 270. However, after testing sizes (going up 20) from 250 to 350 I found 320 to be the fastest at 1.15.
No matter what I try, I can not for the love of me get it lower then my base query times.
Other students have done the same and halved their query time.
All help is really appreciated.