In my understanding, an 'avoid note' in the context of a scale is practically a particular note we should not play , for example during an improvisation, because of the clash (minor 9th interval) it creates with one or more notes of the underlying chord. For example if I'm playing a G7b9 with my left hand, namely F Ab B D, in this case an Avoid note would be the C because of a minor 9th distance from the 3rd. But should I be aware just of minor 9ths intervals or there can be other possible situations?
"Avoid notes" are an idea created by Berklee College of Music's harmony department.
They say that chord tones are the tonic, the third, the fifth, the seventh, and tensions that are a major 9th above a chord tone.
This is not entirely true, since tensions like the b9 and 11 exist on the right scales.
The avoid notes that you're talking about are called melodic avoid notes. If you're playing a G7b9, you'd use a G mixolydian b9 (optional #9) to improvise over it. C would be considered an 11 in a harmonic context, and isn't an "available" chord tone.
If you're looking to have a sound that fits well with the chord, don't sit on the C.