Split table

[sujithkumarsl]

Hi i want to split one large table into two. But here my problem is, i need to query from both the tables, which should give the data same as the parent table. For example If the large table is like

COLA COLB
1 A
2 B
3 C
4 D
5 E

and if i split this into FIRST table

COLA COLB
1 A
3 C
5 E

Second table as

COLA COLB
2 B
4 D

Now i want to query the two tables, so that i should give the value order by COLA. Result shold be like

COLA COLB
1 A
2 B
3 C
4 D
5 E

My small attempt...

[WoutL]

This should do the trick:

select cola, colb
from(
select cola, colb from table1
union all
select cola, colb from table2
) d
order by cola

But I don't see why you should split a table in two.

Wout Louwers

[sujithkumarsl]

Actually i have one billion records in this table all are logs from a server machine. But only some specific error logs are used to process. To get better performance i am planning to do the same

My small attempt...

[J4amieC]

You'd be better off archiving out old records and properly indexing your main table.

[sujithkumarsl]

i am creating some reports by parsing the error logs in that tables. SO i need all the error logs in the table. That y planed to move irrelevant data. The table is indexed... Any other technique?

My small attempt...

[Mycroft Holmes]

Look into partitioning the table by periods.

Never underestimate the power of human stupidity RAH

[sujithkumarsl]

That also did.. Right now i have different partition files to keep data for this table

My small attempt...