[Subtask]: Scan with ordering, and interesting order in general

### Parent Issue

#20650

### Detail of Subtask

When scan a table, if the scan order is the same as (or a subset prefix of) the clustering or primary key, we should be able to scan the table from many S3 objects and MERGE them, producing a stream of ordered records. It should be very efficient and use very little of memory.

The optimizer MUST know such an interesting order from scan -- v.s the cost using a real sort (could be external sort).

### Describe implementation you've considered

_No response_

### Additional information

_No response_