I have a function based index on a column like TRUNC(DATECREATED). When I use the predicate expression do I have to do something to force this index. When I did the DB trace the generated SQL didn't use the index the SQL was like

"DATECREATED" BETWEEN :Datecreated2 AND :Datecreated3

. So it didn't use the index.

Thanks Sam

Generally, the generated SQL will not include the name of the indexes used - the RDBMs will select what it thinks is the best index for the query at the time that it processes the query. This can usually be seen by examing the query execution plan. I know this is easy do to in SQL Server - I'm assuming Oracle has something similar?

If you examine the execution plan, does it show your index being used ?

Matt

In the execution plan this index doesn't show.

This means the the query optimizer does not think that the index is worth using for the query - why do you think the index should be used..?

Matt

Because When I take the query and run directly on oracle and if I use trunc(datecreated) it uses the index. But if I just use date created then it doesn't use the index.

As Matt said, that is a decision of Oracle SQL Query Optimizer. LLBLGen just generate the sql for you.

What you can do is use a DBFunctionCall so include the TRUNC function in the predicate so your query would use the involved index.

Thanks.

could you please let me know how to use TRUNC function in the predicate.

Please check Calling a database function

Since the query return multiple records. I did a storedproc and then I was able to put the TRUNC in the where condition. so now I a able to use the index.

I am using WCF. I was trying to get the data to DataReader from the procedure but since DataReader is not serializable I can't use. I know I can use DataSet. but its heavy.

What is your suggestion on this. which is the best approach to get the results of a stored proc and transfer data in WCF.

thanks Sam

Check this...

Excellant! Thank you for all your help.

No problem, we're always happy to help.

Matt