Hi ,
I wanted to do a self join with a table which it is having data around 10 crores of rows. ,Which is the best method to do that.
Please suggest..
Thanks in advance..
Performing Lookups on Large tables
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54
- Joined: Mon May 28, 2007 12:41 am
- Location: Chennai
hi
I think u sud wirte a user defined sql query.
Re: Performing Lookups on Large tables
Sorry, but could you please specify what are 10 crores ? 10 millions rows ? In that case I would check if this is really usefull to read 10mio rows.Das wrote:Hi ,
I wanted to do a self join with a table which it is having data around 10 crores of rows. ,Which is the best method to do that.
Please suggest..
Thanks in advance..
Thanks in advance
Re: Performing Lookups on Large tables
10 crores = 100,000,000 rows, before self join make sure that you have indexes on this table or make partition and use the table then only performance will be good.caltog wrote:Sorry, but could you please specify what are 10 crores ? 10 millions rows ? In that case I would check if this is really usefull to read 10mio rows.Das wrote:Hi ,
I wanted to do a self join with a table which it is having data around 10 crores of rows. ,Which is the best method to do that.
Please suggest..
Thanks in advance..
Thanks in advance
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
You could use a database input stage and a lookup stage to join the table to itself - but you would be reading the 10 crores of data in twice. The user-defined SQL in a database input stage lets you join the table on the database engine and bring the data to your ETL server just once. It performs a single intensive SQL statement rather than millions of small SQL statements which should overall be a lighter load on your database - as long as you have the right indexing.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn