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).

规划第一次查询(使用PLsql Developer)：

http://habreffect.ru/66c/f25faa5f8/plan2.jpg

计划第二次查询：

http://habreffect.ru/f91/86e780cc7/plan1.jpg

有什么方法可以优化我的查询？