Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Phoenix Index on a EVENT Column with ORDER BY DESC

Hi, I have a requirement in which I want the event column to be indexed. However I wanted to know if it is possible to ORDER the event column in Descending order something like below:

CREATE INDEX my_index ON my_table (EVENT ORDER BY DESC) INCLUDE (v2);

My Base table has EVENT column but it is not ordered, I am thinking if indexing EVENT column in Descending order will have me get away with the ORDERBY clause and reduce query time.

Request for comments.
3 REPLIES 3

Cloudera Employee

Hi, we can create an index in descending order like this:

CREATE INDEX my_index ON my_table (EVENT DESC) INCLUDE (v2);

Thank you @Toshihiro Suzuki. I created the index something like

CREATE INDEX my_index ON my_table (col1, col2, col3 , EVENT DESC) INCLUDE (col4 , col5);

However the EVENT column is not sorted in descending order. Is there anything I am missing? Have I created the Index correctly?

One thing I wanted to highlight was my EVENT column datatype is VARCHAR....is that making EVENT column not to sort?

A VARCHAR column will be sorted differently than a numeric column, yes. However, the event column would need to be the leading (or only) column in your create index statement.

Otherwise, you would need to scan all records, buffering them, to construct the descending order. Try:

CREATE INDEX my_index ON my_table (EVENT DESC, col1, col2, ...) include (...)