catalog ( id NUMBER PRIMARY KEY,name VARCHAR2(255),owner NUMBER,root NUMBER REFERENCES catalog(id) ... ) university ( id NUMBER PRIMARY KEY,... ) securitygroup ( id NUMBER PRIMARY KEY ... ) catalog_securitygroup ( catalog REFERENCES catalog(id),securitygroup REFERENCES securitygroup(id) ) catalog_university ( catalog REFERENCES catalog(id),university REFERENCES university(id) )
目录：500 000行,catalog_university：500 000,catalog_securitygroup：1 500 000.
我需要从目录中选择任何50行,其中指定的根目录按当前大学和当前安全组的名称排序.有一个查询：
SELECT ccc.* FROM (
SELECT cc.*,ROWNUM AS n FROM (
SELECT c.id,c.name,c.owner
FROM catalog c,catalog_securitygroup cs,catalog_university cu
WHERE c.root = 100
AND cs.catalog = c.id
AND cs.securitygroup = 200
AND cu.catalog = c.id
AND cu.university = 300
ORDER BY name
) cc
) ccc WHERE ccc.n > 0 AND ccc.n <= 50;
100 – 一些目录,200 – 一些安全组,300 – 一些大学.此查询在3分钟内从~170 000返回50行.
但是下一个查询会在2秒内返回此行：
SELECT ccc.* FROM (
SELECT cc.*,c.owner
FROM catalog c
WHERE c.root = 100
ORDER BY name
) cc
) ccc WHERE ccc.n > 0 AND ccc.n <= 50;
我构建了下一个索引：(catalog.id,catalog.name,catalog.owner),(catalog_securitygroup.catalog,catalog_securitygroup.index),(catalog_university.catalog,catalog_university.university).
http://habreffect.ru/66c/f25faa5f8/plan2.jpg
计划第二次查询：
您的问题来自于您无法首先加入最小的表.您的加入订单应该从小到大.但是因为您的映射表不包含安全组到大学的表,所以您不能先加入最小的表.所以你从一个或另一个开始,到一张大桌子,到另一张大桌子,然后用那个大的中间结果你必须去一张小桌子.
如果您始终使用current_univ和current_secgrp以及root作为输入,则希望尽快使用它们进行过滤.唯一的方法是更改您的架构.实际上,如果必须,您可以保留现有表格,但是您将使用此建议添加到空间.
您已经很好地规范了数据.这对于更新速度来说非常好……对于查询而言并不是那么好.我们非规范化以加速查询(这是数据仓库的全部原因(确定和历史)).使用以下列构建单个映射表.
Univ_id,SecGrp_ID,Root,catalog_id.将它作为pk的前3列的索引组织表.
现在,当您使用所有三个PK值查询该索引时,您将使用允许的目录ID的完整列表完成该索引扫描,现在它只是对cat表的单个连接以获取cat项目详细信息并且您已关闭运行.
