Re: possible to use an exceptions table in parallel?

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Mon, 23 Jun 2008 12:00:55 -0500
Message-ID: <485FD6C7.2070004@gmail.com>

Rick

   Potentially, you could following syntax top add constraint and create index in parallel etc..

 alter table deldups add constraint u1 unique (a,b)  using index (create unique index d1_i1 on deldups (a,b) parallel (degree 4) )
 exceptions into sys.exceptions;

  Problem is still underlying SQL populating exceptions table does not use parallelism.

 insert into "SYS"."EXCEPTIONS"( "ROW_ID" , "OWNER" , "TABLE_NAME" , "CONSTRAINT") select /*+ all_rows ordered */ "A".rowid, 'DELDUPS', 'U1' from "DELDUPS" "A",
 (select /*+ all_rows */ "A" , "B" from "DELDUPS" "A" where( "A" is not null or "B" is not null) group by "A" , "B" having count(1) > 1) "B" where( "A"."A" is not null or "A"."B" is not null) and( sys_op_map_nonnull("A"."A") = sys_op_map_nonnull("B"."A") and sys_op_map_nonnull("A"."B") = sys_op_map_nonnull("B"."B"))

So, perhaps you could simulate exceptions outside the SQL with parallel hint as below? I would suggest use 10046 trace, find SQL inserting in to exceptions and modify to use parallelism or you might be able to modify table degree and CBO might choose that plan if cheaper..

explain plan for
 insert into "SYS"."EXCEPTIONS"( "ROW_ID" , "OWNER" , "TABLE_NAME" , "CONSTRAINT")
 select /*+ all_rows ordered parallel (a 4) */ "A".rowid, 'AVAIL', 'DELDUPS', 'U1'
  from "AVAIL"."DELDUPS" A,
  (select /*+ all_rows parallel (a 4) */ "A" , "B"     from "AVAIL"."DELDUPS" A
    where( "A" is not null or "B" is not null) group by "A" , "B" having count(1) > 1) "B"
  where( "A"."A" is not null or "A"."B" is not null) and( sys_op_map_nonnull("A"."A") = sys_op_map_nonnull("B"."A") and sys_op_map_nonnull("A"."B") = sys_op_map_nonnull("B"."B")) /

SQL> select * from table(dbms_xplan.display);

---

| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |

---

|   0 | INSERT STATEMENT       |             |     1 |    59 |     6 
|        |      |            |
|*  1 |  HASH JOIN             |             |     1 |    59 |     6 | 
29,02  | P->S | QC (RAND)  |
|*  2 |   TABLE ACCESS FULL    | DELDUPS     |    80 |  2640 |     1 | 
29,02  | PCWP |            |
|   3 |   VIEW                 |             |    80 |  2080 |     5 | 
29,01  | P->P | BROADCAST  |
|*  4 |    FILTER              |             |       |       |       | 
29,01  | PCWC |            |
|   5 |     SORT GROUP BY      |             |    80 |  2080 |     5 | 
29,01  | PCWP |            |
|   6 |      SORT GROUP BY     |             |    80 |  2080 |     5 | 
29,00  | P->P | HASH       |
|*  7 |       TABLE ACCESS FULL| DELDUPS     |    80 |  2080 |     1 | 
29,00  | PCWP |            |


---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - access(SYS_OP_MAP_NONNULL("A"."A")=SYS_OP_MAP_NONNULL("B"."A") AND
              SYS_OP_MAP_NONNULL("A"."B")=SYS_OP_MAP_NONNULL("B"."B"))
   2 - filter("A"."A" IS NOT NULL OR "A"."B" IS NOT NULL)

   4 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>1)    7 - filter("A"."A" IS NOT NULL OR "A"."B" IS NOT NULL)

Note: cpu costing is off

24 rows selected.

Cheers
Riyaj
Rick Ricky wrote:

> http://www.jlcomp.demon.co.uk/faq/duplicates.html
>
> I do not know how to do an
>
> 'alter table add unique constraint' in parallel.
>
> the only way I know how to use parallel is with a 'create index'. 
> Problem is 'exceptions into' does not seem to work with this statement.
>
> I have a big table. Anyway to use the exceptions table with the 
> parallel clause? if not it will run for hours.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 23 2008 - 12:00:55 CDT