select
        s_acctbal,
        s_name,
        n_name,
        p_partkey,
        p_mfgr,
        s_address,
        s_phone,
        s_comment
from
        part p,
        supplier,
        partsupp,
        nation,
        region
where
        p_partkey = ps_partkey
        and s_suppkey = ps_suppkey
        and p_size =  15 
        and p_type like '%BRASS'
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'EUROPE'
        and ps_supplycost = (
                select
                        min(ps_supplycost)
                from
                        partsupp,
                        supplier,
                        nation,
                        region
                where
                        p.p_partkey = ps_partkey
                        and s_suppkey = ps_suppkey
                        and s_nationkey = n_nationkey
                        and n_regionkey = r_regionkey
                        and r_name = 'EUROPE'
        )
order by
        s_acctbal desc,
        n_name,
        s_name,
    p_partkey
FETCH FIRST  100  rows ONLY
OPTIMIZE FOR  100  ROWS
;



execution started at timestamp  2006 - 03 - 01 - 17 . 04 . 24 . 613000 
found [ 1 ] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [   77 , 779 ] MB
total disk space constrained to         [  337 , 398 ] MB
Trying variations of the solution set.
Optimization finished.
   9   indexes in current solution
 [ 660810 , 3125 ] timerons  (without recommendations)
 [ 1702 , 1033 ] timerons  (with current solution)
 [ 99 , 74 %] improvement


--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1],   19,329MB
   CREATE INDEX "VVM     "."IDX603011205030000" ON "VVM     "."PART" ("P_SIZE" ASC, "P_MFGR" ASC, "P_TYPE" ASC, "P_PARTKEY" ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."PART" FOR INDEX "VVM     "."IDX603011205030000" ;
   COMMIT WORK ;
-- index[2],   27,493MB
   CREATE INDEX "VVM     "."IDX603011204410000" ON "VVM     "."PARTSUPP" ("PS_PARTKEY" ASC, "PS_SUPPLYCOST" ASC, "PS_SUPPKEY" ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."PARTSUPP" FOR INDEX "VVM     "."IDX603011204410000" ;
   COMMIT WORK ;
-- index[3],    0,196MB
   CREATE UNIQUE INDEX "VVM     "."IDX603011204390000" ON "VVM     "."SUPPLIER" ("S_SUPPKEY" ASC) INCLUDE ("S_NATIONKEY") ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."SUPPLIER" FOR INDEX "VVM     "."IDX603011204390000" ;
   COMMIT WORK ;
-- index[4],    0,013MB
   CREATE INDEX "VVM     "."IDX603011204270000" ON "VVM     "."REGION" ("R_NAME" ASC, "R_REGIONKEY" ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."REGION" FOR INDEX "VVM     "."IDX603011204270000" ;
   COMMIT WORK ;
-- index[5],    0,013MB
   CREATE INDEX "VVM     "."IDX603011204310000" ON "VVM     "."NATION" ("N_REGIONKEY" ASC, "N_NATIONKEY" ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."NATION" FOR INDEX "VVM     "."IDX603011204310000" ;
   COMMIT WORK ;
-- index[6],   27,493MB
   CREATE INDEX "VVM     "."IDX603011204430000" ON "VVM     "."PARTSUPP" ("PS_SUPPLYCOST" ASC, "PS_PARTKEY" ASC, "PS_SUPPKEY" ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."PARTSUPP" FOR INDEX "VVM     "."IDX603011204430000" ;
   COMMIT WORK ;
-- index[7],    3,216MB
   CREATE UNIQUE INDEX "VVM     "."IDX603011205400000" ON "VVM     "."SUPPLIER" ("S_SUPPKEY" ASC) INCLUDE ("S_ACCTBAL", "S_COMMENT", "S_PHONE", "S_ADDRESS", "S_NAME", "S_NATIONKEY") ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."SUPPLIER" FOR INDEX "VVM     "."IDX603011205400000" ;
   COMMIT WORK ;
-- index[8],    0,013MB
   CREATE UNIQUE INDEX "VVM     "."IDX603011205350000" ON "VVM     "."NATION" ("N_NATIONKEY" ASC) INCLUDE ("N_NAME", "N_REGIONKEY") ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE "VVM     "."NATION" FOR INDEX "VVM     "."IDX603011205350000" ;
   COMMIT WORK ;


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "VVM     "."NATION" FOR INDEX "VVM     "."NATION_PK" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "VVM     "."PARTSUPP" FOR INDEX "VVM     "."PARTSUPP_PK" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "VVM     "."REGION" FOR INDEX "VVM     "."REGION_PK" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "VVM     "."SUPPLIER" FOR INDEX "VVM     "."SUPPLIER_PK" ;
-- COMMIT WORK ;


--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "VVM     "."PART_BRAND_CONTAIN";
-- DROP INDEX "VVM     "."PART_NAME";
-- DROP INDEX "VVM     "."PARTSUPP_SUPPKEY";
-- DROP INDEX "VVM     "."SUPPLIER_NATIONKEY";
-- ===========================
--

 31  solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.

Using user id as default schema name. Use -n option to specify schema