Java EE + Oracle: how to use GTT(global temporary table) to avoid possible long(1000+) IN clause?

Question

I have a query for Oracle database, built with CriteriaBuilder of Hibernate. Now, it has a IN clause which already takes about 800+ params.

Team says this may surpass 1000 and hitting the hard upper limit of Oracle itself, which only allows 1000 param for an IN clause. We need to optimize that.

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

So, two solutions I can think of:

1. The easy one, as now the list from :param3 to :param818 are below 1000, and in the future, we may hit 1000, we can separate the list if size > 1000, into another IN clause, so it becomes:

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

2. Both the original code and solution 1 are not very efficient already. Although it can fetch 40K records in 25 seconds, we should use a GTT(Global Temporary Table), as per what I can find on AskTom or other sites, by professional DBAs. But I can only find SQL examples, not Java code.

What I can imagine is:

• createNativeQuery("create global temporary table GTT_COMPANIES if not exist (companyId varchar(32)) ON COMMIT DELETE ROWS;"); and execute(Do we need index here?)
• createNativeQuery("insert into GTT_COMPANIES (list)"); query.bind("1", query.getCompanyIds()); and execute(can we bind a list and insert it?)
• use CriteriaQuery to select from this table(but I doubt, as CriteriaQueryBuilder will require type safe meta model class to be generated beforehand, and here we don't have the entity; this is an ad-hoc table and no model entity is mapped to it)
• and, do we need to create GTT even the list size is < 1000? As often it is big, 700~800.

So, any suggestion? Someone got a working example of Hibernate CriteriaQuery + Oracle GTT?

The whole method is like this:

    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);
        }
    }