Home » SQL & PL/SQL » SQL & PL/SQL » Deducing rows (Oracle 9i R2)
Deducing rows [message #582691] Sun, 21 April 2013 15:40 Go to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Hi all,

drop table test;
create table test
(
  my_env  varchar2(8)   ,
  id      int         
)
/

alter table test add constraint pk_test primary key (my_env, id);

insert into test values ('A', 1);
insert into test values ('A', 2);
insert into test values ('A', 3);
insert into test values ('B', 1);
insert into test values ('B', 2);



These rows are valued :

drop table valued_test;
create table valued_test
(
  my_env  varchar2(8)   ,
  id      int           ,    
  val     int     
)
/

alter table valued_test add constraint pk_vtest primary key (my_env, id);
alter table valued_test add constraint fk_vtest foreign key (my_env, id) references test(my_env, id);

insert into valued_test values ('A', 1, 100);
insert into valued_test values ('A', 3, 200);
insert into valued_test values ('B', 1, 100);


Now the rows of the table test can move from one situation to another.
To model the movement from one situation to another, there is another table test_history

drop table test_history;
create table test_history
(
  my_env      varchar2(8)   ,
  id          int           ,
  my_env_new  varchar2(8)   ,
  id_new      int         
)
/

alter table test_history add constraint pk_htest primary key (my_env, id);
alter table test_history add constraint fk_htest_old foreign key (my_env, id) references test(my_env, id);
alter table test_history add constraint fk_htest_new foreign key (my_env_new, id_new) references test(my_env, id);

insert into test values ('A1', 1);
insert into test_history values ('A', 1, 'A1', 1);


So the row (A,1) moved to (A1,1).
What I want to do is to replicate the valued rows with(A,1) to (A1,1).
if we issue select * from <some_query> we will get this :

MY_ENV           ID        VAL
-------- ---------- ----------
A                 1        100
A                 3        200
B                 1        100
A1                1        100


We can issue this query to get it :

select my_env, id, val
from valued_test
union
select my_env_new my_env, id_new, val
from valued_test, test_history
where 1 = 1
and valued_test.my_env = test_history.my_env
and valued_test.id = test_history.id
;

MY_ENV           ID        VAL
-------- ---------- ----------
A                 1        100
A                 3        200
A1                1        100
B                 1        100


It works fine, but when we have lot of data in test and valued_test, these query becomes very slow.
I think it is because of the union. So here are my questions :

1) could we remove the union
2) What columns in test, valued_test, test_history should we index to make the result be returned faster.

Thanks in advance,

Amine
Re: Deducing rows [message #582692 is a reply to message #582691] Sun, 21 April 2013 20:03 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
post EXPLAIN PLAN for query below
SELECT my_env, 
       id, 
       val 
FROM   valued_test 
UNION 
SELECT my_env_new my_env, 
       id_new, 
       val 
FROM   valued_test, 
       test_history 
WHERE  1 = 1 
       AND valued_test.my_env = test_history.my_env 
       AND valued_test.id = test_history.id 
Re: Deducing rows [message #582699 is a reply to message #582692] Mon, 22 April 2013 00:46 Go to previous messageGo to next message
isri
Messages: 14
Registered: November 2012
Junior Member
I would like to ask - why are you using that 1 = 1 condition?
As it is always true, would it make any difference on results?
Re: Deducing rows [message #582706 is a reply to message #582699] Mon, 22 April 2013 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference.
It is just often used to not check if there is already a previous condition or not and to think if you have to write WHERE or AND.

Regards
Michel

[Updated on: Mon, 22 April 2013 02:06]

Report message to a moderator

Re: Deducing rows [message #582713 is a reply to message #582691] Mon, 22 April 2013 03:42 Go to previous message
Amine
Messages: 264
Registered: March 2010
Senior Member

I teach myself to write like this when I construct dynamic SQL. Then, for me, all clauses begin with "and".
Previous Topic: how to find last or latest inserted row.?
Next Topic: Please help me!
Goto Forum:
  


Current Time: Sun Sep 21 11:55:17 CDT 2014

Total time taken to generate the page: 0.18694 seconds