Red Hat Summit Red Hat Summit지원콘솔개발자평가판 시작연락처

이 콘텐츠는 선택한 언어로 제공되지 않습니다.

3.4. Code Table Caching

3.4.1. Code Table Caching
링크 복사

Red Hat JBoss Data Virtualization provides a short cut to creating an internal materialized view table via the lookup function.
The lookup function provides a way to accelerate getting a value out of a table when a key value is provided. The function automatically caches all of the key/return pairs for the referenced table. This caching is performed on demand, but will proactively load the results to other members in a cluster. Subsequent lookups against the same table using the same key and return columns will use the cached information.
This caching solution is appropriate for integration of "reference data" with transactional or operational data. Reference data is usually static and small data sets that are used frequently. Examples are ISO country codes, state codes, and different types of financial instrument identifiers.
This caching mechanism is automatically invoked when the lookup scalar function is used. The lookup function returns a scalar value, so it may be used anywhere an expression is expected. Each time this function is called with a unique combination of referenced table, return column, and key column (the first three arguments to the function). Here is a lookup for country codes: 
  
   lookup('ISOCountryCodes', 'CountryCode', 'CountryName', 'United States')
Copy to Clipboard Toggle word wrap
Code table caching does have some limitations:
  • The use of the lookup function automatically performs caching; there is no option to use the lookup function and not perform caching.
  • No mechanism is provided to refresh code tables
  • Only a single key/return column is cached - values will not be session/user specific.
The lookup function is a shortcut to create an internal materialized view with an appropriate primary key. In many situations, it may be better to directly create the analogous materialized view rather than to use a code table. 
  
	 SELECT (SELECT CountryCode From MatISOCountryCodes WHERE CountryName = tbl.CountryName) as cc FROM tbl
Copy to Clipboard Toggle word wrap
Here MatISOCountryCodes is a view selecting from ISOCountryCodes that has been marked as materialized and has a primary key and index on CountryName. The scalar subquery will use the index to lookup the country code for each country name in tbl.
Here are some reasons why you should use a materialized view:
  • More control of the possible return columns. Code tables will create a materialized view for each key/value pair. If there are multiple return columns it would be better to have a single materialized view.
  • Proper materialized views have built-in system procedure/table support.
  • More control via the cache hint.
  • The ability to use OPTION NOCACHE.
  • There is almost no performance difference.

3.4.2. Create a Materialized View for Code Table Caching
링크 복사

Procedure 3.4. Create a Materialized View for Code Table Caching

  1. Create a view selecting the appropriate columns from the desired table. In general, this view may have an arbitrarily complicated transformation query.
  2. Designate the appropriate column(s) as the primary key. Additional indexes can be added if needed.
  3. Set the materialized property to true.
  4. Add a cache hint to the transformation query. To mimic the behavior of the implicit internal materialized view created by the lookup function, use the Hints and Options /*+ cache(pref_mem) */ to indicate that the table data pages should prefer to remain in memory.
Result

Just as with the lookup function, the materialized view table will be created on first use and reused subsequently.

맨 위로 이동
Red Hat logoGithubredditYoutubeTwitter

자세한 정보

평가판, 구매 및 판매

커뮤니티

Red Hat 문서 정보

Red Hat을 사용하는 고객은 신뢰할 수 있는 콘텐츠가 포함된 제품과 서비스를 통해 혁신하고 목표를 달성할 수 있습니다. 최신 업데이트를 확인하세요.

보다 포괄적 수용을 위한 오픈 소스 용어 교체

Red Hat은 코드, 문서, 웹 속성에서 문제가 있는 언어를 교체하기 위해 최선을 다하고 있습니다. 자세한 내용은 다음을 참조하세요.Red Hat 블로그.

Red Hat 소개

Red Hat은 기업이 핵심 데이터 센터에서 네트워크 에지에 이르기까지 플랫폼과 환경 전반에서 더 쉽게 작업할 수 있도록 강화된 솔루션을 제공합니다.

Theme

© 2025 Red Hat