Hide Forgot
Help Desk Ticket Reference: https://c.na7.visual.force.com/apex/Case_View?id=500A0000006nl1V project_key: SOA The Table is BIG ! - The Oracle table that we're importing is partitioned by date with 5 million rows per day and 2 years worth of data. The problem is that it takes longer than 2 hours because it issues an ANALYSE table command. Would it be possible to create an option (perhaps a checkbox) that would allow the meta data to be created quiclky and defer the ANALYSE to runtime? create an option (perhaps a checkbox) on Import from JDBC that would allow the metadata to be created quickly and defer the ANALYSE to runtime. Also, it was indicated that the ANALYSE call is an old Oracle command, that there is a newer command. Also, consider running the stats only only the requested partition.
Link: Added: This issue Cloned from TEIIDDES-921
Security: Added: Public
There are a could of things that are confusing here. We do not explicitly issue an ANALYSE call either during import or during the gathering of cost stats. Did the customer use a trace facility or other mechanism to see that ANALYSE was being issued? Is it possible that they mean the update costing took too long - see also TEIIDDES-932. We are defaulting to issuing aggregate queries against each number/date column to determine the min/max value. As to whether stats can be deferred, Teiid is adding an ability to inject costing metadata at runtime, but it will be based upon the user providing the stats not us.
The person was directly monitoring Oracle and said he saw ANALYSE being executed when importing of the metadata was performed in Designer.
As to taking too long, that was the real issue, it was taking over 2 hours to import on a partitioned schema.
As for deferring costing, it was suggested that Designer enabled the costing be done a table at a time, when the user selected the table for costing.
To clarify was ANALYSE seen on import or during the update of cost statistics? If it's specifically import, that's very confusing. We'd definitely have to reproduce here to understand what in the world would trigger that behavior. It also would mean that there is a fundamental disconnect with this issue. That is - they are assuming there is some purpose in running ANALYSE during import, however there is no reason for the driver to do that.
Yes, As stated in TEIIDDES-921.
I'm sorry I don't see any additional specifics in TEIIDDES-921. So yes it is specifically import? Not to seem incredulous, but there is almost no reason for that to occur. Someone will have to reproduce. If that's the case, then this is just a driver/import option issue. All of the other stuff about deferring or per table analyze is not meaningful.
On import, which was taking over 2 hours, the user was monitoring oracle and specifically saw ANALYSE being called. Based on what he saw, and because the schema was partitioned, it was taking too long.
Here's the resolution to the issue. When importing using the JDBC Importer and selecting indexes, the "Approximations Allowed" should be checked. This says to use the current stats on the indexes, otherwise, oracle will issue Analyse when getIndexes is called to update the stats. There is no code changes required support this fix.
The resolution to this issue does not require any code fix. If the client has further issues, this jira can be reopened if needed or a new jira can be opened.
11/30/2011 - Will not fix - no code fix.