Partition Users for Self-Service Optimization

It is strongly recommended that you use the functions available for the Self-Service Optimization user migration and migrate no more than 2000 users at one time.

If you need to migrate more than 2000 self service users you must partition them into batches. This task shows you how to partition users based on the contact party ID value. The users are partitioned into batches and the party ID of the first contact and last contact in the batch are shown. This migration strategy assumes that no users have been migrated. If some users have already been migrated then this task will still work, but if a large number of users have already been migrated this partitioning method may not work as expected.

Note: See the section "Create a Data Model from a Custom Query" for more information on how to run the following queries.

First, for information only, use the following query to find the total number of users:

SELECT  count(*)
FROM fusion.svc_self_service_roles
WHERE relationship_type_cd = 'ORA_CSS_USER'
    AND delete_flag = 'N'
    AND current_idp_cd != 'ORA_CSS_IDP_IDCS'

Next, use the following query that uses the DENSE_RANK analytic function to partition the users into batches. The batch size in this example is 2000.

SELECT
    batch_num,
    MIN(contact_party_id) first_contact_party_id,
    MAX(contact_party_id) last_contact_party_id,
    COUNT(*) batch_size
FROM
    (
        SELECT
            contact_party_id,
            floor((rank - 1) / 2000) batch_num
        FROM
            (
                SELECT
                    DISTINCT contact_party_id,
                    DENSE_RANK() OVER(
                        ORDER BY
                            contact_party_id
                    ) AS rank
                FROM fusion.svc_self_service_roles
                WHERE
                    relationship_type_cd = 'ORA_CSS_USER'
                    AND delete_flag = 'N'
                    AND current_idp_cd != 'ORA_CSS_IDP_IDCS'
                ORDER BY contact_party_id
            )
    )
GROUP BY batch_num
ORDER BY batch_num

The output shows the party ID of the first and last contact in each batch.