1

Topic: Exists under two or more tables

FB3. Here such request:

select a.sn_mdl
from modul a
where exists (select b.id_ums
from umass b
inner join defect c on c.id_ums=b.id_ums
where b.id_mdl=a.id_mdl
)

What is necessary? It is necessary to receive all records in the table modul which have records in the table defect. The request fulfills correctly, but long. If in exists to leave one table umass, behavior exists expected (it is read on one record from umass for each record modul). If in exists two tables umass all is read. Generally tables umass and defect the big. Therefore it would be desirable such behavior - we read record from modul, we read sequentially umass and defect if record is we pass to other record modul. Whether It is possible to make something with exists? Or to look at other techniques? What?

2

Re: Exists under two or more tables

And on-krestjanski

select a.sn_mdl
inner join umass b on b.id_mdl=a.id_mdl
inner join defect c on c.id_ums=b.id_ums
from modul a

Not?

3

Re: Exists under two or more tables

Yes matter is not in exist-e, and in indexes, for sure.

4

Re: Exists under two or more tables

a teddy bear wrote:

And on-krestjanski

select a.sn_mdl
inner join umass b on b.id_mdl=a.id_mdl
inner join defect c on c.id_ums=b.id_ums
from modul a

Not?

In my opinion, you wrote all correctly, but forgot to deliver DISTINCT after SELECT:

SELECT DISTINCT a.sn_mdl
INNER JOIN umass b ON b.id_mdl=a.id_mdl
INNER JOIN defect c ON c.id_ums=b.id_ums
FROM modul a

5

Re: Exists under two or more tables

rdb_dev;
It is necessary to give such thin councils, when you own sufficient information volume about the task.
Leave DISTINCT to itself.
As sn_mdl in the table modul can be initially nonunique.

6

Re: Exists under two or more tables

WildSery, serial number in tables of units can be nonunique in case of coincidence of serial numbers from different vendors of units. Adding in sampling of the identifier of the vendor solves this problem.

7

Re: Exists under two or more tables

rdb_dev;
You continue to solve the task which condition you do not know, and you assume.

8

Re: Exists under two or more tables

WildSery to tell "", proceeding from obvious entity relationships of a DB more truly.

9

Re: Exists under two or more tables

rdb_dev;
The obvious? Thanks, .
For a long time for itself drew an output that obvious it is not so obvious to me another. Assorting the next hand-made article of a command "" developers, almost each time of it I am convinced.
Therefore scepticism - our all. I prefer to be surprised pleasantly that is this time made correctly reasonably.

10

Re: Exists under two or more tables

Old teddy bear;
On-krestjanski more interesting. Only here so, it is finite:

select a.sn_mdl
from modul a
inner join umass b on b.id_mdl=a.id_mdl
inner join defect c on c.id_ums=b.id_ums

But I now do not like the plan:

wrote:

the Plan
PLAN JOIN (C NATURAL, B INDEX (PK_UMASS), A INDEX (PK_MODUL))

Why defect on ? Though can that the table defect at present less than two others. If so, in a root incorrectly. The table defect is inclined to the strong growth. And all it to scan incorrectly.

11

Re: Exists under two or more tables

KreatorXXI;
Use magic +0
And generally, as soon as it expands, the plan itself exchanges.

12

Re: Exists under two or more tables

KreatorXXI;
As grows you will enumerate statistics and the plan changes. I do not see in it a problem

13

Re: Exists under two or more tables

KreatorXXI> And all it to scan incorrectly.
Well and the initiating plan what was?

14

Re: Exists under two or more tables

KreatorXXI;
Judging by the plan the request will not be an equivalent exists. defect incorporates not on unique key and consequently will give counterparts of records

15

Re: Exists under two or more tables

WildSery wrote:

rdb_dev;
The obvious? Thanks, .
For a long time for itself drew an output that obvious it is not so obvious to me another. Assorting the next hand-made article of a command "" developers, almost each time of it I am convinced.
Therefore scepticism - our all. I prefer to be surprised pleasantly that is this time made correctly reasonably.

As a last resort, it is possible to rely on uniqueness of the identifier of the unit. Such variant should correspond:

SELECT sn_mdl
FROM (
SELECT DISTINCT a.id_mdl, a.sn_mdl
FROM modul a
INNER JOIN umass b ON b.id_mdl=a.id_mdl
INNER JOIN defect c ON c.id_ums=b.id_ums)

16

Re: Exists under two or more tables

The people, give again.
The table umass big, defect too. What sense to scan in a forehead everything if I need to learn only presence of child records?

17

Re: Exists under two or more tables

KreatorXXI;
Show the plan which was in initial request with exists

18

Re: Exists under two or more tables

KreatorXXI so specify to request that plan that you consider as correct.

19

Re: Exists under two or more tables

KreatorXXI> What sense to scan in a forehead all
What for you start to broadcast to argue, without understanding elements?
I repeat - show the plan of initial request.

20

Re: Exists under two or more tables

The plan of the first request:

wrote:

the Plan
PLAN JOIN (B INDEX (FK_UMASS_RELATIONS_MODUL), C INDEX (DEFECT_IDX1))
PLAN (A NATURAL)

DEFECT_IDX1 Is an index on id_ums.

21

Re: Exists under two or more tables

KreatorXXI;
Try here so

SELECT a.sn_mdl
FROM modul a
JOIN umass b ON b.id_mdl=a.id_mdl
WHERE EXISTS (SELECT * FROM defect c ON c.id_ums=b.id_ums)

22

Re: Exists under two or more tables

KreatorXXI> the Plan of the first request:
In it something does not arrange? Small , big on an index.

23

Re: Exists under two or more tables

Simonov Denis;
Hardly corrected, in exists instead of on delivered where. The plan:

wrote:

the Plan
PLAN (C INDEX (DEFECT_IDX1))
PLAN JOIN (B NATURAL, A INDEX (PK_MODUL))

Can somehow not through select?

24

Re: Exists under two or more tables

By the way, what data volumes (now and expected)?

25

Re: Exists under two or more tables

KreatorXXI;
smile)) Was mistaken .
At the plan do not look if you do not understand speed in comparison with other variants arranges?