选择索引数据库

Question

我在为这个问题选择最佳索引时遇到了一些问题。给定一组查询：

--查询1

select CUSTOMERS.PID
from CUSTOMERS join
     PARTICIP
     on CUSTOMERS.PID = PARTICIP.PID
group by CUSTOMERS.PID
having COUNT(CUSTOMERS.PID) = 1;

--查询2

select CUSTOMERS.PID
from CUSTOMERS join
     PARTICIP
     on CUSTOMERS.PID = PARTICIP.PID
group by CUSTOMERS.PID
having COUNT(CUSTOMERS.PID) >= 2;

--查询3

select hasa.pid,strno as "Street #", street, city, prov as Province
from ((addresses join hasa on hasa.addrid = addresses.addrid) join 
      customers
      on customers.pid = hasa.pid
     )
order by city;

--查询4

select * 
from guides 
where pid in (select pid 
              from particip 
              where tid in (select unique tid 
                            from itineraries 
                            where date >= '2015-01-01' and date <= '2015-12-31'));

--查询5

select age, gender 
from persons 
where persons.pid in (select hase.pid 
                      from hase 
                      where hase.pid in (select * from employees));

--查询6

select provid 
from isp 
where provid NOT in (select provid as service 
                     from isp 
                     group by provid, svctype 
                     having svctype = 'ACCOM');

--查询7

select provid 
from isp 
where provid in (select provid 
                 from isp 
                 where ((svctype != 'ACCOM' and svctype = 'MEAL') 
                        or (svctype = 'ACCOM' and svctype != 'MEAL')) 
                 group by provid 
                 having count(unique svctype) = 1);

--查询8

select unique provid, svctype 
from isp 
where penalty = 100 or penalty = 200;

--查询9

select pid, sum(amount) as "totalSalary", count(pid) as "numTours" 
from hasco 
group by pid 
order by sum(amount) desc;

--查询10

select distinct hasco.TID 
from hasco 
join (select * 
      from tours 
      where status = 'I' OR status = 'F') as table1 on hasco.tid = table1.tid  
where amount >= 5000;
terminate;

ER图：ER Diagram

我的任务是选择两个属性来索引，这两个属性将提供最大的好处。没有提到对表的任何数量的查询或修改。谢谢。

编辑:我只需要一个或两个属性来索引，并在其背后进行一些推理来帮助理解。

另外，通常情况下，非集群比集群更好吗？谢谢。

Answer 1

考虑到上面的查询和ER图，不可能选择“最好的”或任何具有“最大”好处的东西。您(和优化器)还需要了解数据(“统计”)。涉及多少行，值是如何分布的，以及更多的问题。

如果只有较小的表，优化器可能会选择一个表扫描，而根本不需要索引。通常，尝试弄清楚谓词列和连接列上的索引是否有帮助。

您是否已经使用了db2解释工具或design/index advisor (db2advis)来研究此问题？