Skip to Content.
Sympa Menu

grouper-dev - flat membership views and change log view

Subject: Grouper Developers Forum

List archive

flat membership views and change log view

Chronological Thread 
  • From: Chris Hyzer <>
  • To: Grouper Dev <>
  • Subject: flat membership views and change log view
  • Date: Thu, 13 May 2010 00:03:16 -0400
  • Accept-language: en-US
  • Acceptlanguage: en-US

I added these DB views:


with the following columns:

"created_on", "change_log_category", "action_name", "sequence_number",
             "label_string01", "string01", "label_string02", "string02",
             "label_string03", "string03", "label_string04", "string04",
             "label_string05", "string05", "label_string06", "string06",
             "label_string07", "string07", "label_string08", "string08",
             "label_string09", "string09", "label_string10", "string10",
             "label_string11", "string11", "label_string12", "string12",
             "context_id", "change_log_type_id"

This makes the change log easier for a human to read.



There are three new views with the same columns as the previous three lightweight membership views:






These are on the flattened tables.  If you are seeing if a user is in a group or listing the members of a group or the groups of a user, then flat and nonflat have similar performance (at least a similar cost in oracle explain plan).  However, if you do heavier things then the flat tables have much better performance.


e.g. select all memberships: SELECT * FROM grouper_memberships_lw_v


nonflat cost: 60,000

flat cost: 6,000


e.g. select all memberships that someone is allowed to read


SELECT mships.*

  FROM grouper_memberships_lw_v mships, grouper_memberships_lw_v privs

 WHERE mships.GROUP_ID = privs.GROUP_ID

   AND mships.list_name = 'members'

   AND privs.list_name IN ('readers', 'admins')

   AND privs.subject_source = 'abc'

   AND privs.subject_id = 'def';


nonflat cost: 16,000

flat cost: 52


Bottom line if you are doing complex operations, and you can deal with a few minutes of propagation delay, then the flat views will be a more performant way to get your data...

  • flat membership views and change log view, Chris Hyzer, 05/13/2010

Archive powered by MHonArc 2.6.16.

Top of Page