--
-- a. create an index that will speedup query_1 only
--
CREATE INDEX "SCOTT"."ADM_PG_FEATUREVALUE_IDX2" 
ON "SCOTT"."ADM_PG_FEATUREVALUE" ("FEATUREVALUE_ID" )  
PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "SYSTEM" LOGGING;



--
-- b. create index that improves query_1_bis and query_14_bis
--
CREATE INDEX "SCOTT"."LU_ELEMENTRANGE_REL_IDX" ON "SCOTT"."LU_ELEMENTRANGE_REL" ("ELEMENTRANGE_ID" )  
PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "SYSTEM" NOLOGGING;

CREATE INDEX "SCOTT"."LU_ELEMENTGROUP_REL_IDX1" ON "SCOTT"."LU_ELEMENTGROUP_REL" ("ELEMENTGROUP_ID" )  
PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" NOLOGGING;





--
-- c. drop index that affect query_14, query_50, and query_51.
--    This will cause sever degradation. This is just 
--ALTER INDEX "ADM_PG_FEATUREVALUE_PK" UNUSABLE;
DROP INDEX "LU_ELEMENTRANGE_REL_IDX";
DROP INDEX "LU_ELEMENTGROUP_REL_IDX1";

