Data retrieval takes too long even when using indexed field

[shannonlp]

Hi,

Crystal XI/Oracle 9i

I have a table that I need to use in many of my reports that is huge (25 million + records). One of the fields that I use a lot I’m told is indexed which is supposed to speed up the data retrieval. Its not – running reports that query off of the indexed field take at least 30 minutes. I checked with our dba and he says that he watched the query on the oracle side and he can tell that Crystal is not using the index. Separate from getting our dba from doing some configuration work, is there anything that I can do on the front end to ensure that the index is being used?

Below is what is in the Select Expert:

{ARCHIVE_TRANS.DATE_STAMP} in DateTime (2006, 01, 01, 00, 00, 00) to DateTime (2006, 01, 31, 00, 00, 00) and
{ARCHIVE_USER_LOWER.MBR_SID} = "NYSYR" or
{ARCHIVE_USER_LOWER.ARCHIVE_USER_ID} = JREA@SYRACUSE.COM

ARCHIVE_TRANS.DATE_STAMP is the indexed field and I'm using it as a parameter.

Thanks,
Shannon

 

[satinsilhouette]

Shannon,
can you somehow use the indexed field in the select criteria to force it to send it to the database?

Thanks so much!
satinsilhouette

 

[shannonlp]

I've used it both ways, as a parameter and hard coding it in the select expert.

{ARCHIVE_TRANS.DATE_STAMP} in DateTime (2006, 01, 01, 00, 00, 00) to DateTime (2006, 01, 31, 00, 00, 00) and

or

{ARCHIVE_TRANS.DATE_STAMP} = {?Download date range}

Both approaches take the same amount of time to retrieve data - 30 + minutes.

 

[shannonlp]

Hi,

Never mind. Changing parens works. It limits my and/ors to that date range.

{ARCHIVE_TRANS.DATE_STAMP} = {?Download date range} and
({ARCHIVE_USER_LOWER.MBR_SID} = "NYSYR" or
{ARCHIVE_USER_LOWER.ARCHIVE_USER_ID} = "JREA@SYRACUSE.COM" and
{ARCHIVE_TRANS.BILL_FLAG} = "Y" and
{ARCHIVE_TRANS.DOCUMENT_CLASS} <> "CR") and
Not(isnull({ARCHIVE_TRANS.DOCUMENT_ID}))

Thanks.