Next: optimizer_secure_view_merging
From: xcure2k on 28 Dec 2005 10:23
Hi there,

I'm trying to solve an update, and simply can't find a way. It updates
1 table, that has a 3 column PK. See it here:

update
( select
t1.emp_cod t1emp,
t1.cednte_cedn t1cedn,
t1.titlo_noss_num t1noss,
t1.titlo_num_versao,
t2.emp_cod t2emp,
t2.cednte_cedn t2cedn,
t2.titlo_noss_num t2noss,
t2.renda_dat,
t2.renda_tx_equal_banco
from
tb_cob_renda t2,
tb_titlo t1
where
t2.emp_cod = t1.emp_cod and
t2.cednte_cedn = t1.cednte_cedn and
t2.titlo_noss_num = t1.titlo_noss_num and
t1.emp_cod = 1 and
t2.renda_dat = to_date('2005-10-28','yyyy-MM-dd') )
set titlo_num_versao = renda_tx_equal_banco;

Here is the script for the 2 tables:

CREATE TABLE "TB_TITLO" (
"EMP_COD" NUMBER(4,0) NOT NULL,
"CEDNTE_CEDN" NUMBER(10,0) NOT NULL,
"TITLO_NOSS_NUM" NUMBER(11,0) NOT NULL,
"TITLO_NUM_VERSAO" NUMBER(5,0)) ;

ALTER TABLE "TB_TITLO" ADD ( CONSTRAINT PK_TITLO PRIMARY KEY (
"EMP_COD", "CEDNTE_CEDN", "TITLO_NOSS_NUM" )) ;

CREATE TABLE "TB_COB_RENDA" (
"RENDA_DAT" DATE NOT NULL,
"EMP_COD" NUMBER(4,0) NOT NULL,
"CEDNTE_CEDN" NUMBER(10,0) NOT NULL,
"TITLO_NOSS_NUM" NUMBER(11,0) NOT NULL,
"RENDA_TX_EQUAL_BANCO" NUMBER(5)) ;

ALTER TABLE "TB_COB_RENDA" ADD ( CONSTRAINT PK_RENDA PRIMARY KEY (
"RENDA_DAT", "EMP_COD", "CEDNTE_CEDN", "TITLO_NOSS_NUM" )) ;

Why, if the 2 tables are connected and using their primary keys, I
always get the ORA-01779 (non key-preserved table)???

The update itself a bit more complex, updates many columns, and using
the:

update tb_titlo t1
set ( t1.titlo_num_versao ) =
( select renda_tx_equal_banco
from tb_cob_renda t2
where t2.emp_cod = t1.emp_cod
and t2.cednte_cedn = t1.cednte_cedn
and t2.titlo_noss_num = t1.titlo_noss_num
and t2.renda_dat = to_date('2005-10-28','yyyy-MM-dd') )
where t1.emp_cod = 1
and exists ( select 1
from tb_cob_renda
where tb_cob_renda.emp_cod = t1.emp_cod
and tb_cob_renda.cednte_cedn = t1.cednte_cedn
and tb_cob_renda.titlo_noss_num =
t1.titlo_noss_num
and tb_cob_renda.renda_dat =
to_date('2005-10-28','yyyy-MM-dd') );

works, but gives me poor performance.

Please!!!!!!!!! Help me!!!!
I'm using Oracle8i Enterprise Edition Release 8.1.7.4.1

PS. I prefer Oracle, but this specific issue was so simple in SQL
Server... (update t1 from t1, t2 where...) ... :(

From: xcure2k on 28 Dec 2005 10:54
By the way, i have searched other postings on this subject, have tried
to understand the manual, but it seems to me I'm doing the right thing
on the update, that is, using the PK for tb_titlo t1 (and even for t2),
but still i get the same error....
Sorry to post the "nth" version on the same topic...

From: William Robertson on 28 Dec 2005 20:08

xcure2k(a)gmail.com wrote:
> Hi there,
>
> I'm trying to solve an update, and simply can't find a way. It updates
> 1 table, that has a 3 column PK. See it here:
>
> update
> ( select
> t1.emp_cod t1emp,
> t1.cednte_cedn t1cedn,
> t1.titlo_noss_num t1noss,
> t1.titlo_num_versao,
> t2.emp_cod t2emp,
> t2.cednte_cedn t2cedn,
> t2.titlo_noss_num t2noss,
> t2.renda_dat,
> t2.renda_tx_equal_banco
> from
> tb_cob_renda t2,
> tb_titlo t1
> where
> t2.emp_cod = t1.emp_cod and
> t2.cednte_cedn = t1.cednte_cedn and
> t2.titlo_noss_num = t1.titlo_noss_num and
> t1.emp_cod = 1 and
> t2.renda_dat = to_date('2005-10-28','yyyy-MM-dd') )
> set titlo_num_versao = renda_tx_equal_banco;
>
> Here is the script for the 2 tables:
>
> CREATE TABLE "TB_TITLO" (
> "EMP_COD" NUMBER(4,0) NOT NULL,
> "CEDNTE_CEDN" NUMBER(10,0) NOT NULL,
> "TITLO_NOSS_NUM" NUMBER(11,0) NOT NULL,
> "TITLO_NUM_VERSAO" NUMBER(5,0)) ;
>
> ALTER TABLE "TB_TITLO" ADD ( CONSTRAINT PK_TITLO PRIMARY KEY (
> "EMP_COD", "CEDNTE_CEDN", "TITLO_NOSS_NUM" )) ;
>
> CREATE TABLE "TB_COB_RENDA" (
> "RENDA_DAT" DATE NOT NULL,
> "EMP_COD" NUMBER(4,0) NOT NULL,
> "CEDNTE_CEDN" NUMBER(10,0) NOT NULL,
> "TITLO_NOSS_NUM" NUMBER(11,0) NOT NULL,
> "RENDA_TX_EQUAL_BANCO" NUMBER(5)) ;
>
> ALTER TABLE "TB_COB_RENDA" ADD ( CONSTRAINT PK_RENDA PRIMARY KEY (
> "RENDA_DAT", "EMP_COD", "CEDNTE_CEDN", "TITLO_NOSS_NUM" )) ;
>
> Why, if the 2 tables are connected and using their primary keys, I
> always get the ORA-01779 (non key-preserved table)???
>
> The update itself a bit more complex, updates many columns, and using
> the:
>
> update tb_titlo t1
> set ( t1.titlo_num_versao ) =
> ( select renda_tx_equal_banco
> from tb_cob_renda t2
> where t2.emp_cod = t1.emp_cod
> and t2.cednte_cedn = t1.cednte_cedn
> and t2.titlo_noss_num = t1.titlo_noss_num
> and t2.renda_dat = to_date('2005-10-28','yyyy-MM-dd') )
> where t1.emp_cod = 1
> and exists ( select 1
> from tb_cob_renda
> where tb_cob_renda.emp_cod = t1.emp_cod
> and tb_cob_renda.cednte_cedn = t1.cednte_cedn
> and tb_cob_renda.titlo_noss_num =
> t1.titlo_noss_num
> and tb_cob_renda.renda_dat =
> to_date('2005-10-28','yyyy-MM-dd') );
>
> works, but gives me poor performance.
>
> Please!!!!!!!!! Help me!!!!
> I'm using Oracle8i Enterprise Edition Release 8.1.7.4.1
>
> PS. I prefer Oracle, but this specific issue was so simple in SQL
> Server... (update t1 from t1, t2 where...) ... :(

You can update tb_cob_renda here but not tb_titlo, because the same
tb_titlo row could (so far as the database knows) appear against more
than one tb_cob_renda. For example, of you join employees to
departments you can update the employee side of the join but not
departments.

In 9i you can use the BYPASS_UJVC hint (undocumented, unsupported, at
your own risk), though I don't know whether it existed in 8i.

From: William Robertson on 28 Dec 2005 20:13
William Robertson wrote:

> For example, of you join

= For example, if you join

They put those keys too close together.

From: xcure2k on 29 Dec 2005 07:32
Thank you William! I'll try the bypass in 8i, if not, i'll try hints or
something else! Thank you!!!

 |  Next  |  Last
Pages: 1 2
Next: optimizer_secure_view_merging