| History Tables Search Criteria [message #640166] |
Thu, 23 July 2015 04:23  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
Hi Friends,
Please help to advise an approach on below scenario
Table "T1" (columns as C1 & indexed) has 2million records and it has 10 history tables of same structure/data volumes i.e., T2, T3, T4, ... T11
For each record in T1.C1, I've to search the not null record starting from T2, T3, T4 & ... if I found something at t2 itself then I can stop there itself else continue still table T10.
As these tables are history tables(even tough column C1 is indexed) and are of 2millions records each, don't want to join all the tables in a single query.
Help to tune the search.
|
|
|
|
|
|
| Re: History Tables Search Criteria [message #640171 is a reply to message #640166] |
Thu, 23 July 2015 04:47  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
oraranjangmailcom wrote on Thu, 23 July 2015 14:53it has 10 history tables of same structure/data volumes i.e., T2, T3, T4, ... T11
What is the business need to have 10 identical tables?
Quote:and are of 2millions records each
2 million rows X 10 tables = 20 million rows, with proper indexes and constraints it is not a huge operation. Anyway, first answer the first question, why do you have 10 identical tables?
|
|
|
|
|
|
|
|
| Re: History Tables Search Criteria [message #640285 is a reply to message #640211] |
Sun, 26 July 2015 22:59 |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
ok, all the 10 tables are history tables with data based on different date range.
I am fine to choose join condition, but by any chance is there a way to avoid the use of all the tables i.e., use the second table only when the first join doesn't return any records & so on ....
Note: currently, due to environmental limitations ( partition is disabled) I cannot opt for partition tables.
|
|
|
|
|
|
| Re: History Tables Search Criteria [message #640291 is a reply to message #640285] |
Mon, 27 July 2015 00:19 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
oraranjangmailcom wrote on Mon, 27 July 2015 05:59
...
I am fine to choose join condition, but by any chance is there a way to avoid the use of all the tables i.e., use the second table only when the first join doesn't return any records & so on ....
...
Already answered, use UNION ALL.
oraranjangmailcom wrote on Mon, 27 July 2015 05:59
...
Note: currently, due to environmental limitations ( partition is disabled) I cannot opt for partition tables.
Search for PARTITIONED VIEW.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
