True or False. Crystal drops index when using multiple data sources

[JohnWantsToKnow]

All crystal version from 8.5
Someone has mentioned to me that Crystal will not use indexes of tables when more then one data source is being used. As I'm about to start building a new report utilising two Oracle schemas I'm hoping that someone can come back with a quick "yes or no" to this.
Thanks in appreciation.

 

[synapsevampire]

Do you mean the indexes that Crystal creates, or your database indexes?

Databases will use indexes depending upon the query passed to them, Crystal doesn't control how the Oracle optimizer works.

They might have been referring to Access databases.

Now, if you link an Oracle schema to another Oracle schema, it may not use the proper indexes across the schemas, but that's a different issue, and you might get around it using a hint.

I would pose the question in an ORacle foruma, and keep in mind that creating a SP or a View on the Oracle server is a better idea than linking within Crystal.

-k

 

[Turkbear]

Hi,
If the tables are indexed in Oracle and the Optimizer has current stats and both schemas are in the same instance, then Oracle will determine if Indexes will be used..Assuming all the query is passed to Oracle for processing..( A tip: Use the Show Sql tab to see what is actually going to the database - copy it to a SqlPLus session and run an Explain Plan on it to see how it will be handled).





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[smcnulty2000]

Short answer: build your report.

Synapse and Turkbear have it dead on.

If there is any problem with hitting the indexes it would be on the Oracle side, not based on the scenario you mentioned. I don't recall crossing schemas inside of Oracle being a performance issue, anyway, unless you don't have permission to access the schema with the index. Again, that would be a Oracle side issue.

This is not to say that you couldn't have Crystal under some circumstances cause the optimizer to throw up its hands and just go ahead with a full table scan. If the query didn't pull all of the fields in the primary key, for example.

If you follow Synapse's advice and build a stored procedure on the server then you can take advantage of every aspect of the database's ability to increase it's own performance.

Turkbear's idea to check the query for the execution plan is good too because then you can tune. Your dba can run a trace on your session to see things from the other end too.

There is also a possibility that bypassing the indexes would be good for your report, if the data pull is greater than 5% of records of the table for example. This is the sort of thing you would want to test both ways, if you could.

But by all means build your report. Presumably you could build a partial report that pulls your data as you expect to need it but without all the bells and whistles that the final report will have. This should tell you what you are in for.

Good luck.

scottm.