Optimal Oracle Sql Query To Complete Group-by On Multiple Columns In Single Table Containing ~ 7,000,000 Records

I am a SQL Novice in need of some advice. What is the most efficient (fastest running query) way to do the following- Select all columns from a table after- -Performing a 'Group By

Solution 1:

Although you phrase this as a group by query, there is another approach using row_number(). This enumerates each row in the group, based on the "order by" clause. In the following query, it enumerates each group based on external_reference and top_line_id, ordered by support_id:

select*from (Select t.*,
             row_number() over (partitionby external_reference, top_line_id
                                orderby support_id) as seqnum
      from STAGE.SFS_GH_R3_IB_ENTLMNT_CONTACTS t
     )
where seqnum =1

Solution 2:

This should work(can't test it)

SELECT*FROM
  stage.sfs_gh_r3_ib_entlmnt_contacts
WHERE
  (support_id, external_reference, top_line_id) IN
    (
      SELECTmax(support_id), 
        external_reference, 
        top_line_id
      FROM
        stage.sfs_gh_r3_ib_entlmnt_contacts
      WHERE
        external_reference ISNOTNULLAND
        top_line_id ISNOTNULLGROUPBY
        top_line_id, external_reference
    )