Why using a column of a virtual table makes a SQL in Oracle slow

Question

Why using a column of a virtual table makes a SQL in Oracle slow

Navigation

#1 by (2 votes)

3

Good.

Not wanting to make the subject too "gassy" but just wanted things to investigate in the environment.

I have a SQL that uses a virtual table, for a series of reasons that does not come much to the case.

Something like:

select *
from
(select a,b,c,x,virtual.d
 from tabela1,
(select a,b,c,d
 from tabela2
 where ....) virtual
where virtual.a = tabela1.a
and virtual.b = tabela1.b
virtual.c = tabela1.c)

SQL performs fast, but I need to do a test of type

 select *
    from
    (select a,b,c,virtual.d
     from tabela1,
    (select a,b,c,d
     from tabela2
     where ....) virtual
    where virtual.a = tabela1.a
    and virtual.b = tabela1.b
    virtual.c = tabela1.c)
where ((c <> d) or (a=1))

When you do this SQL is extremely slow.

What could be investigated.

I do not know if details like type description, indexes etc would help, the basic question is:

Why use a column of a virtual table makes a SQL in Oracle slow?

Thank you.

sql oracle oracle11g pl-sql

asked by anonymous 18.01.2017 / 01:09

1 answer

Is there any way to use Laravel Eloquent in the Silex microframework? How to implement a system of undo and redo in javascript?

score 2 · Answer 1

In case of interest to another, I resolved with a HINT

SELECT /*+USE_CONCAT*/ * from
    (select a,b,c,virtual.d
     from tabela1,
    (select a,b,c,d
     from tabela2
     where ....) virtual
    where virtual.a = tabela1.a
    and virtual.b = tabela1.b
    virtual.c = tabela1.c)
where ((c <> d) or (a=1))

Font