Re: Advanced Replication Performance problems

From: <OKukartsev_at_hotmail.com>
Date: Tue, 22 Jun 1999 19:25:56 GMT
Message-ID: <7konvm$hp3$1@nnrp1.deja.com>

I'm sorry about my previous posting...
There are more details about this problem:

CREATE TABLE catalogpage
 (

  pageid                     NUMBER(9) NOT NULL,
  catalogid                  NUMBER(9) NOT NULL,
  image                      VARCHAR2(100) NOT NULL,
  ocrtext                    VARCHAR2(100) NOT NULL

 )

ALTER TABLE catalogpage
 ADD CONSTRAINT pk_catalogpage PRIMARY KEY (pageid,catalogid)

Oracle generate those statements to perform replication:

delete from "CATALOGPAGE"

    where (

            decode(substr(column_sent$_varchar2, 1, 1),
                   'N', 'Y',
                   decode("CATALOGID1_o", "CATALOGID", 'Y', 'N')) = 'Y'
    and
            decode(substr(column_sent$_varchar2, 2, 1),
                   'N', 'Y',
                   decode("IMAGE2_o", "IMAGE", 'Y', 'N')) = 'Y'
    and
            decode(substr(column_sent$_varchar2, 3, 1),
                   'N', 'Y',
                   decode("OCRTEXT3_o", "OCRTEXT", 'Y', 'N')) = 'Y'
    and
            decode(substr(column_sent$_varchar2, 4, 1),
                   'N', 'Y',
                   decode("PAGEID4_o", "PAGEID", 'Y', 'N')) = 'Y'
          );

I.e. it doesn't use PRIMARY KEY to delete

---

(Look at last 2 rows)
update "CATALOGPAGE" set

      "CATALOGID" =
        decode(substr(column_changed$_varchar2, 1, 1),


            'N', "CATALOGID",


            'Y', "CATALOGID1_n",


            NULL, decode("CATALOGID1_o",
                         "CATALOGID1_n","CATALOGID",
                         "CATALOGID1_n")),
      "IMAGE" =
        decode(substr(column_changed$_varchar2, 2, 1),


            'N', "IMAGE",


            'Y', "IMAGE2_n",


            NULL, decode("IMAGE2_o",
                         "IMAGE2_n","IMAGE",
                         "IMAGE2_n")),
      "OCRTEXT" =
        decode(substr(column_changed$_varchar2, 3, 1),


            'N', "OCRTEXT",


            'Y', "OCRTEXT3_n",


            NULL, decode("OCRTEXT3_o",
                         "OCRTEXT3_n","OCRTEXT",
                         "OCRTEXT3_n")),
      "PAGEID" =
        decode(substr(column_changed$_varchar2, 4, 1),


            'N', "PAGEID",


            'Y', "PAGEID4_n",


            NULL, decode("PAGEID4_o",
                         "PAGEID4_n","PAGEID",
                         "PAGEID4_n"))
    where (((1 = 1 and
             decode(substr(column_changed$_varchar2, 2, 1),
                     'N', 'Y',
                     'Y', 'N',
                     decode("IMAGE2_o", "IMAGE2_n", 'Y', 'N')) = 'Y' and
             decode(substr(column_changed$_varchar2, 3, 1),
                     'N', 'Y',
                     'Y', 'N',
                     decode("OCRTEXT3_o", "OCRTEXT3_n", 'Y', 'N')) = 'Y'
and
             1 = 1)) or
           (1 = 1 and
            decode(substr(column_sent$_varchar2, 2, 1),
                   'N', 'Y',
                   decode("IMAGE2_o", "IMAGE", 'Y', 'N')) = 'Y' and
            decode(substr(column_sent$_varchar2, 3, 1),
                   'N', 'Y',
                   decode("OCRTEXT3_o", "OCRTEXT", 'Y', 'N')) = 'Y' and
            1 = 1))
      and "CATALOGID1_o" = "CATALOGID"
      and "PAGEID4_o" = "PAGEID";

I.e. it does use PRIMARY KEY to update

Is there any way to force Oracle Replication to use PRIMARY KEY to perform delete?

OK

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 22 1999 - 14:25:56 CDT