java - Java EE + Oracle：如何使用 GTT（全局临时表）来避免可能的 long(1000+) IN 子句？

Question

CriteriaBuilder我有一个使用Hibernate构建的 Oracle 数据库查询。现在，它有一个 IN 子句，它已经使用了大约 800 多个参数。

团队表示，这可能会超过 1000 并达到 Oracle 本身的硬上限，它只允许 IN 子句使用 1000 个参数。我们需要优化它。

select ih from ItemHistory as ih 
    where ih.number=:param0
    and
    ih.companyId in (
        select c.id from Company as c 
        where (
                ( c.organizationId in (:param1) ) 
                or 
                ( c.organizationId like :param2 )
        ) and (
                c.organizationId in (:param3, :param4, :param5, :param6, :param7, :param8, :param9, :param10, ..... :param818)
        )
    )
order by ih.eventDate desc

所以，我能想到两个解决方案：

1. 最简单的，因为现在 from :param3to的列表:param818在 1000 以下，将来可能达到 1000，如果 size > 1000，我们可以将列表分隔到另一个 IN 子句中，所以它变成：

   c.organizationId in (:param3, :param4, :param5, :param6, :param7, :param8, :param9, :param10, ..... :param1002) 或 c.organizationId in (:param1003, ...)

2. 原始代码和解决方案 1 都不是很有效。尽管它可以在 25 秒内获取 40K 条记录，但我们应该使用专业 DBA 在 AskTom 或其他网站上找到的 GTT（全局临时表）。但我只能找到 SQL 示例，而不是 Java 代码。

我能想象的是：

• createNativeQuery("create global temporary table GTT_COMPANIES if not exist (companyId varchar(32)) ON COMMIT DELETE ROWS;");并执行（这里需要索引吗？）
• createNativeQuery("insert into GTT_COMPANIES (list)"); query.bind("1", query.getCompanyIds());并执行（我们可以绑定一个列表并插入它吗？）
• 使用 CriteriaQuery 从该表中进行选择（但我怀疑，因为 CriteriaQueryBuilder 将需要预先生成类型安全的元模型类，而这里我们没有实体；这是一个临时表，没有模型实体映射到它）
• 而且，即使列表大小小于 1000，我们是否还需要创建 GTT？通常它很大，700~800。

那么，有什么建议吗？有人有 Hibernate CriteriaQuery + Oracle GTT 的工作示例吗？

整个方法是这样的：

    public List<ItemHistory> findByIdTypePermissionAndOrganizationIds(final Query<String> query, final ItemIdType idType) throws DataLookupException {
        String id = query.getObjectId();
        String type = idType.name();
        Set<String> companyIds = query.getCompanyIds();
        Set<String> allowedOrgIds = query.getAllowedOrganizationIds();
        Set<String> excludedOrgIds = query.getExcludedOrganizationIds();
        // if no orgs are allowed, we should return empty list
        if (CollectionUtils.isEmpty(allowedOrgIds)) {
            return Collections.emptyList();
        }
        try {
            CriteriaBuilder builder = entityManager.getCriteriaBuilder();
            CriteriaQuery<ItemHistory> criteriaQuery = builder.createQuery(ItemHistory.class);
            Subquery<String> subQueryCompanyIds = filterByPermissionAndOrgIdsInSubquery(query, builder, criteriaQuery);


            Subquery<String> subQueryCompanyIds = criteriaQuery.subquery(String.class);
            Root<Company> companies = subQueryCompanyIds.from(Company.class);
            companies.alias(COMPANY_ALIAS);
            Path<String> orgIdColumn = companies.get(Company_.organizationId);
            /* 1. get permission based restrictions */
            // select COMPANY_ID where (ORG_ID in ... or like ...) and (ORG_ID not in ... and not like ...)
            // actually query.getExcludedOrganizationIds() can also be very long list(1000+), but let's do it later
            Predicate permissionPredicate = getCompanyIdRangeByPermission(
                    builder, query.getAllowedOrganizationIds(), query.getExcludedOrganizationIds(), orgIdColumn
            );
            /* 2. get org id based restrictions, which was done on top of permission restrictions */
            // ... and where (ORG_ID in ... or like ...)
            // process companyIds with and without "*" by adding different predicates, like (xxx%, yyy%) vs in (xxx, yyy) 
            // here, query.getCompanyIds() could be very long, may be 1000+
            Predicate orgIdPredicate = groupByWildcardsAndCombine(builder, query.getCompanyIds(), orgIdColumn, false);
            /* 3. Join two predicates with AND, because originally filtering is done twice, 2nd is done on basis of 1st */
            Predicate subqueryWhere = CriteriaQueryUtils.joinWith(builder, true, permissionPredicate, orgIdPredicate);  // join predicates with AND
            subQueryCompanyIds.select(companies.get(Company_.id)); // id -> COMPANY_ID
            if (subqueryWhere != null) {
                subQueryCompanyIds.where(subqueryWhere);
            } else {
                LOGGER.warn("Cannot build subquery of org id and permission. " +
                                "Org ids: {}, allowed companies: {}, excluded companies: {}",
                        query.getCompanyIds(), query.getAllowedOrganizationIds(), query.getExcludedOrganizationIds());
            }
            Root<ItemHistory> itemHistory = criteriaQuery.from(ItemHistory.class);
            itemHistory.alias(ITEM_HISTORY_ALIAS);
            criteriaQuery.select(itemHistory)
                    .where(builder.and(
                            builder.equal(getColumnByIdType(itemHistory, idType), id),
                            builder.in(itemHistory.get(ItemHistory_.companyId)).value(subQueryCompanyIds)
                    ))
                    .orderBy(builder.desc(itemHistory.get(ItemHistory_.eventDate)));
            TypedQuery<ItemHistory> finalQuery = entityManager.createQuery(criteriaQuery);
            LOGGER.trace(LOG_MESSAGE_FINAL_QUERY, finalQuery.unwrap(org.hibernate.Query.class).getQueryString());
            return finalQuery.setMaxResults(MAX_LIST_FETCH_SIZE).getResultList();
        } catch (NoResultException e) {
            LOGGER.info("No item history events found by permission and org ids with {}={}", type, id);
            throw new DataLookupException(ErrorCode.DATA_LOOKUP_NO_RESULT);
        } catch (Exception e) {
            LOGGER.error("Error when fetching item history events by permission and org ids with {}={}", type, id, e);
            throw new DataLookupException(ErrorCode.DATA_LOOKUP_ERROR,
                    "Error when fetching item history events by permission and org ids with " + type + "=" + id);
        }
    }

标签： javaoraclecriteriaquery