 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / November 2006sql question
hi, if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29 million records and i divide table into small tables each having data for one year like table1_2005 only contains records with year(c2) = 2005. how can i create view on these tables so that from view it appears like single table and there has to be no change in middleware code( java ). i am sorry for replying to my own message but do not know how to edit it i know that union of all table will work but that will not use any index on tables. > hi, > [quoted text clipped - 5 lines] > how can i create view on these tables so that from view it appears like > single table and there has to be no change in middleware code( java ). >i am sorry for replying to my own message but do not know how to edit > it > > i know that union of all table will work but that will not use any > index on tables. I don't know why you say it will not use an index. Normally it would use an index. But you may need to post all of your DDL to look at what you are doing. Right-- a UNION ALL view should be exactly what you need. Are you saying that you've defined indexes on the base tables but they're not being used, or that it's your understanding that such indexes *won't* be used (i.e. it's a DB2 thing, not a circumstantial thing)? --Jeff > >i am sorry for replying to my own message but do not know how to edit > > it [quoted text clipped - 5 lines] > index. But you may need to post all of your DDL to look at what you are > doing. Your best bet *is* a UNION ALL view, with indexes defined on the base tables. Why do you say that no indexes will be used? --Jeff > i am sorry for replying to my own message but do not know how to edit > it [quoted text clipped - 11 lines] > > how can i create view on these tables so that from view it appears like > > single table and there has to be no change in middleware code( java ). > hi, > [quoted text clipped - 5 lines] > how can i create view on these tables so that from view it appears like > single table and there has to be no change in middleware code( java ). Another option is to use MDC (V8 and V9) or Range Partitioning (V9 only). Then you really have only 1 table. > hi, > > if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29 > million records and i divide table into small tables each having data > for one year like table1_2005 only contains records with year(c2) = > 2005. If you are on V9, you can make use of the table partitioning feature, i.e. range partitioning. Then you don't have to worry about views etc.  SignatureKnut Stolze DB2 Information Integration Development IBM Germany > If you are on V9, you can make use of the table partitioning feature, i.e. > range partitioning. Then you don't have to worry about views etc. To the best of my knowledge the table partitioning feature is only available in the enterprise edition of db2 v9. So, this is only a viable solution if you are targetting the enterprise edition. > > If you are on V9, you can make use of the table partitioning feature, i.e. > > range partitioning. Then you don't have to worry about views etc.To the best of my knowledge the table partitioning feature is only > available in the enterprise edition of db2 v9. So, this is only a > viable solution if you are targetting the enterprise edition. i did not test it yet but i am assumming that if i create view like view abc as select * from table1_2005 union all select * from table1_2006 and then query like select * from abc where c2 = '05062005' , it will not use existing index on both table if all underlying tables have index on column c2 which is a date column i will also test this today but please share with me if someone already know the answer >> > If you are on V9, you can make use of the table partitioning feature, >> > i.e. [quoted text clipped - 13 lines] > i will also test this today but please share with me if someone already > know the answer It will use the index. You should probably read this article to understand UNION ALL views: http://www-128.ibm.com/developerworks/db2/library/techarticle/0202zuzarte/0202zu zarte.pdf > It will use the index. > > You should probably read this article to understand UNION ALL views: > > http://www-128.ibm.com/developerworks/db2/library/techarticle/0202zuzarte/0202zu zarte.pdf Just to clarify, if the C2 column is defined as a DATE in DB2, then Where clause should look like this: WHERE C2 = '2005-06-05' If the column is defined as CHAR(8), then you could use WHERE C2 = '05062005' A DATE column only takes up 4 bytes of storage in DB2. > > It will use the index. > [quoted text clipped - 8 lines] > > A DATE column only takes up 4 bytes of storage in DB2. unfortunately, it is a character column and i will convert this to date soon thanks for the article on partitioning using union all view everyone in this group is awesome i will try implement all this and will get back to you Once you've converted that field to a date, you'll want to make sure you include the date range within the view definition for each of the sub-tables. For example: create view ..... select c1, c2, date_col from table1 where date_col between '01/01/2004' and '12/31/2004' union all select c1, c2, date_col from table2 where date_col between '01/01/2005' and '12/31/2005' union all select c1, c2, date_col from table3 where date_col between '01/01/2006' and '12/31/2006' If you do this, then any queries off the view that are for a specific date range should hit only the qualifying tables. Good Luck, Greig Wise > > > It will use the index. > > [quoted text clipped - 14 lines] > everyone in this group is awesome > i will try implement all this and will get back to you > unfortunately, it is a character column and i will convert this to date > soon If the column C2 is CHAR(8) and value is 'mmddyyyy', you can change it to DATE by the following expression. DATE(TRANSLATE('EFGH-AB-CD', C2, 'ABCDEFGH')) > > > If you are on V9, you can make use of the table partitioning feature, i.e. > > > range partitioning. Then you don't have to worry about views etc.To the best of my knowledge the table partitioning feature is only [quoted text clipped - 11 lines] > i will also test this today but please share with me if someone already > know the answer Rule number one, never use SELECT *. Rule number two, see rule number one. Does a query on each of the underlying TABLEs use that INDEX for the same query? B. We use the union all view and indexes are used without any issues... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.