oracle-databaseoptimizationappendhint

Parallel hint in Oracle DB


I want to use parallel hint in my select query to improve performance. But this select statement contains more than one tables in the from clause. How can we use parallel hint in the select statement.

INSERT INTO /*+APPEND */ ITEM
    (
    )
    
    SELECT a FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>
    
    UNION
    
    SELECT a FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>

Solution

  • Let's say your PARALLEL_MAX_SERVERS value is 80. Then you can use it:

    INSERT INTO /*+APPEND */ ITEM
        (
        )
        
        SELECT /*+ PARALLEL(p, 8) PARALLEL(r, 16) */ a 
        FROM GTT_LINE p, GTT_LINE_XY r
        Where <condition>
        
        UNION
        
        SELECT /*+ PARALLEL(p, 8) PARALLEL(r, 16) */ a 
        FROM GTT_LINE p, GTT_LINE_XY r
        Where <condition>
    

    Note: 8 and 16 in sum shouldn't exceed the maximum value of 80. To check the maximum value you have to log-in as SYS or SYSDBA and run the next command:

    SELECT name, value FROM v$parameter WHERE name IN ('parallel_max_servers', 'parallel_execution_enabled');