Home » SQL & PL/SQL » SQL & PL/SQL » multiple self join
multiple self join [message #286677] Sun, 09 December 2007 13:59 Go to next message
iowa
Messages: 4
Registered: December 2007
Junior Member
Hi,

Looking into an external system which is on Oracle10G and Solaris 10, got a query with multiple self joins. Is there a way to improve it without these many self joins?

select
a1.day_ind        monday_id,
a2.day_ind        tuesday_id,
a3.day_ind        wednesday_id,
a4.day_ind        thursday_id,
a5.day_ind        friday_id,
a6.day_ind        saturday_id,
a7.day_ind        sunday_id
from 
     day_ind_map a1,
     day_ind_map a2,
     day_ind_map a3,
     day_ind_map a4,
     day_ind_map a5,
     day_ind_map a6,
     day_ind_map a7,
     calendar c
where c.monday_type=a1.old_day_ind
and   c.tuesday_type=a2.old_day_ind
and   c.wednesday_type=a3.old_day_ind
and   c.thursday_type=a4.old_day_ind
and   c.friday_type=a5.old_day_ind
and   c.saturday_type=a6.old_day_ind
and   c.sunday_type=a7.old_day_ind
;



Got another table which needs to self join 20 times, because the table consists of fields acct1, acct2, acct3, .... acct20. Just looking for a better way

Thanks a lot for your time!
Re: multiple self join [message #286678 is a reply to message #286677] Sun, 09 December 2007 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
REDESIGN the suboptimal tables!

The table should have a DAY_OF_WEEK column!
Re: multiple self join [message #286680 is a reply to message #286678] Sun, 09 December 2007 16:08 Go to previous messageGo to next message
iowa
Messages: 4
Registered: December 2007
Junior Member
Thanks for the quick response.

This is a legacy system and we're trying to migrate data to another system. Legacy system is not normalized at all.

Here is the table definition and sample data. Could you shed some light?

SQL> Create table  calendar (
  2  monday_id number(2),
  3  tuesday_id  number(2),
  4  wednesday_id number(2),
  5  thursday_id number(2),
  6  friday_id number(2),
  7  saturday_id number(2),
  8  sunday_id number(2)
  9  );

Table created.

SQL> insert into calendar values (10,11,12,13,17,18,22);

1 row created.

SQL> insert into calendar values (21,23,12,13,17,18,22);

1 row created.

SQL> insert into calendar values (21,23,12,13,17,18,23);

1 row created.

SQL> commit;

Commit complete.

SQL> 
Re: multiple self join [message #286682 is a reply to message #286677] Sun, 09 December 2007 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> Could you shed some light?
If you turn on a flashlight in a blackhole, what will you see?

Why is CALENDER in the FROM clause since it returns no data to the SELECT clause?

Since you do NOT specify what the result set, I have no good idea exactly problem you are trying to solve, but I suspect it could be replaced by just using TO_CHAR.

>SQL> insert into calendar values (10,11,12,13,17,18,22);
>SQL> insert into calendar values (21,23,12,13,17,18,22);
>SQL> insert into calendar values (21,23,12,13,17,18,23);

What in the world are these values?

Why have 2 rows with same values?
>SQL> insert into calendar values (21,23,12,13,17,18,22);
>SQL> insert into calendar values (21,23,12,13,17,18,23);



If Monday is the 10th, how in the world is Friday the 17th?
>SQL> insert into calendar values (10,11,12,13,17,18,22);

[Updated on: Sun, 09 December 2007 19:53] by Moderator

Report message to a moderator

Re: multiple self join [message #286959 is a reply to message #286682] Mon, 10 December 2007 13:00 Go to previous messageGo to next message
iowa
Messages: 4
Registered: December 2007
Junior Member

anacedent,

Thanks a lot for your time. Here is the table definitions for calendar and day_ind_map; and sample data.


Is there a better way to get the same data without self joins?

As I mentioned earlier, I have another query very similar to this, but with million records and need to do self join 20 times.

And Oracle server has a space limitation of 5GB for TEMP and SGA is 1.5GB for the instance. I'm filling up that space with the query easily. SO looking for some pointers to make it better.


Thanks!!


SQL> create table day_ind_map (
  2   day_ind number(2),
  3   old_day_ind number(2),
  4   description  varchar2(50))
  5  /

Table created.

SQL> Create table  calendar (
  2  monday_type number(2),
  3  tuesday_type  number(2),
  4  wednesday_type number(2),
  5  thursday_type number(2),
  6  friday_type number(2),
  7  saturday_type number(2),
  8  sunday_type number(2)
  9  )
 10  /

Table created.

SQL> insert into calendar values (10,11,12,13,17,18,22)
  2  /

1 row created.

SQL> insert into calendar values (21,23,12,13,17,18,22)
  2  /

1 row created.

SQL>  insert into calendar values (21,23,12,13,17,18,23)
  2  /

1 row created.

SQL> 
SQL> insert into day_ind_map values ( 1, 10, 'Peak')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 2, 11, 'Offpeak')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 3, 12, 'Anytime')
  2  /

1 row created.

SQL> insert into day_ind_map values (4, 13, 'Offpeak23_24')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 5, 17, 'Exclusion_free')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 6, 18, 'FreedayOffpeak')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 7, 21, '1800:2000_TT17')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 8, 22, 'Holiday')
  2  /

1 row created.

SQL> insert into day_ind_map values ( 9, 23, 'Weekday')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select
  2  a1.day_ind        monday_id,
  3  a2.day_ind        tuesday_id,
  4  a3.day_ind        wednesday_id,
  5  a4.day_ind        thursday_id,
  6  a5.day_ind        friday_id,
  7  a6.day_ind        saturday_id,
  8  a7.day_ind        sunday_id
  9  from 
 10       day_ind_map a1,
 11       day_ind_map a2,
 12       day_ind_map a3,
 13       day_ind_map a4,
 14       day_ind_map a5,
 15       day_ind_map a6,
 16       day_ind_map a7,
 17       calendar c
 18  where c.monday_type=a1.old_day_ind
 19  and   c.tuesday_type=a2.old_day_ind
 20  and   c.wednesday_type=a3.old_day_ind
 21  and   c.thursday_type=a4.old_day_ind
 22  and   c.friday_type=a5.old_day_ind
 23  and   c.saturday_type=a6.old_day_ind
 24  and   c.sunday_type=a7.old_day_ind
 25  ;

 MONDAY_ID TUESDAY_ID WEDNESDAY_ID THURSDAY_ID  FRIDAY_ID SATURDAY_ID  SUNDAY_ID
---------- ---------- ------------ ----------- ---------- ----------- ----------
         1          2            3           4          5           6          8
         7          9            3           4          5           6          8
         7          9            3           4          5           6          9

SQL> 

Re: multiple self join [message #286963 is a reply to message #286677] Mon, 10 December 2007 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
On the production system, how many rows exist in the calendar table?

Describe in business terms the definition of the data in the calendar table.
Re: multiple self join [message #286969 is a reply to message #286963] Mon, 10 December 2007 13:50 Go to previous messageGo to next message
iowa
Messages: 4
Registered: December 2007
Junior Member
In the calendar table in production, around 25-30 records...

Business case is you could have a configuration where Monday can be offpeak for travel, Friday can be peak, and '1800:2000_TT17' means 1800 to 2000 hours on the day, it can have a different price point for the travel charge.


Thanks!
Re: multiple self join [message #286970 is a reply to message #286963] Mon, 10 December 2007 13:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
>> Legacy system is not normalized at all.

Actually the problem is that the legacy table IS normalized (too much). Not having repeating fields is usually advised - but not in this case. Having 7 repeating fields is usually the preferred diesing in this case... Wink

Address-line1, addresss-line2 is another common example.
Re: multiple self join [message #287003 is a reply to message #286970] Mon, 10 December 2007 20:05 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How did we get to 7 replies without someone mentioning PIVOT? This looks like just a bog-standard-garden-variety PIVOT to me.

Do a search in this forum.

Ross Leishman
Previous Topic: Cursor ...
Next Topic: trying to update table 1 but int he where clauses it is joining two tables
Goto Forum:
  


Current Time: Fri Dec 02 23:13:38 CST 2016

Total time taken to generate the page: 0.26196 seconds