Optimize multiple subselects with WITH clause in Oracle

Question

I have a query like:

select 
    qsn.code, 
    (select prs.display_name from prs where prs.id = qsn.fk_prs) display_name,
    (select prs.address from prs where prs.id = qsn.fk_prs) address,
    (select prs.tel from prs where prs.id = qsn.fk_prs) tel
from 
    qsn
where 
    qsn.register_date between :x1 and :x2

When I look at the execution plan of the query, it queries prs table 3 times (each time using INDEX UNIQUE SCAN).

I wonder if I can query the prs table once using WITH clause? How can I write the query that way.

I shall mention that because each of the tables have millions of record, joining them makes the query so slow.

Without seeing your execution plans only a wild guess is possible. Please post [execution plans](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) for both queries (with subqueries and with the join). One explanation of your observation is that you switch to hash join (with possible FTS on both tables), the other explanation is that you profit from scalar subquery cashing (in case there is relatively low number of the `qsn.fk_prs`). — Marmite Bomber, Sep 06 '16 at 16:27

score 1 · Answer 1 · edited Sep 06 '16 at 15:39

1

Use a join:

select qsn.code, prs.display_name, prs.address, prs.tel
from qsn
left join prs on prs.id = qsn.fk_prs  
where qsn.register_date between :x1 and :x2

edited Sep 06 '16 at 15:39

XING

9,608
4
22
38

answered Sep 06 '16 at 15:19

Codo

75,595
17
168
206

As I mentioned when I join them the query execution time becomes worse! – Amir Pashazadeh Sep 06 '16 at 15:20
@AmirPashazadeh - do you have an index on `qsn.fk_prs`, not just on `prs.id`? If you don't, add one and see if it helps. – Sep 06 '16 at 15:27
Yes It has the index, but no index on combination of `id`, `display_name`, `address`, and `tel`. I always use ANSI JOINS but the query cost (using explain plan increases from 41000 to 64000) – Amir Pashazadeh Sep 06 '16 at 15:28
@AmirPashazadeh: If you have an index on prs.id, it shouldn't be slower than your original query. Maybe you need to add the *ordered* hint to force it to start with *qsn*: `select /*+ORDERED*/ sqn.code ...` – Codo Sep 06 '16 at 15:34
@AmirPashazadeh: Joins do not make a query slow in general. If you have proper indices and the result set isn't huge, joins are fast in Oracle. Joining tables isn't a bad thing as many people seem to believe. – Codo Sep 06 '16 at 15:36
@Codo Well I executed both queries for a 3 month range on our large database, the first one using 3 subselects took 98 seconds and the second one using joins took 148 seconds. I know the first one is not ideal either, but it performs better. – Amir Pashazadeh Sep 06 '16 at 15:58
@AmirPashazadeh Since `prs.id` is unique you should try to filter `qsn` by `register_date`. Do you have an index on `register_date`? Partition Maybe? It will help to know the explain plan. I think this method could get faster than the one you are using. – vercelli Sep 06 '16 at 16:01

XING · Accepted Answer · 2016-09-06T15:38:29.643

1

using with clause your query goes like this:

with abc as (select id,
                    display_name ,
                    address ,
                    tel  
               from prs)
select 
    qsn.code, 
    abc.display_name,
    abc.address,
    abc.tel
from   qsn
inner join abc
on qsn.fk_prs = abc.id
where qsn.register_date between :x1 and :x2 ;

ps: not tested.

edited Sep 06 '16 at 15:38

answered Sep 06 '16 at 15:27

XING

9,608
4
22
38

Optimize multiple subselects with WITH clause in Oracle

2 Answers2