Customer Relationship Management (CRM)
Data Farm
Inc.®
|
The Technical "Know-How"
|
Data Index - Simple, But not Child's Play
What is an Index?
In a nutshell a database index is an auxiliary data structure which allows for
faster retrieval of data stored in the database.
What types of index a data structure can you have?
• Primary index or key
• Bitmap - using bits to make a value
• Hash - integer key for value
Clustered Index:
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval.
Non-Clustered Index:
Non-Clustered Index does not alter the physical order of the table and maintains logical order of data.
Advantage of Using Index:
Indices can greatly increase overall speed of retrieval.
Being a mathematician, numbers are easier to work than strings. Algorithms can also be
developed to make sorting and searching a lot faster. Big Data and CRM analysis can use
index, bit map, hashing and numbers to reduce the size of data and speed the analysis.
Index- Search ID:
We are not here to give our secrets away, but we are presenting an example to illustrate
our use of index as a way to reduce the size of data and search for values. In the following table, we can use Java
Long Integer as an index for tracking and analysis. We use integer Mod and Div to get the
country, the business type, the product, business status (still running,
profitable, ..etc), the annual gross income and other search options.
Index example - 24 digits Java Long Integer:
|
Country - 3 digits
|
ID - 7 digits
|
Income- 6 digits
|
Others- 8 digits
|
0- 999
Country Calling Code
|
0 - 9,999,999
2 digits - business (0-99)
4 digits - products (0-9,999)
1 digit - business status (0-9)
|
0- 99,9999
Gross in $100,000
Zero is less than $100K
|
0-99,999,999
Other search options
|
Java Div and Mod Example:
1051 % 1000 = 51
(51 / 10) * 10 = 50
Bit Map:
The same principle can used on the bit level of 16 bits index.
Hashing:
As for hashing, we can use each group of digits as an index to more details for other search or values.
|
|
