Optimization: make the most of Hint::AcceptsSingular when call `make_scalar_function` to Improve performance

### Is your feature request related to a problem or challenge?

When implementing the `ScalarUDFImpl` trait, if the `invoke` calls `make_scalar_function`(eg. [overlay](https://github.com/apache/arrow-datafusion/blob/main/datafusion/functions/src/string/overlay.rs#L71-L78)) we should use `Hint::AcceptsSingular` as much as possible according to the specific situation of the function. Theoretically, overhead can be reduced by avoiding the continuous creation of Arrays.

### Describe the solution you'd like

1. Using Hint::AcceptsSingular in `hints` of `make_scalar_function`.
2. In `inner` of `make_scalar_function`, `ArrayIter` of arg is processed in combination with hints info. If the hint corresponding to arg is `Hint::AcceptsSingular`, then `ArrayIter` adapts to the arg length of the non `Hint::AcceptsSingular` to facilitate `zip` operation.

### Describe alternatives you've considered

_No response_

### Additional context

_No response_

Start with the make_scalar_function entry point and compare its current behavior with the overlay example in datafusion/functions/src/string/overlay.rs. Trace Hint::AcceptsSingular and ArrayIter handling, then verify that singular arguments avoid unnecessary array creation while zip processing remains correct.

Written by the indexing model from the issue text.