Home » SQL & PL/SQL » SQL & PL/SQL » Performance Speed for select from USER_ROLE_PRIVS (merged)
Performance Speed for select from USER_ROLE_PRIVS (merged) [message #414496] Wed, 22 July 2009 06:29 Go to next message
sysman2007
Messages: 24
Registered: March 2009
Junior Member
Hi
I have tables like this
TABLE SEC.XROLS
(
  SYS_CODE     VARCHAR2(6 BYTE)                 NOT NULL,
  ROL_PARAM    VARCHAR2(10 BYTE),
  ROL_DESC     VARCHAR2(80 BYTE),
  FRM_FORM     VARCHAR2(60 BYTE)                NOT NULL,
  ROL_ROLE     VARCHAR2(30 BYTE)                NOT NULL);


TABLE SEC.MENUS
(
  MENU_ENU       NUMBER(10),
  MENU_NAME      VARCHAR2(200 BYTE)             NOT NULL,
  MENU_DSC       VARCHAR2(300 BYTE),
  MENU_SYS_CODE  VARCHAR2(3 BYTE),
  MENU_MASTER    NUMBER(20)                     DEFAULT NULL,
  MENU_TYPE      NUMBER(1),
  MENU_ICON      VARCHAR2(100 BYTE),
  MENU_IS_ROLE   NUMBER(1)                      DEFAULT 1
)


VIEW SEC.VW_MENUS_ROLES
AS
   SELECT M.MENU_ENU,
          M.MENU_NAME,
          NVL (ROL_DESC, MENU_DSC) MENU_DSC,
          M.MENU_SYS_CODE,
          M.MENU_MASTER,
          M.MENU_TYPE,
          M.MENU_ICON,
          M.MENU_IS_ROLE
   FROM SEC.MENUS M, XROLS X,USER_ROLE_PRIVS U 
   WHERE SYS_CODE(+) = MENU_SYS_CODE 
   AND ROL_ROLE(+) = MENU_NAME
   AND M.MENU_NAME = U.GRANTED_ROLE(+);


VIEW SEC.VW_MENUS
AS
   SELECT M.MENU_ENU,
          M.MENU_NAME,
          NVL (ROL_DESC, MENU_DSC),
          M.MENU_SYS_CODE,
          M.MENU_MASTER,
          M.MENU_TYPE,
          M.MENU_ICON,
          M.MENU_IS_ROLE
   FROM SEC.MENUS M, XROLS X
   WHERE SYS_CODE(+) = MENU_SYS_CODE AND ROL_ROLE(+) = MENU_NAME;


When I run
SELECT *
FROM VW_MENUS;


That's run under 50ms
and when I run
SELECT *
FROM VW_MENUS_ROLES

That's run under 350ms!!!

is any way to speed up to run SELECT * FROM VW_MENUS_ROLES??
Re: Performance Speed for select from USER_ROLE_PRIVS [message #414505 is a reply to message #414496] Wed, 22 July 2009 06:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Previous Topic: Multiple call for procedure in same time from its main Procedure
Next Topic: Per Group ranking - how to continue rank numbers when new rows are inserted in a group?
Goto Forum:
  


Current Time: Sun Dec 04 16:20:27 CST 2016

Total time taken to generate the page: 0.07981 seconds