4 Replies Latest reply: Oct 5, 2009 2:17 AM by 656310 RSS

    How to disable parallel query for specified table?

    656310
      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
          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
            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-Oracle
              >
              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
                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.