Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Question Unanswered: I am executing this below query and measuring the time that it takes to fetch the rec

    I am executing this below query and measuring the time that it takes to fetch the records with and without creating composite index....



    SELECT * FROM (SELECT ru.* FROM ddd_USER ru
    inner join ddd_user_group rg ON ru.id = rg.user_id
    inner join ddd_group_permission rgp ON rg.group_id = rgp.group_id
    inner join ddd_permission rp ON rgp.permission_id = rp.id WHERE ru.ACTIVE_FLAG='Y' AND rp.name LIKE 'write%' )

    HTML Code:
        CREATE INDEX ddd_USER_ix ON ddd_USER (ACTIVE_FLAG, name);
        
        EXPLAIN PLAN for SELECT ru.ID,ru.NAME FROM ddd_USER ru
        inner join ddd_user_group rg ON ru.id = rg.user_id
        inner join ddd_group_permission rgp ON rg.group_id = rgp.group_id
        inner join ddd_permission rp ON rgp.permission_id = rp.id WHERE ru.ACTIVE_FLAG='Y' AND rp.name LIKE 'write%'
        
        without index  :-
        ------------
        
        Total execution time 500 ms
        
        
        with index :-
        ----------
        
        Total execution time 484 ms

    could you please advise how can I optimize it more effiecntly so that no more inner joins are required and all the things can go in one way and performance time also get approved

    folks please advise how the performance could be improved and query can become more efficient

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You would need at least these indexes:
    Code:
    Table                 Index
    --------------------  ---------
    ddd_user_group        user_id
    ddd_group_permission  group_id
    ddd_permission        id
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •