Question

Customer Transaction Table – CUSTOMER_TXNS

Column Name	Description	Type
Account_id	Account identifier	Integer
Txn_timestamp	Time of transaction (UTC)	Timestamp
Product_id	The id of the product purchased	Integer
Txn_Amt	The revenue amount of the transaction	Float
Txn_Qty	The number of items purchased	Integer

NOTE: Customer Transaction table has multiple records per account_id.

Customer Master Table – CUSTOMER_MSTR

Column Name	Description	Type
Account_id	Account identifier	Integer
Country	Country Code	Character(3)
Address	Address of the customer	Character(64)
Registerd_Dt	Date the account id was first used	Date
Tier	Account Tier	Integer

NOTE: Customer Master table has one record per account_id.

A database administrator wants to create an index on the address column in the CUSTOMER_MSTR table. Their statement is below:

CREATE INDEX ADDRESS ON CUSTOMER_MSTR (Address(64));

You think the index can be a lot shorter and therefore more efficient. Write an example SQL query that you could run to show that an index based on Address with a length of 32 characters is just as good as one with a length of 64 characters.

Answer 1

One of the possible queries would be:

SELECT * FROM CUSTOMER_MSTR WHERE Address LIKE '12%';