The official documentation on the Data Dictionary Cache is very minimal and consists of a brief description and how to access it.

Although light on information, the Data Dictionary Cache appears to be a critical component of APEX that stores metadata about schema tables and their columns.

It appears that it…

• Provides statistics and data analysis for all workspace schema tables and caches in a workspace.

• Is used by both the Create Application Wizard and Create Page Wizard rely on the Data Dictionary Cache to create new applications and pages.

• Includes information on how facets will be rendered when creating a faceted search page. The wizards use the top five facet search score columns.

It can be refreshed by…

• The Oracle APEX nightly job ORACLE_APEX_DICTIONARY_CACHE automatically refreshes the Data Dictionary Cache if tables change due to and DDL or DML changes at 1 a.m. (now we know why)

• Users can manually update the cache by clicking "Gather Statistics and Refresh Cache" or "Refresh Cache Only" using the Data Dictionary Cache Report from Administration Services

It can be accessed by clicking Administration > Manage Service > Manage Service

and then clicking Data Dictionary Cache

Demonstration Setup

I’m going to create a sample schema of 2 tables

• STAND_UP_COMEDIANS

• JOKES

and a view

• HILARIOUS_HITS

Faceted Search

Lets attempt to create a Smart Filters page on my new JOKES table

Oh man, that’s not what I expected. No Facets, zeros everywhere…

It looks a bit pathetic to be honest (same as the jokes)

If i look in the dictionary cache two tables now appear…

This is because 2 tables have been touched. Now they appear in the Data Dictionary. The reason is because:

• Jokes

  • For the Faceted Search

• Stand_up_comedians

  • Because the Faceted Search used a List of Value to pluck the Comedian Name

When I clicked on JOKES I see more zeros. This is because stats have not been gathered.

What I should have done is clicked this little Refresh button here:

When you do press this button, it looks radically different

Now, when I look at the Data Dictionary Cache I see statistics have now been gathered

and I also see populated column values

What, so I need to click Refresh every time I create a Faceted Search?

Not necessarily. This example was on fresh new tables, that when touched enter the dictionary cache, but the Stats are not gathered until the Developer clicks Refresh in the Wizard (or clicks Gather Statistics and Refresh Cache in the Data Dictionary Cache. In addition, there is a APEX Job that gathers statistics daily, so for existing tables (or changed tables) you are recommended to click Refresh.

Furthermore, if I synchronize UI Defaults…

this only results in touching the table, not gathering stats.

AI Assistant

Once its configured, to use the AI App Assistant, you need tables before it can do anything.

So I add my tables, and without having any table in the Data Dictionary Cache, I ask for an app on all 3 tables and I get

At this point…. I still have no Data Dictionary Cache. So lets try with non-existent tables.

Great. Now, lets create an app on just one table.

After clicking the Create Application button, I now get the STAND_UP_COMEDIANS in the cache with stats.

Now lets try the non-existent footwear tables

Oh?

Oh Oh. So it appears if there is any Data Dictionary Cache existing at all, its limited only to that cache. But wait…

… right. so, if you include a table from the cache (e.g STAND_UP_COMEDIANS), it allows you to bring in other non-cached tables (even non-existent ones).

Once again, not including that table (e.g STAND_UP_COMEDIANS) means an app cannot be generated

Conclusion

• In the absence of any cached tables, the AI App Assistant appears to use the Schema Objects.

• If the cache exists, only caches tables can be used in the prompt. However other tables, regardless of if they exist or not, can be used within the prompt … but only if a cached table is mentioned.

• Once the Create Application button is clicked, the Blueprints appear to gather stats regardless of page type.

• Specifically, the Faceted Search Pages are built less effectively when using absent or stale statistic

ENJOY!

What’s the picture? Its ROGERS' ALMSHOUSES in Harrogate

The 12 original almshouses, built in 1893, were a gift of George Rogers, the Bradford textile manufacturer and Harrogate philanthropist. The present day trustees have built a further 2 almshouses and in 1975, with the aid of a grant from the Housing Corporation, the almshouses were refurbished to present day standards. The almshouses are available to persons from Harrogate or Bradford who are over 60 years of age. The clock tower is decorated with a bust of Mr Rogers and his emblem of industry — the beehive.