Effective inheritance management

Question

I have the following two data structures.

First , a list of properties applied to object tags:

Object1 Object2 Object3 Property Value O1 O2 O3 P1 "abc" O1 O2 O3 P2 "xyz" O1 O3 O4 P1 "123" O2 O4 O5 P1 "098" 

Second , the inheritance tree:

 O1 O2 O4 O3 O5 

Or regarded as a relation:

 Object Parent O2 O1 O4 O2 O3 O1 O5 O3 O1 null 

The semantics of this is that O2 inherits properties from O1; O4 is from O2 and O1; O3 - from O1; and O5 are from O3 and O1 in this priority order.
NOTE 1: I have an effective way to select all children or all parents of a given object. Currently this is done with left and right indexes, but a hierarchy can also work. Now this does not seem important.
NOTE 2: I have a tiger to make sure that the “Object” column always contains all the possible objects, even if they really should not be there (ie Do not have a parent or children). This allows you to use inner join , rather than significantly less effecient outer join s.

Purpose : if a pair is specified (Property, value), return all triples of objects that have this property with a value that is explicitly defined or inherited from the parent.

NOTE 1: An object triple (X,Y,Z) is considered a “parent” triple (A,B,C) when it is true, either either X = A or X is a parent of A , and the same is true for (Y,B) and (Z,C) .
NOTE 2: The property specified in the closer parent "overrides" the same property that is defined for the more distant parent.
NOTE 3: When (A, B, C) has two parents - (X1, Y1, Z1) and (X2, Y2, Z2), then (X1, Y1, Z1) is considered a “closer” parent when:
(a) X2 is the parent of X1, or
(b) X2 = X1 and Y2 is the parent of Y1, or
(c) X2 = X1 and Y2 = Y1, and Z2 is the parent of Z1

In other words, “proximity” in the pedigree for triples is determined first on the first components of the triple, then on the second components, then on the third components. This rule establishes an unambiguous partial order for triples in terms of a pedigree.

For example, given the pair (P1, "abc"), the result set of triples will be:

  O1, O2, O3 -- Defined explicitly O1, O2, O5 -- Because O5 inherits from O3 O1, O4, O3 -- Because O4 inherits from O2 O1, O4, O5 -- Because O4 inherits from O2 and O5 inherits from O3 O2, O2, O3 -- Because O2 inherits from O1 O2, O2, O5 -- Because O2 inherits from O1 and O5 inherits from O3 O2, O4, O3 -- Because O2 inherits from O1 and O4 inherits from O2 O3, O2, O3 -- Because O3 inherits from O1 O3, O2, O5 -- Because O3 inherits from O1 and O5 inherits from O3 O3, O4, O3 -- Because O3 inherits from O1 and O4 inherits from O2 O3, O4, O5 -- Because O3 inherits from O1 and O4 inherits from O2 and O5 inherits from O3 O4, O2, O3 -- Because O4 inherits from O1 O4, O2, O5 -- Because O4 inherits from O1 and O5 inherits from O3 O4, O4, O3 -- Because O4 inherits from O1 and O4 inherits from O2 O5, O2, O3 -- Because O5 inherits from O1 O5, O2, O5 -- Because O5 inherits from O1 and O5 inherits from O3 O5, O4, O3 -- Because O5 inherits from O1 and O4 inherits from O2 O5, O4, O5 -- Because O5 inherits from O1 and O4 inherits from O2 and O5 inherits from O3 

Please note that the triple (O2, O4, O5) is not in this list. This is due to the fact that property P1 is explicitly defined for the triple (O2, O4, O5), and this prevents this triple from inheriting this property (O1, O2, O3). Also note that the triple (O4, O4, O5) is also missing. This is due to the fact that this triple inherits its value P1 = "098" from (O2, O4, O5), because it is a closer parent than (O1, O2, O3).

An easy way to do this is as follows. First, for each triple defined by a property, select all possible trinities for children:

 select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value from TriplesAndProperties tp -- Select corresponding objects of the triple inner join Objects as Objects1 on Objects1.Id = tp.O1 inner join Objects as Objects2 on Objects2.Id = tp.O2 inner join Objects as Objects3 on Objects3.Id = tp.O3 -- Then add all possible children of all those objects inner join Objects as Children1 on Objects1.Id [isparentof] Children1.Id inner join Objects as Children2 on Objects2.Id [isparentof] Children2.Id inner join Objects as Children3 on Objects3.Id [isparentof] Children3.Id 

But this is not the whole story: if a triple inherits the same property from several parents, this query will give conflicting results. Therefore, the second step is to select only one of these conflicting results:

 select * from ( select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value, row_number() over( partition by Children1.Id, Children2.Id, Children3.Id, tp.Property order by Objects1.[depthInTheTree] descending, Objects2.[depthInTheTree] descending, Objects3.[depthInTheTree] descending ) as InheritancePriority from ... (see above) ) where InheritancePriority = 1 

The window function row_number() over( ... ) performs the following actions: for each unique combination of triple and property objects, it sorts all the values ​​at the distance of the ancestors from the triple to the parents to which the value is inherited, and then I select only the very first one received in The result is a list of values. A similar effect can be achieved using the GROUP BY and ORDER BY statements, but I just find that the window function is semantically cleared (the execution plans they give are identical). The fact is that I need to choose the closest of my predecessors, and for this I need to group, and then sort inside the group.

And finally, now I can just filter the result set using the property and value.

This circuit works. Very reliable and predictable. It turned out to be very powerful for the business task that it implements.

The only problem: she is awfuly slow .
It can be noted that joining seven tables can slow down the work, but in fact this is not a bottleneck.

According to the actual execution plan that I get from SQL Management Studio (as well as SQL Profiler), sorting is the bottleneck. The problem is that in order to satisfy my window function, the server must sort by Children1.Id, Children2.Id, Children3.Id, tp.Property, Parents1.[depthInTheTree] descending, Parents2.[depthInTheTree] descending, Parents3.[depthInTheTree] descending , and there can be no indexes that it can use, because the values ​​come from the cross join of several tables.

EDIT: At the suggestion of Michael Buen (thanks, Michael), I posted the whole riddle for sqlfiddle here.In terms of execution, you can see that the Sort operation takes into account 32% of the entire query, and this will grow with the number of full rows, since all other operations use indexes .

Usually in such cases, I would use an indexed view, but not in this case, because indexed views cannot contain self-joins, of which six.

The only way I can think of so far is to create six instances of the Objects table and then use them for joins, which will allow indexing the view.
Is it time that I will be reduced to such hacks? Despair comes.

Answer 1

I have 3 possible answers.

The sql script for your question is here: http://sqlfiddle.com/#!3/7c7a0/3/0

The sql script for my answer is here: http://sqlfiddle.com/#!3/5d257/1

Warnings:

• Query Analyzer is not enough. . I noticed that a number of responses were rejected because their query plans are more expensive than the original request. The analyzer is just a guide. Depending on the actual data set, hardware, and use case, more expensive queries may return faster than less expensive ones. You must test in your environment.
• The query analyzer is inefficient - even if you find a way to remove the “most expensive step” from the query, it often has nothing to do with your query.
• Only query changes rarely eliminate schema / design issues. . Some responses were rejected because they were related to schema level changes, such as triggers and additional tables. Complex queries that oppose optimization are a strong sign that the problem is with the underlying design or with my expectations. You may not like this, but you may have to admit that the problem is not solvable at the query level.
• An indexed view cannot contain a row_number () / partitition clause . Working on the self-join problem by creating six instances of the object table is not enough for you to create the indexed view you proposed, I tried this in this sqlfiddle . If you uncomment this last create index statement, you will get an error because your view "contains a ranking function or aggregate window".

Answers on questions:

• Left registration instead of row_number () . You can use a query that uses left joins to exclude results that were overridden below in the tree. Removing the final “order by” from this query actually removes the sort that haunted you! The execution plan for this request is still more expensive than the original, but see Disclaimer No. 1 above.
• Indexed view for the query part . Using some serious query magic (based on this method ), I created an indexed view for the query part. This view can be used to improve the original question or answer # 1.
• Update to a well indexed table . Someone suggested this answer, but they may not have explained it well. If your result set is very large or you update the source tables very often, then updating the query results and using a trigger to keep them up to date is a great way around this problem. Once you have created a view for your query, just check this option. You can reuse answer # 2 to speed up the launch, and then further improve it over time. (You are talking about creating six copies of your tables, first try this. This ensures that the performance for the question you select will be the highest possible.)

Here is part of my answer from sqlfiddle:

 Create Table Objects ( Id int not null identity primary key, LeftIndex int not null default 0, RightIndex int not null default 0 ) alter table Objects add ParentId int null references Objects CREATE TABLE TP ( Object1 int not null references Objects, Object2 int not null references Objects, Object3 int not null references Objects, Property varchar(20) not null, Value varchar(50) not null ) insert into Objects(LeftIndex, RightIndex) values(1, 10) insert into Objects(ParentId, LeftIndex, RightIndex) values(1, 2, 5) insert into Objects(ParentId, LeftIndex, RightIndex) values(1, 6, 9) insert into Objects(ParentId, LeftIndex, RightIndex) values(2, 3, 4) insert into Objects(ParentId, LeftIndex, RightIndex) values(3, 7, 8) insert into TP(Object1, Object2, Object3, Property, Value) values(1,2,3, 'P1', 'abc') insert into TP(Object1, Object2, Object3, Property, Value) values(1,2,3, 'P2', 'xyz') insert into TP(Object1, Object2, Object3, Property, Value) values(1,3,4, 'P1', '123') insert into TP(Object1, Object2, Object3, Property, Value) values(2,4,5, 'P1', '098') create index ix_LeftIndex on Objects(LeftIndex) create index ix_RightIndex on Objects(RightIndex) create index ix_Objects on TP(Property, Value, Object1, Object2, Object3) create index ix_Prop on TP(Property) GO ---------- QUESTION ADDITIONAL SCHEMA -------- CREATE VIEW TPResultView AS Select O1, O2, O3, Property, Value FROM ( select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value, row_number() over( partition by Children1.Id, Children2.Id, Children3.Id, tp.Property order by Objects1.LeftIndex desc, Objects2.LeftIndex desc, Objects3.LeftIndex desc ) as Idx from tp -- Select corresponding objects of the triple inner join Objects as Objects1 on Objects1.Id = tp.Object1 inner join Objects as Objects2 on Objects2.Id = tp.Object2 inner join Objects as Objects3 on Objects3.Id = tp.Object3 -- Then add all possible children of all those objects inner join Objects as Children1 on Children1.LeftIndex between Objects1.LeftIndex and Objects1.RightIndex inner join Objects as Children2 on Children2.LeftIndex between Objects2.LeftIndex and Objects2.RightIndex inner join Objects as Children3 on Children3.LeftIndex between Objects3.LeftIndex and Objects3.RightIndex ) as x WHERE idx = 1 GO ---------- ANSWER 1 SCHEMA -------- CREATE VIEW TPIntermediate AS select tp.Property, tp.Value , Children1.Id as O1, Children2.Id as O2, Children3.Id as O3 , Objects1.LeftIndex as PL1, Objects2.LeftIndex as PL2, Objects3.LeftIndex as PL3 , Children1.LeftIndex as CL1, Children2.LeftIndex as CL2, Children3.LeftIndex as CL3 from tp -- Select corresponding objects of the triple inner join Objects as Objects1 on Objects1.Id = tp.Object1 inner join Objects as Objects2 on Objects2.Id = tp.Object2 inner join Objects as Objects3 on Objects3.Id = tp.Object3 -- Then add all possible children of all those objects inner join Objects as Children1 WITH (INDEX(ix_LeftIndex)) on Children1.LeftIndex between Objects1.LeftIndex and Objects1.RightIndex inner join Objects as Children2 WITH (INDEX(ix_LeftIndex)) on Children2.LeftIndex between Objects2.LeftIndex and Objects2.RightIndex inner join Objects as Children3 WITH (INDEX(ix_LeftIndex)) on Children3.LeftIndex between Objects3.LeftIndex and Objects3.RightIndex GO ---------- ANSWER 2 SCHEMA -------- -- Partial calculation using an indexed view -- Circumvented the self-join limitation using a black magic technique, based on -- http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html CREATE TABLE dbo.multiplier (i INT PRIMARY KEY) INSERT INTO dbo.multiplier VALUES (1) INSERT INTO dbo.multiplier VALUES (2) INSERT INTO dbo.multiplier VALUES (3) GO CREATE VIEW TPIndexed WITH SCHEMABINDING AS SELECT tp.Object1, tp.object2, tp.object3, tp.property, tp.value, SUM(ISNULL(CASE Mi WHEN 1 THEN Objects.LeftIndex ELSE NULL END, 0)) as PL1, SUM(ISNULL(CASE Mi WHEN 2 THEN Objects.LeftIndex ELSE NULL END, 0)) as PL2, SUM(ISNULL(CASE Mi WHEN 3 THEN Objects.LeftIndex ELSE NULL END, 0)) as PL3, SUM(ISNULL(CASE Mi WHEN 1 THEN Objects.RightIndex ELSE NULL END, 0)) as PR1, SUM(ISNULL(CASE Mi WHEN 2 THEN Objects.RightIndex ELSE NULL END, 0)) as PR2, SUM(ISNULL(CASE Mi WHEN 3 THEN Objects.RightIndex ELSE NULL END, 0)) as PR3, COUNT_BIG(*) as ID FROM dbo.tp cross join dbo.multiplier M inner join dbo.Objects on (Mi = 1 AND Objects.Id = tp.Object1) or (Mi = 2 AND Objects.Id = tp.Object2) or (Mi = 3 AND Objects.Id = tp.Object3) GROUP BY tp.Object1, tp.object2, tp.object3, tp.property, tp.value GO -- This index is mostly useless but required create UNIQUE CLUSTERED index pk_TPIndexed on dbo.TPIndexed(property, value, object1, object2, object3) -- Once we have the clustered index, we can create a nonclustered that actually addresses our needs create NONCLUSTERED index ix_TPIndexed on dbo.TPIndexed(property, value, PL1, PL2, PL3, PR1, PR2, PR3) GO -- NOTE: this View is not indexed, but is uses the indexed view CREATE VIEW TPIndexedResultView AS Select O1, O2, O3, Property, Value FROM ( select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value, row_number() over( partition by tp.Property, Children1.Id, Children2.Id, Children3.Id order by tp.Property, Tp.PL1 desc, Tp.PL2 desc, Tp.PL3 desc ) as Idx from TPIndexed as TP WITH (NOEXPAND) -- Then add all possible children of all those objects inner join Objects as Children1 WITH (INDEX(ix_LeftIndex)) on Children1.LeftIndex between TP.PL1 and TP.PR1 inner join Objects as Children2 WITH (INDEX(ix_LeftIndex)) on Children2.LeftIndex between TP.PL2 and TP.PR2 inner join Objects as Children3 WITH (INDEX(ix_LeftIndex)) on Children3.LeftIndex between TP.PL3 and TP.PR3 ) as x WHERE idx = 1 GO -- NOTE: this View is not indexed, but is uses the indexed view CREATE VIEW TPIndexedIntermediate AS select tp.Property, tp.Value , Children1.Id as O1, Children2.Id as O2, Children3.Id as O3 , PL1, PL2, PL3 , Children1.LeftIndex as CL1, Children2.LeftIndex as CL2, Children3.LeftIndex as CL3 from TPIndexed as TP WITH (NOEXPAND) -- Then add all possible children of all those objects inner join Objects as Children1 WITH (INDEX(ix_LeftIndex)) on Children1.LeftIndex between TP.PL1 and TP.PR1 inner join Objects as Children2 WITH (INDEX(ix_LeftIndex)) on Children2.LeftIndex between TP.PL2 and TP.PR2 inner join Objects as Children3 WITH (INDEX(ix_LeftIndex)) on Children3.LeftIndex between TP.PL3 and TP.PR3 GO ---------- ANSWER 3 SCHEMA -------- -- You're talking about making six copies of the TP table -- If you're going to go that far, you might as well, go the trigger route -- The performance profile is much the same - slower on insert, faster on read -- And instead of still recalculating on every read, you'll be recalculating -- only when the data changes. CREATE TABLE TPResult ( Object1 int not null references Objects, Object2 int not null references Objects, Object3 int not null references Objects, Property varchar(20) not null, Value varchar(50) not null ) GO create UNIQUE index ix_Result on TPResult(Property, Value, Object1, Object2, Object3) --You'll have to imagine this trigger, sql fiddle doesn't want to do it --CREATE TRIGGER tr_TP --ON TP -- FOR INSERT, UPDATE, DELETE --AS -- DELETE FROM TPResult -- -- For this example we'll just insert into the table once INSERT INTO TPResult SELECT O1, O2, O3, Property, Value FROM TPResultView 

Request part of my answer from sqlfiddle:

 -------- QUESTION QUERY ---------- -- Original query, modified to use the view I added SELECT O1, O2, O3, Property, Value FROM TPResultView WHERE property = 'P1' AND value = 'abc' -- Your assertion is that this order by is the most expensive part. -- Sometimes converting queries into views allows the server to -- Optimize them better over time. -- NOTE: removing this order by has no effect on this query. -- ORDER BY O1, O2, O3 GO -------- ANSWER 1 QUERY ---------- -- A different way to get the same result. -- Query optimizer says this is more expensive, but I've seen cases where -- it says a query is more expensive but it returns results faster. SELECT O1, O2, O3, Property, Value FROM ( SELECT A.O1, A.O2, A.O3, A.Property, A.Value FROM TPIntermediate A LEFT JOIN TPIntermediate B ON A.O1 = B.O1 AND A.O2 = B.O2 AND A.O3 = B.O3 AND A.Property = B.Property AND ( -- Find any rows with Parent LeftIndex triplet that is greater than this one (A.PL1 < B.PL1 AND A.PL2 < B.PL2 AND A.PL3 < B.PL3) OR -- Find any rows with LeftIndex triplet that is greater than this one (A.CL1 < B.CL1 AND A.CL2 < B.CL2 AND A.CL3 < B.CL3) ) -- If this row has any rows that match the previous two cases, exclude it WHERE B.O1 IS NULL ) AS x WHERE property = 'P1' AND value = 'abc' -- NOTE: Removing this order _DOES_ reduce query cost removing the "sort" action -- that has been the focus of your question. -- Howeer, it wasn't clear from your question whether this order by was required. --ORDER BY O1, O2, O3 GO -------- ANSWER 2 QUERIES ---------- -- Same as above but using an indexed view to partially calculate results SELECT O1, O2, O3, Property, Value FROM TPIndexedResultView WHERE property = 'P1' AND value = 'abc' -- Your assertion is that this order by is the most expensive part. -- Sometimes converting queries into views allows the server to -- Optimize them better over time. -- NOTE: removing this order by has no effect on this query. --ORDER BY O1, O2, O3 GO SELECT O1, O2, O3, Property, Value FROM ( SELECT A.O1, A.O2, A.O3, A.Property, A.Value FROM TPIndexedIntermediate A LEFT JOIN TPIndexedIntermediate B ON A.O1 = B.O1 AND A.O2 = B.O2 AND A.O3 = B.O3 AND A.Property = B.Property AND ( -- Find any rows with Parent LeftIndex triplet that is greater than this one (A.PL1 < B.PL1 AND A.PL2 < B.PL2 AND A.PL3 < B.PL3) OR -- Find any rows with LeftIndex triplet that is greater than this one (A.CL1 < B.CL1 AND A.CL2 < B.CL2 AND A.CL3 < B.CL3) ) -- If this row has any rows that match the previous two cases, exclude it WHERE B.O1 IS NULL ) AS x WHERE property = 'P1' AND value = 'abc' -- NOTE: Removing this order _DOES_ reduce query cost removing the "sort" action -- that has been the focus of your question. -- Howeer, it wasn't clear from your question whether this order by was required. --ORDER BY O1, O2, O3 GO -------- ANSWER 3 QUERY ---------- -- Returning results from a pre-calculated table is fast and easy -- Unless your are doing many more inserts than reads, or your result -- set is very large, this is a fine way to compensate for a poor design -- in one area of your database. SELECT Object1 as O1, Object2 as O2, Object3 as O3, Property, Value FROM TPResult WHERE property = 'P1' AND value = 'abc' ORDER BY O1, O2, O3 

Answer 2

You can speed this up by materializing the join in an indexed table, say joinresult. This has the disadvantage of requiring space and saving disk space. But this has the advantage that you can use the index for the slow part.

 insert into joinedresult select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value,Objects1.[depthInTheTree] as O1D,Objects2.[depthInTheTree] as O2D,Objects3. depthInTheTree] as O3D from ... (see above) 

Make sure that joinresult has an index in [O1, O2, O3, Property, O1D, O2D, O3D] and clear it before starting. Then

 select * from ( select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value, row_number() over( partition by Children1.Id, Children2.Id, Children3.Id, tp.Property order by O1D descending, O2D descending, O3D descending ) as InheritancePriority from joinedresult ) where InheritancePriority = 1 

Answer 3

Have you tried the index (or set pk) first, the column "Value", the second column "Property", the third column "Object1", the fourth column "Object2" and the column "Object3"? I suggest that "Value" is more restrictive than "Property".

I also assume that you have an identifier column as the primary key and a foreign key relationship between ParentId and Id.

How is this query executed ?:

  with -- First, get all combinations that match the property/value pair. validTrip as ( select Object1, Object2, Object3 from TriplesAndProperties where value = @value and property = @property ), -- Recursively flatten the inheritance hierarchy of Object1, 2 and 3. o1 as ( select Id, 0 as InherLevel from Objects where Id in (select Object1 from validTrip) union all select rec.Id, InherLevel + 1 from Objects rec inner join o1 base on rec.Parent = base.[Object] ), o2 as ( select Id, 0 as InherLevel from Objects where Id in (select Object2 from validTrip) union all select rec.Id, InherLevel + 1 from Objects rec inner join o2 base on rec.Parent = base.[Object] ), o3 as ( select Id, 0 as InherLevel from Objects where Id in (select Object3 from validTrip) union all select rec.Id, InherLevel + 1 from Objects rec inner join o3 base on rec.Parent = base.[Object] ) -- select the Id triple. select o1.Id, o2.Id, o3.Id N -- match every option in o1, with every option in o2, with every option in o3. from o1 cross join o2 cross join o3 -- order by the inheritance level. order by o1.InherLevel, o2.InherLevel, o3.InherLevel; 

Answer 4

Hierarchical queries , i.e. WITH RECURSIVE ... or patented equivalents like CONNECT BY are your friend in this case.

The recipe for solving your specific problem: Start with a vacation and go up to the root aggregation and exclude everything that has already been found.

Answer 5

I assume your table is quite large. Hence the slowness. , ( 2 ). "where property = 'P1" CTE. , , , .

- : http://sqlfiddle.com/#!3/7c7a0/92/0

Answer 6

- . , . , CACHE , . .

Option 1

SQL - . , , .

, , .

- "".

. SQL:

 select * from ( select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value, row_number() over( partition by Children1.Id, Children2.Id, Children3.Id, tp.Property order by closeness DESC ) as InheritancePriority from ... (see above) ) where InheritancePriority = 1 

, TriplesAndProperties. "", node (O1). closeness(tuple) = closeness(Object1)*100+closeness(Object2)*10+closeness(Object3)

, - , .

, , .

---

Option 2

, .

TriplesAndProperties : Object1, Object2, Object3, Property, Value, Effective_Object1, Effective_Object2, Effective_Object3, Closeness .

, .

/ (X, Y, Z), :

 (X,Y,Z,Property,Value,X,Y,Z,0) (X,Y,Z,Property,Value,X,Y,Z.child,1) (X,Y,Z,Property,Value,X,Y,Z.grandchild,2) (X,Y,Z,Property,Value,X,Y.child,Z,10) (X,Y,Z,Property,Value,X,Y.child,Z.child,11) (X,Y,Z,Property,Value,X,Y.child,Z.grandchild,12) (X,Y,Z,Property,Value,X,Y.grandchild,Z,20) (X,Y,Z,Property,Value,X,Y.grandchild,Z.child,21) (X,Y,Z,Property,Value,X,Y.grandchild,Z.grandchild,22) ... ... 

, // ~ 20 . .

.

:

 SELECT * FROM ( SELECT Effective_Object1, Effective_Object2, Effective_Object3, Property, Value, row_number() over( partition by Effective_Object1, Effective_Object2, Effective_Object3, Property order by Closeness DESC ) AS InheritancePriority FROM TriplesAndProperties ) WHERE InheritancePriority = 1; 

, , (Effective_Object1, Effective_Object2, Effective_Object3, Property, Closeness).

---

, , , .