0

I have a year_qtr column as a varchar '201503', '201701' etc. That can be a number as well (instead of Varchar).

Question is - if there is already index on this column - how much benefits I could expect once I change this 6 letter Varchar to a 'Number' column when doing joins or search with Where clause on this column etc.

Thanks!

Joe
  • 11,983
  • 31
  • 109
  • 183
  • 2
    Why are you not using a `DATE` data type? The performance benefits are going to be minor regardless of the data type you use but you can then use `ADD_MONTHS( value, 3 )` (and other date manipulation functions) to get the next quarter without going through complicated string / number manipulation. – MT0 Oct 31 '17 at 20:20
  • 3
    An index on a `varchar` or `number` column that stores dates is very different from an index on a `date` column. The reason has to do with statistics: how many strings (and how many numbers) are between '201512' and '201601'? We know as **dates** they are consecutive, but as strings or numbers Oracle may think they are far apart and decide not to use the index. –  Oct 31 '17 at 20:27
  • @MT0 - That is another potential. This particular table I just got to review so was not involved in early design plans.. So performance benefits are minimal when using 'Number' as oppose to 'Varchar'? (assuming the column is indexed) – Joe Oct 31 '17 at 20:27
  • @mathguy - am I understanding correctly that 'date' column would be a win-win in terms of performance (since if indexed - oracle may utilize it better) and in terms of functionality (can add new quarter etc.)? – Joe Oct 31 '17 at 20:30
  • 1
    Correct! Both are among the many very strong reasons in favor of storing dates in `date` data type. –  Oct 31 '17 at 20:40
  • 1
    Do not store dates as anything other than `DATE` or `TIMESTAMP`. A `DATE` will take [7 bytes to store](https://stackoverflow.com/questions/13568193/how-are-dates-stored-in-oracle) a `VARCHAR2` will take 1-byte per ASCII character plus bytes for length so you are not saving anything on storage. The difference in time will be negligible between data types except when you want to do date arithmetic ... then trying to something like add 3 months to a string (or number) and wrap the months modulo 12 will be time consuming and you will probably end up converting to a date and back ... just don't. – MT0 Oct 31 '17 at 20:50
  • Thanks! I was just reading here (https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2048810500346778955) and if I understand correctly - there will be no performance improvments if I compare String to String (as oppose to Number to a Number). Thoughts? – Joe Oct 31 '17 at 21:09
  • How to create a 'date' column based on these 6 characters in example above? ( '201503', '201701' etc.) – Joe Nov 01 '17 at 01:33

0 Answers0