Considerations for Indexing
You should evaluate the following considerations when assigning data types to index
Suitability of the type family for indexing
Numeric types are best because they are compact and often tend toward uniqueness in
Character and byte types are poor because they tend not to be compact and because
character and byte data naturally tends to be non‑unique.
XML, BLOB, CLOB, Period, ARRAY, VARRAY, and JSON data type columns cannot be used
to define any type of index.
“Example 2” on page 244 and “Example 3” on page 245 under the topic “Hash-Related Functions” address this issue.
Greater space requirements for a column translate to fewer data rows per block, which
results in slower full‑table scans.
Join and predicate conversions
Any time tables are joined on a column, whether indexed or not, the columns must have
the same data type in both tables. If the types do not agree, one must be converted,
which results in a performance hit. The same is true for comparisons made in WHERE