Oracle等RDBMS如何为字符串字段创建索引？存储管理及结构疑问

Hey there! Let's tackle your two questions about string indexes in Oracle and other relational databases—this is a solid set of questions, so let's break it down clearly:

Creating indexes for string columns is similar to other data types, but there are a few specialized options depending on your use case:

• Basic B-Tree Index (Default)
  For most standard scenarios (exact matches, range queries like NAME LIKE 'A%'), a regular B-tree index works perfectly. The syntax is simple:

  -- Oracle example
  CREATE INDEX idx_table_name ON your_table(NAME);
  
  -- Works for VARCHAR2, CHAR, and other string types
  

  This is the go-to for most RDBMS (PostgreSQL, SQL Server, etc.) too.

• Function-Based Indexes (For Transformations)
  If you frequently run queries that modify the string (like case-insensitive searches), a regular index won't be used. Instead, create a function-based index to match your query pattern:

  -- Case-insensitive search support in Oracle
  CREATE INDEX idx_table_lower_name ON your_table(LOWER(NAME));
  

  Now queries like WHERE LOWER(NAME) = 'jane' will hit this index. Other databases support similar expression-based indexes.

• Full-Text Indexes (For Large Text)
  For columns with long text (CLOBs, large VARCHAR2s) where you need keyword or phrase searches, use full-text indexing. In Oracle, this leverages Oracle Text:

  CREATE INDEX idx_table_name_fulltext ON your_table(NAME) INDEXTYPE IS CTXSYS.CONTEXT;
  

  This lets you run advanced searches like CONTAINS(NAME, 'smith OR john').

Let's get into the details of how these indexes work under the hood:

• Index Structure: B-Tree is the Standard
  Yes! Oracle (and nearly all mainstream RDBMS) uses B-Tree indexes as the default for string columns. B-Trees are ideal here because they enable fast exact matches, range scans, and ordered traversals—all critical for common string operations like sorting names or filtering by prefixes.

• Sorting: Lexicographic (Dictionary) Order
  Index entries are absolutely stored in lexicographic order, but this depends on your database's NLS (National Language Support) settings. By default:

  • Oracle uses the database's character set collation to determine sort order (e.g., BINARY collation is case-sensitive, so 'Apple' comes before 'apple')
  • Accented characters and special symbols follow the rules of the chosen collation
    If you need a custom sort order, you can build a function-based index using NLSSORT to specify a specific collation.

• How the Index Stores Your Data
  For your ID | NAME table with an index on NAME:

  • The B-Tree nodes store sorted NAME values as index keys
  • Each key points to the corresponding row in the table via a ROWID (Oracle's unique row identifier)
  • If multiple rows share the same NAME, all their ROWIDs are stored under that key
    You can also enable index compression (CREATE INDEX ... COMPRESS) to save space if there are many duplicate string values.

内容的提问来源于stack exchange，提问作者J.J. Beam