Efficient Storage and Query Processing of Large String in Oracle

Abstract

Variable size strings are a fundamental data type in RDBMS and used in virtually all database components and applications including XMLs, blogging, customer service comments, e-commerce product descriptions, etc. Many applications could require large strings to store XML documents, JSON documents, customer support history, blog entries, or HTML documents. Many social network applications as well as web 3.0 applications also require large string type. A naïve implementation would simply increase the size of the traditional variable strings, but this will incur performance problems due to row chaining. Using Large Object type (LOB) will enable users to store large string without row chaining, but it is difficult to manipulate LOBs and many built-in operators for strings are not applicable to LOBs. Oracle 12c provides a capability of storing large strings without the row-chaining problem while eliminating LOB?s deficiencies. In addition, users can control the data placement and storage format based on their application workload. However, reading the large string from storage for each reference to the string would be inefficient for queries that reference the strings frequently. This paper presents an efficient processing strategy for queries involving large strings, while supporting theoretically unlimited size of the strings. It illustrates how seemingly simple conceptual work involves careful design and extensive engineering work to have a scalable and efficient implementation. The solution has been implemented in Oracle 12c, and the performance results show its efficiency.