1

Topic: A little conditions join for the ambiguous data

Prompt, how it is better such to implement?
It is necessary to connect among themselves two data sets for which conditions on binding can depend on several factors.
There is a Client. The Client has child Services of different Types. For the new data restriction is observed that at the Client can be no more than one Service of each Type. But for old Clients of such restriction was not and some Clients can have some Services of one type. Services have a dial-up of the various Attributes which composition depends on service Type.
There are two Types of services which are logically coordinated with each other at level of business processes. At level of a database of service it is possible to connect among themselves (for this purpose in a DB there is a special table of communications), however in an information system for operators it is not provided GUI for this operation, communication is installed by means of administrative tools of an information system.
It is necessary to synchronize by means of an indirect script them among themselves. At service synchronization are compared among themselves on following algorithm (rules are applied within the limits of one Client, as their listing):
0. If services are present at the table of communications existing communication is always used.
1. If there is exactly one Service with type Tip1 and is equal one service with type Tip2 they communicate (and this communication is added in the table of communications).
2. Service with type Tip1 has Atribut1. Service with type Tip2 has Atribut2. If at services of different types values of Atribut1 and Atribut2 they communicate coincide.
3. Service with type Tip1 has an attribute Login. Service with type Tip2 has an attribute of DataDobavlenija. Untied services are arranged on the above-stated attributes and communicate on ordinal value.
4. The remained Services are fixed in log, in them the manager of an information system manually is engaged.
There is a sampling for Services of type Tip1 about following a type:

select rank () over (partition by S.CUSTOMER_ID order by S.LOGIN) as "IDX" - ordinal value of Service for the Client
, count (*) over (partition by S.CUSTOMER_ID) as "CNT" - an amount of Services at the Client
, S.CUSTOMER_ID - The identifier of the Client
, S.SERVICE_ID - The Service identifier
, S.LOGIN - Attribute Login
, RR.VALUE - Attribute of Atribut1
...
from SERVICES S
join RADREPLY RR on (RR.SERVICE_ID = S.SERVICE_ID and RR.ATTRIBUTE = ' Framed-IP-Address' and RR.DATE_BEG <= sysdate and (RR.DATE_END is null or RR.DATE_END> sysdate))
where...

There is a sampling for Services of type Tip2 about following a type:

select rank () over (partition by S.CUSTOMER_ID order by S.CREATE_DATE) as "IDX" - ordinal value of Service for the Client
, count (*) over (partition by S.CUSTOMER_ID) as "CNT" - an amount of Services at the Client
, S.CUSTOMER_ID - The identifier of the Client
, S.SERVICE_ID - The Service identifier
, S.LOGIN - Attribute of Atribut2
, S.CREATE_DATE - Attribute of DataDobavlenija
, L.SERVICE_ID as "PARENT_ID" - existing communication with services of Tip1
...
from BM_TARIFF T
join SERVICES S on (S.TARIFF_ID = T.TARIFF_ID and S.STATUS! =-20)
left join SERVICES_LINK L on (L.SERVICE_ID_1 = S.SERVICE_ID and L.SERVICE_ID_2 is null and L.MNEMONIC = ' parent ')
where...

Separately these requests return the correct results.
How to me them to connect among themselves?
Somehow so?

with
SET1 as (...) - services of Tip1
, SET2 as (...) - services of Tip2
select *
from SET1
left join SET2
on (
SET2.PARENT_ID = SET1.SERVICE_ID
or (SET1.CNT = 1 and SET2.CNT = 1 and SET1.CUSTOMER_ID = SET2.CUSTOMER_ID)
or (SET1.CUSTOMER_ID = SET2.CUSTOMER_ID and SET1.VALUE = SET2.LOGIN and SET1.CUSTOMER_ID! = SET2.CUSTOMER_ID)
or (SET1.CUSTOMER_ID = SET2.CUSTOMER_ID and SET1.IDX = SET2.IDX and SET1.VALUE! = SET2.LOGIN and SET1.CUSTOMER_ID! = SET2.CUSTOMER_ID)
)

Or there can be a condition connection to describe through CASE (then it will not be necessary to repeat the inverted previous conditions)?

2

Re: A little conditions join for the ambiguous data

In final request altered left on full.
[spoiler]

with
SL as
(
select rank () over (partition by S.CUSTOMER_ID order by S.LOGIN) as "IDX"
, count (*) over (partition by S.CUSTOMER_ID) as "CNT"
, S.CUSTOMER_ID as "CUSTOMER_ID"
, S.SERVICE_ID as "SERVICE_ID"
, S.LOGIN as "LOGIN"
, S.STATUS
, RR.VALUE as "IP"
from SERVICES S
left join RADREPLY RR on (RR.SERVICE_ID = S.SERVICE_ID and RR.ATTRIBUTE = ' Framed-IP-Address' and RR.DATE_BEG <= sysdate and (RR.DATE_END is null or RR.DATE_END> sysdate))
where S.TYPE_ID = 14 and S.STATUS! =-20
);
IP as
(
select rank () over (partition by S.CUSTOMER_ID order by S.CREATE_DATE) as "IDX"
, count (*) over (partition by S.CUSTOMER_ID) as "CNT"
, S.CUSTOMER_ID as "CUSTOMER_ID"
, S.SERVICE_ID as "SERVICE_ID"
, S.LOGIN as "IP"
, S.STATUS
, L.SERVICE_ID as "PARENT"
from BM_TARIFF T
join SERVICES S on (S.TARIFF_ID = T.TARIFF_ID and S.STATUS! =-20)
left join SERVICES_LINK L on (L.SERVICE_ID_1 = S.SERVICE_ID and L.SERVICE_ID_2 is null and L.MNEMONIC = ' parent ')
where T.TYPE_ID = 206
and T.DATE_WORK_BEG <= sysdate and (T.DATE_WORK_END is null or T.DATE_WORK_END> sysdate)
and T.NAME like ' % an IP address of %'
)
select SL.IDX
, SL.CUSTOMER_ID
, SL.SERVICE_ID
, SL.LOGIN
, SL.STATUS
, SL.IP
, IP.SERVICE_ID as IP_SERVICE
, IP.IP as IP_VALUE
, IP.STATUS as IP_STATUS
, IP.PARENT as IP_PARENT
from SL full join IP
on
(
(SL. "SERVICE_ID" = IP. "PARENT")
or (SL. "CUSTOMER_ID" = IP. "CUSTOMER_ID" and SL. "CNT" = 1 and IP. "CNT" = 1)
or (SL. "CUSTOMER_ID" = IP. "CUSTOMER_ID" and SL. "IP" = IP. "IP")
or (SL. "CUSTOMER_ID" = IP. "CUSTOMER_ID" and SL. "IP"! = IP. "IP" and SL. "IDX" = IP. "IDX")
)
where (SL.IP is not null or IP.IP is not null)
--and SL.SERVICE_ID is not null
order by SL.CUSTOMER_ID, SL.LOGIN

[/spoiler]
Works quickly, results returns the correct.
But I am not assured that the data in that order which is required to me will communicate (is most priority existing communication, then remaining criteria).
And still the question, with pure curiosity - for some reason full join is fulfilled much faster, than left join (250 msec against several minutes). And this difference is saved even if I  restriction in WHERE (which actually transforms full join in left join).
Plans certainly differ, but in my opinion the plan with left join should be faster.
[spoiler]
left join + explain

Plan
SELECT STATEMENT ALL_ROWSCost: 861 821 Bytes: 154 873 675 Cardinality: 315 425
20 FILTER
19 NESTED LOOPS OUTER Cost: 861 821 Bytes: 154 873 675 Cardinality: 315 425
7 VIEW BILLING. Cost: 3 297 Bytes: 4 075 291 Cardinality: 12 617
6 WINDOW SORT Cost: 3 297 Bytes: 1 337 402 Cardinality: 12 617
5 NESTED LOOPS OUTER Cost: 2 990 Bytes: 1 337 402 Cardinality: 12 617
1 TABLE ACCESS FULL TABLE BILLING.SERVICES Cost: 222 Bytes: 996 743 Cardinality: 12 617
4 VIEW SYS. Cost: 0 Bytes: 27 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID TABLE BILLING.RADREPLY Cost: 4 Bytes: 63 Cardinality: 1
2 INDEX RANGE SCAN INDEX BILLING.RADREPLY_SERVICE_IDX Cost: 2 Cardinality: 8
18 VIEW SYS. Cost: 68 Bytes: 4 200 Cardinality: 25
17 VIEW BILLING. Cost: 68 Bytes: 5 175 Cardinality: 25
16 WINDOW SORT Cost: 68 Bytes: 3 275 Cardinality: 25
15 NESTED LOOPS OUTER Cost: 67 Bytes: 3 275 Cardinality: 25
12 NESTED LOOPS Cost: 67 Bytes: 2 850 Cardinality: 25
9 TABLE ACCESS BY INDEX ROWID TABLE BILLING.BM_TARIFF Cost: 6 Bytes: 67 Cardinality: 1
8 INDEX RANGE SCAN INDEX BILLING.TARIFF_TYPE_IDX Cost: 1 Cardinality: 40
11 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES Cost: 61 Bytes: 1 786 Cardinality: 38
10 INDEX RANGE SCAN INDEX BILLING.SERV_TARIFF_IDX Cost: 1 Cardinality: 234
14 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES_LINK Cost: 0 Bytes: 17 Cardinality: 1
13 INDEX RANGE SCAN INDEX BILLING.SRV_LINK_MNEMONIC_IDX Cost: 0 Cardinality: 1

full join + explain

Plan
SELECT STATEMENT ALL_ROWSCost: 4 608 138 102 Bytes: 42 809 117 152 424 Cardinality: 57 155 029 576
36 TEMP TABLE TRANSFORMATION
8 LOAD AS SELECT
7 WINDOW SORT Cost: 3 924 Bytes: 4 352 865 Cardinality: 12 617
6 VIEW SYS. Cost: 2 990 Bytes: 4 352 865 Cardinality: 12 617
5 NESTED LOOPS OUTER Cost: 2 990 Bytes: 1 286 934 Cardinality: 12 617
1 TABLE ACCESS FULL TABLE BILLING.SERVICES Cost: 222 Bytes: 492 063 Cardinality: 12 617
4 VIEW SYS. Cost: 0 Bytes: 63 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID TABLE BILLING.RADREPLY Cost: 4 Bytes: 63 Cardinality: 1
2 INDEX RANGE SCAN INDEX BILLING.RADREPLY_SERVICE_IDX Cost: 2 Cardinality: 8
20 LOAD AS SELECT
19 WINDOW SORT Cost: 68 Bytes: 6 100 Cardinality: 25
18 VIEW SYS. Cost: 67 Bytes: 6 100 Cardinality: 25
17 NESTED LOOPS OUTER Cost: 67 Bytes: 5 650 Cardinality: 25
14 VIEW SYS. Cost: 67 Bytes: 5 225 Cardinality: 25
13 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES Cost: 61 Bytes: 1 786 Cardinality: 38
12 NESTED LOOPS Cost: 67 Bytes: 2 850 Cardinality: 25
10 TABLE ACCESS BY INDEX ROWID TABLE BILLING.BM_TARIFF Cost: 6 Bytes: 67 Cardinality: 1
9 INDEX RANGE SCAN INDEX BILLING.TARIFF_TYPE_IDX Cost: 1 Cardinality: 40
11 INDEX RANGE SCAN INDEX BILLING.SERV_TARIFF_IDX Cost: 1 Cardinality: 234
16 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES_LINK Cost: 0 Bytes: 17 Cardinality: 1
15 INDEX RANGE SCAN INDEX BILLING.SRV_LINK_MNEMONIC_IDX Cost: 0 Cardinality: 1
35 VIEW SYS. Cost: 4 608 134 110 Bytes: 42 809 117 152 424 Cardinality: 57 155 029 576
34 UNION-ALL
27 FILTER
26 NESTED LOOPS OUTER Cost: 4 608 134 110 Bytes: 28 063 119 521 325 Cardinality: 57 155 029 575
22 VIEW BILLING. Cost: 32 534 786 Bytes: 738 442 982 109 Cardinality: 2 286 201 183
21 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B61_DA622CF8 Cost: 32 534 786 Bytes: 434 378 224 770 Cardinality: 2 286 201 183
25 VIEW SYS. Cost: 2 Bytes: 4 200 Cardinality: 25
24 VIEW BILLING. Cost: 2 Bytes: 5 175 Cardinality: 25
23 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B62_DA622CF8 Cost: 2 Bytes: 1 650 Cardinality: 25
33 FILTER
30 FILTER
29 VIEW BILLING. Cost: 2 Bytes: 5 175 Cardinality: 25
28 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B62_DA622CF8 Cost: 2 Bytes: 1 650 Cardinality: 25
32 VIEW BILLING. Cost: 32 534 786 Bytes: 413 802 414 123 Cardinality: 2 286 201 183
31 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B61_DA622CF8 Cost: 32 534 786 Bytes: 434 378 224 770 Cardinality: 2 286 201 183

[/spoiler]

3

Re: A little conditions join for the ambiguous data

And still would like to ask.
With data retrieveds I will need to do some different handlings.
Therefore I want to use not request, and the auxiliary table which will be cleared beforehand and then in which this data will be saved.
Do not advise, with what parameters and attributes this table is better for creating? I set LOGGING, CACHE and NOPARALLEL.
Or better simply to make the temporary table (global temp)?