RfC: Resource tree database setup

Heiko W.Rupp hrupp at redhat.com
Tue Jul 6 08:39:23 UTC 2010


Hi,

With in Resource.java we define quite some queries like 
Resource.QUERY_FIND_DESCENDENTS

that go

        + "SELECT r.id " //
        + "  FROM Resource r " //
        + " WHERE r.id = :resourceId " //
        + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.id = :resourceId) "
        + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.parentResource.id = :resourceId) "
        + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.parentResource.parentResource.id = :resourceId) "
        + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.parentResource.parentResource.parentResource.id = :resourceId) "
        + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.parentResource.parentResource.parentResource.parentResource.id = :resourceId) "
        + "    OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.parentResource.parentResource.parentResource.parentResource.parentResource.id = :resourceId) "


Oracle EM reports very high cpu usage when processing them.

I wonder if we should add a 'helper table' (or materialized view), which gets updated on resource creation/deletion
a little like this:
RHQ_RESOURCE_PARENT_CHILD  (=  X in the following examples)
( 
     int resourceId;
     int childId;
     int resourceTypeId;
     int childTypeId;
     int resourceCategoryId;
)
with a pk of (resourceId, childId)

Entries would be for each resource id we have in inventory its id, the ids of all of its children, type of resource, category of resource.

Some example queries:
- To find the parent platform of a resource

   select  resourceId from X where childId = :child and resourceCategory = platform;

- To find all resources below a given root (e.g. for marking as uninventoried)
   
   select childId from X where resourceId = :root;

- To find all children of a certain type (= autogroup)

  select childIf from X where resourceId = :root and childTypeId = :childType

- mark all children of a root as deleted (2 steps now, 2nd one to get rid of resources to delete from X )

  update RHQ_RESOURCE set INVENTORY_STATUS= :status ,...
          where id in (select childId FROM x where resourceId = :root)
  delete FROM X where resourceId = :root
  


I am sure that there are a ton more of information that could be included here
like the 'owner' of a resource or its name. In any cases some good indices
would be needed.

Also to replace existing queries, in some cases would perhaps need two
roundtrips to the database, so in this case one would need to investigate
the benefits here.



-- 
Reg. Adresse: Red Hat GmbH, Otto-Hahn-Strasse 20, 85609 Dornach bei München
Handelsregister: Amtsgericht München HRB 153243
Geschaeftsführer: Brendan Lane, Charlie Peters, Michael Cunningham, Charles Cachera



More information about the rhq-devel mailing list