This discussion is archived
4 Replies Latest reply: Oct 5, 2009 12:17 AM by 656310 RSS

How to disable parallel query for specified table?

656310 Newbie
Currently Being Moderated
Hallo everybody,
does anybody know, if it's possible to disable parallel query execution for specified table?

DB version Oracle 10g - 10.2.0.4.0
DB type: Datawarehouse

I know how to disable parallel queries for whole database (using parallel_max_servers=0).

Table has 0 degree of paralellism and I would like to achive to not use parallel query, even in case if somebody use --+ parallel hint in SELECT query.

Maybe it's possible via some table trigger?

Thanks for ideas.
Filip
  • 1. Re: How to disable parallel query for specified table?
    Chivalri Newbie
    Currently Being Moderated
    I think the following should work:
    ALTER TABLE table_name NOPARALLEL;

    You can also use:
    ALTER TABLE table_name PARALLEL (DEGREE 1 INSTANCES 1);
  • 2. Re: How to disable parallel query for specified table?
    656310 Newbie
    Currently Being Moderated
    unfortunatelly, it doesn't work this way :-( .. (table has already set parallel degree to 0)

    --ALTER
    ALTER TABLE ACCOUNT_TRANSACTIONS NOPARALLEL;

    --SELECT
    SELECT --+ parallel (act,10)
    ACCTP_KEY,
    TO_CHAR (ACCTRN_ACCOUNTING_DATE, 'YYYYMM'),
    SUM (ACCTRN_AMOUNT_CZK) amount
    FROM dwh_owner.account_transactions act
    where acctp_key=101
    GROUP BY ACCTP_KEY, TO_CHAR (ACCTRN_ACCOUNTING_DATE, 'YYYYMM')

    Query still use parallel threads.

    What I need is to disable parallel execution for user queries with parallel hints (like query above)

    Thanks
  • 3. Re: How to disable parallel query for specified table?
    UweHesse Expert
    Currently Being Moderated
    >
    does anybody know, if it's possible to disable parallel query execution for specified table?

    DB version Oracle 10g - 10.2.0.4.0
    DB type: Datawarehouse

    I know how to disable parallel queries for whole database (using parallel_max_servers=0).

    Table has 0 degree of paralellism and I would like to achive to not use parallel query, even in case if somebody use --+ parallel hint in SELECT query.

    Maybe it's possible via some table trigger?
    >

    There are at least two possibilities to achieve that:

    1. Write a logon trigger that disables parallel query for the user that selects with parallel hints
    2. Use a resource manager plan that sets the parallel degree to 1 (no parallelization) for that group in which you put the users that ought not to do parallel statements.

    Kind regards
    Uwe

    http://uhesse.wordpress.com
  • 4. Re: How to disable parallel query for specified table?
    656310 Newbie
    Currently Being Moderated
    Thanks for reply, but it is still not what I want to achieve.

    I want to disable parallel execution ONLY FOR 1 TABLE, while it will be enabled for others ..

    It seems to me, that it's not possible in Oracle 10g :-(

    Note: Reason is that there is a Oracle bug - If you are doing move&compress on a large partitioned table with bitmap indexes and simultaneously you are quering this table with parallel hint, sometimes it returns incorrects counts of records in partitions. Temporary workaround should be disabling parallel for this table and we create SR also.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points