[Rt-devel] Re: SetOwner speed

[Alexey G Misyurenko]

Ruslan Zakirov wrote:
> On 6/15/06, Alexey G Misyurenko <mag at caravan.ru> wrote:
>> Ruslan Zakirov wrote:
>> > Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
>> > should help you with query 4, but to help you with other queries I
>> > need to see EXPLAINs...
>> >
Sorry for the pause with answer - second oracle installation take a time...

>> > Please, make explains before you'll add index and after.
>> >


  WITH INDEX
  ----------
  SQL Statement from editor:
  
  
  SELECT *
    FROM (SELECT limitquery.*, ROWNUM limitrownum
            FROM (SELECT acl.ID
                    FROM acl, GROUPS, principals, cachedgroupmembers
                   WHERE (   acl.rightname = 'SuperUser'
                          OR acl.rightname = 'DelegateRights'
                         )
                     AND principals.disabled = 0
                     AND cachedgroupmembers.disabled = 0
                     AND principals.ID = GROUPS.ID
                     AND principals.principaltype = 'Group'
                     AND principals.ID = cachedgroupmembers.groupid
                     AND cachedgroupmembers.memberid = 10
                     AND (   (acl.objecttype = 'RT::System' AND 
acl.objectid = 1
                             )
                          OR (acl.objecttype = 'RT::System')
                         )
                     AND acl.principalid = principals.ID
                     AND acl.principaltype = 'Group') limitquery
           WHERE ROWNUM <= 1)
   WHERE limitrownum >= 1
 
  ------------------------------------------------------------
   
  Statement Id=4203132   Type=
  Cost=2,64017018715267E-308  TimeStamp=17-06-06::00::53:49
 
       (1)  SELECT STATEMENT  CHOOSE
     Est. Rows: 1  Cost: 9
       (13)  VIEW (Embedded SQL)
     Est. Rows: 1  Cost: 9
           (12)  COUNT STOPKEY
               (11)  TABLE ACCESS BY INDEX ROWID RTADMIN.ACL  [Analyzed]
               (11)   Blocks: 13 Est. Rows: 1 of 1 086  Cost: 2
                    Tablespace: USERS
                   (10)  NESTED LOOPS
                        Est. Rows: 1  Cost: 9
                       (8)  NESTED LOOPS
                            Est. Rows: 1  Cost: 7
                           (6)  NESTED LOOPS
                                Est. Rows: 1  Cost: 6
                               (3)  TABLE ACCESS BY INDEX ROWID 
RTADMIN.CACHEDGROUPMEMBERS  [Analyzed]
                               (3)   Blocks: 21 317 Est. Rows: 1 of 
4 538 707  Cost: 4
                                    Tablespace: USERS
                                   (2)  NON-UNIQUE INDEX RANGE SCAN 
RTADMIN.CACHEDGR_NDX  [Analyzed]
                                        Est. Rows: 1  Cost: 3
                               (5)  TABLE ACCESS BY INDEX ROWID 
RTADMIN.PRINCIPALS  [Analyzed]
                               (5)   Blocks: 7 174 Est. Rows: 1 of 
2 164 662  Cost: 2
                                    Tablespace: USERS
                                   (4)  UNIQUE INDEX UNIQUE SCAN 
RTADMIN.PRINCIPALS_KEY  [Analyzed]
                                        Est. Rows: 1  Cost: 1
                           (7)  UNIQUE INDEX UNIQUE SCAN 
RTADMIN.GROUPS_KEY  [Analyzed]
                                Est. Rows: 1  Cost: 1
                       (9)  NON-UNIQUE INDEX RANGE SCAN 
RTADMIN.QUEST_SX_D297E41F3CD1F7F  [Not Analyzed]
                            Est. Rows: 30  Cost: 1


====================

WITHOUT INDEX

  SQL Statement from editor:
  
  
  SELECT *
    FROM (SELECT limitquery.*, ROWNUM limitrownum
            FROM (SELECT acl.ID
                    FROM acl, GROUPS, principals, cachedgroupmembers
                   WHERE (   acl.rightname = 'SuperUser'
                          OR acl.rightname = 'DelegateRights'
                         )
                     AND principals.disabled = 0
                     AND cachedgroupmembers.disabled = 0
                     AND principals.ID = GROUPS.ID
                     AND principals.principaltype = 'Group'
                     AND principals.ID = cachedgroupmembers.groupid
                     AND cachedgroupmembers.memberid = 10
                     AND (   (acl.objecttype = 'RT::System' AND 
acl.objectid = 1
                             )
                          OR (acl.objecttype = 'RT::System')
                         )
                     AND acl.principalid = principals.ID
                     AND acl.principaltype = 'Group') limitquery
           WHERE ROWNUM <= 1)
   WHERE limitrownum >= 1
 
  ------------------------------------------------------------
   
  Statement Id=1460648   Type=
  Cost=2,64017018715267E-308  TimeStamp=17-06-06::01::02:32
 
       (1)  SELECT STATEMENT  CHOOSE
     Est. Rows: 1  Cost: 9
       (13)  VIEW (Embedded SQL)
     Est. Rows: 1  Cost: 9
           (12)  COUNT STOPKEY
               (11)  TABLE ACCESS BY INDEX ROWID RTADMIN.ACL  [Analyzed]
               (11)   Blocks: 13 Est. Rows: 1 of 1 086  Cost: 2
                    Tablespace: USERS
                   (10)  NESTED LOOPS
                        Est. Rows: 1  Cost: 9
                       (8)  NESTED LOOPS
                            Est. Rows: 1  Cost: 7
                           (6)  NESTED LOOPS
                                Est. Rows: 1  Cost: 6
                               (3)  TABLE ACCESS BY INDEX ROWID 
RTADMIN.CACHEDGROUPMEMBERS  [Analyzed]
                               (3)   Blocks: 21 317 Est. Rows: 1 of 
4 538 707  Cost: 4
                                    Tablespace: USERS
                                   (2)  NON-UNIQUE INDEX RANGE SCAN 
RTADMIN.CACHEDGR_NDX  [Analyzed]
                                        Est. Rows: 1  Cost: 3
                               (5)  TABLE ACCESS BY INDEX ROWID 
RTADMIN.PRINCIPALS  [Analyzed]
                               (5)   Blocks: 7 174 Est. Rows: 1 of 
2 164 662  Cost: 2
                                    Tablespace: USERS
                                   (4)  UNIQUE INDEX UNIQUE SCAN 
RTADMIN.PRINCIPALS_KEY  [Analyzed]
                                        Est. Rows: 1  Cost: 1
                           (7)  UNIQUE INDEX UNIQUE SCAN 
RTADMIN.GROUPS_KEY  [Analyzed]
                                Est. Rows: 1  Cost: 1
                       (9)  NON-UNIQUE INDEX RANGE SCAN 
RTADMIN.QUEST_SX_D297E41F3CD1F7F  [Not Analyzed]
                            Est. Rows: 30  Cost: 1



-- 
WBR,   Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP            http://www.caravan.ru
Phone: +7 095 3632252         Cell:  +7 095 5082794