As pointed out by ypercube, Oracle (since v9) has supported using partial index scans in some circumstances where the relevant predicates do not include the first column in the index.
I'll leave the rest of the answer as it is relevant to anyone passing by who is using another DB. Further notes: from reading around I get the impression that the query planner will be eager to multiple seeks on another index where possible even if this is an option, so you may need to use an index hint if you explicitly want an index skip scan to happen. There is talk of such an operator being introduced to postgres in the not too distant future (see https://commitfest.postgresql.org/19/1741/ amongst other references).
Query planners can generally only use a composite index if they need to reference the first column in the index, so an index on columns
a, b, c could be useful for queries needing to filter/sort/both by
c. It may also be used when referencing other columns too, of course, for example if filtering by
d but there is no composite index to help that more directly.
They generally can't use a composite index to filter/group/sort by columns later in the list unless the earlier ones are also needed. It is conceivable that they could use columns later in the index, but maintaining the statistics needed to make detecting when this is a beneficial operation would be expensive in the general case, making it an impractical optimisation to implement, so I doubt any query engine out there does this sort of thing.
tl;dr: if you have queries that filter/sort/group by
serialnumber but not
modelnumber, your smaller index is likely to be needed despite
serialnumber being part of the composite index.