1

I have a table with a lot of columns and a few million rows.

One colum has column type "DATE":

DATE_ID
2022-10-01
2022-10-02
2022-10-03
...

Exasol does manage indexes itself so you can't set one.

When I need the month of the date is it better to use

EXTRACT(MONTH FROM DATE_ID)

or to use

MONTH(DATE_ID)

in terms of performance?

https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/extract.htm https://docs.exasol.com/db/latest/sql_references/functions/alphabeticallistfunctions/month.htm

Vega
  • 2,661
  • 5
  • 24
  • 49
  • I wouldn't be surprised if after statement compilation there is no difference. – Mark Rotteveel Nov 29 '22 at 14:50
  • 1
    @MarkRotteveel Docu for MONTH(): "This function can also be applied on strings, in contrast to function EXTRACT." -> so I thought that maybe EXTRACT() is faster for DATE columns and MONTH() is faster for VARCHAR columns? – Vega Nov 29 '22 at 15:09

1 Answers1

1

Indexes in Exasol

Exasol does set manage indexes itself, you can't set one.

You can set indexes manually with the ENFORCE statement, but this is not recommended. See Manual Index Creation.

However, an index on e.g. MONTH(DATE_ID) is an expression index. Such an index might be created during a join, but it will never be stored persistently. This means it will be recreated every time the query runs. If performance is a problem, it might be better to create a separate column with the month. If you use this column during a join, Exasol will create a persistent index on this column that can be reused. See Indexes for more information.

MONTH vs. EXTRACT

MONTH does add an implicit cast to TIMESTAMP, so there might be a slight performance advantage for EXTRACT which doesn't add a cast. In most queries, this won't be a significant difference.

sirain
  • 918
  • 10
  • 19