MVSFORUMS.com

[psridhar]

Hi,

I have a table from which I have to pick up the rows as follows.

SELECT C1, C2.......
FROM TABLE 1
WHERE DATE(TBL_INSERT_TIMESTAMP) = GIVEN DATE;

Since this table is having rows around 20 lakhs and we expect a volume of 50 to 60 thousand rows per day, this SQL taking more time. The idea I got is creating an index on the date as follows.

CREATE TYPE 2 INDEX INDEX1
ON TABLE1
(DATE(TBL_INSERT_TIMESTAMP) ASC )

But it gave an error

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ) ,

DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

Can you please suggest me any other alternate?

Thanks in Advance
Sridhar P

[psridhar]

Thanks alot Ravi..........

Now I will eloborate my problem more clearly. I have an SQL as follows.

SELECT C1, C2
FROM T1, T2
WHERE T1.YEAR = GIVEN YEAR
AND T1.S_NO = T2.S_NO
AND DATE(T1.INSERT_TMSTMP) = GIVEN DATE

Primary Key for T1 is YEAR and S_NO. S_NO is a column in both tables.

I have an index on T1 with columns YEAR and S_NO.

This index is taken but with Match Columns only 1. Here I have an another doubt. Why the Match Columns is only 1. As per my knowledge, I expect Match Column = 2. What I guess is Since S_NO of T1 is compared with S_NO of T2, Match Columns is only 1, that is only T1.YEAR. Is it right? Most of the rows are having T1.YEAR as 2004 and that is why Index1 is not of much use. That is why I thought of going for an index on date(INSERT_TMSTMP).

On top of all these, I ( not only me.... but also all the people who are INTARETED in learning) am very very thankful to you for your help....

[kolusu]

I think the join of the tables is what is causing the problem. The MatchCOlS in this case is only one. Check this link for explanation of data access.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAGH10/5.10.2?DT=20010212102121

Try this sql which will avoid the Join and will work on a subquery which will just be execueted once.