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...