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;

Popeye · Accepted Answer · 2020-06-25 06:34:56Z

2

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

edited Jun 25, 2020 at 6:34

answered Jun 25, 2020 at 5:58

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Miztory Over a year ago

Partsupp, supplier and nation are the names of the tables that we're using. The code above needs to be corrected.

Miztory Over a year ago

After correcting the code there is a very slight improvement in the performance. Thanks for the help.

Miztory Over a year ago

Thanks! The code works now but is there any way to further increase the performance.

Popeye Over a year ago

If you have an index on the` ps_partkey, ps_suppkey, s_suppkey, s_nationkey, n_nationkey, and n_name` then it will be much faster.

EdStevens Over a year ago

@Tajesh . if such an index(s) are built, I'd say that it may be much faster, but it's not a certainty. If the index is not sufficiently selective, the optimizer may very well decide that a FTS is still more efficient that the extra reads required for an index access. Only testing on a target system will tell.

Collectives™ on Stack Overflow

Optimizing sql select statement (oracle)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related