Optimizing sql select statement (oracle)

Question

I'm currently trying to optimize a sql select statement. I would like to know if there's a simpler way to implement this sql statement and improve its performance.

I would also like to know if I can improve the performance of this statement using indexing, partitioning, clustering, tuning data buffer cache, using in-memory column store, etc.

select
        ps_partkey,                                                        
        sum(ps_supplycost * ps_availqty) value                                                                                
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'FRANCE'  
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.0005
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = 'FRANCE'
                )
order by
        value desc;

Answer 1

Can you use different query as follows and check for the performance?

Select distinct ps_partkey, value
  from
(select
        ps_partkey,                                                        
        sum(ps_supplycost * ps_availqty)  over (partition by ps_partkey) value,
        sum(ps_supplycost * ps_availqty)  over () as totalvalue 
  from partsupp
       join supplier on ps_suppkey = s_suppkey
       join nation on s_nationkey = n_nationkey
 where n_name = 'FRANCE')
Where value > totalvalue * 0.0005

Note: It is recommended to use standard ANSI joins