The documentation of the Oracle connector says:
To collect statistics for a table, add the following statement to your Oracle database:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘USER_NAME’, ‘TABLE_NAME’);
Does that mean that I have to access the original Oracle database or can I execute it via the Starburst SQL-editor? I am asking this, because the syntax does not look like correct Oracle syntax to me. I would have guessed that if I am executing it in Oracle, that it would have to look like this:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘schema_name’,
tabname => ‘table_name’,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE
);
END;
/
Furthermore, i was wondering if this needs to be done for the managed statistics to work, or if I could just use collect_statistics.
I currently dont have access to a cloud-hosted Oracle database so I can’t test it myself. However, I need to be able to use Oracle statistics soon, so I would love some help.
Thanks in advance!
By default, the Oracle connector doesn’t allow you to calculate stats from a Trino client, but the Starburst Oracle connector does allow you to enable Managed statistics — Starburst Enterprise and then you can run the following command as described in the “collect_statistics” section; Oracle connector — Starburst Enterprise.
ALTER TABLE example_table EXECUTE collect_statistics;
Thanks for the quick response!
Do the managed statistics also work in Starburst Galaxy? If not, is there a another way to get the table statistics of an oracle table in Starburst Galaxy?
Based on the catalog configuration screen and the documentation at Starburst | Oracle catalogs which doesn’t show a place to enable it, I think this is not an option that has made it from SEP to Galaxy, BUT since I don’t know for 100% I’ll ping some folks internally to verify. If I don’t have a follow-up reply you can safely assume that’s the case now.
For a roadmap timeline, even if I can find out I’m not so sure I can put the answer here. I hate to do it to you, but likely best to bring this up via your Starburst account team and/or via a Starburst support case as a feature request to get a more definitive answer.
And, of course, you could always try it to see what happens when you try to collect the stats via an Oracle catalog on Galaxy. Based on what I know right now, I’d expect that it would error out saying it managed stats weren’t enabled (explicitly or maybe just a general error).
Again, no updates news means my hunch was correct about managed statistics for Oracle NOT being available (today) for Oracle catalogs.
1 Like
Did verify from the connectors team that managed stats are NOT in Galaxy at the moment. I have no formal expectation to share for timing of when it will be available.
1 Like