asked on

Parallel hint not working in an insert

Here is my INSERT pseudo code. How come parallel not working? Working on another insert in the same database with same source table. Thanks.

INSERT /*+ append */ INTO BIG_TABLE
SELECT /*+ index (source_table my_idx1) parallel_index(source_table, my_idx1,14)
DISTINCT ...
FROM
                  (SELECT /*+ index (source_table my_idx1) parallel_index(source_table, my_idx1,14)
           from ....big query)
            where ...

SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ewang1205

ASKER

Here is the results. Parallel works on this server because some other parallel queries work fine. I guess my query may be too complex.

SQL> show parameter parallel;

NAME TYPE VALUE
------------------------------------ ----------- ---------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 385
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

ewang1205

ASKER

Might be limitation on parallel. Will parallel still work if I have ROLLUP function in the SQL query? Thanks.

ewang1205

ASKER

Looks like parallel doesn't work on ROLLUP function in the SQL query. I tried without ROLLUP function in the SQL query and parallel hint works.

SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial