View performance problem

Hi,

    I'm a relative newcomer to Oracle. I'm trying to implement row & column security through views. Not unexpectedly, I'm running into performance problems. As an example, I've got the following view:

CREATE OR REPLACE VIEW V_OBJECT (
    PK_TABLE_NAME,
    PK_COL_NAME
 ) AS SELECT
    DECODE( PP2_SECURITY.PERM_BIT( PP2_SECURITY.PERMISSION( 'OBJECT',
'PK_TABLE_NAME', FK_REC_SEC_ID ), 1 ), 0, NULL, 1, RTRIM(PK_TABLE_NAME) )
PK_TABLE_NAME,
    DECODE( PP2_SECURITY.PERM_BIT( PP2_SECURITY.PERMISSION( 'OBJECT',
'PK_COL_NAME', FK_REC_SEC_ID ), 1 ), 0, NULL, 1, RTRIM(PK_COL_NAME) )
PK_COL_NAME
FROM OBJECT
WHERE PP2_SECURITY.VIEW_PERM( 'OBJECT', 'N/A', FK_REC_SEC_ID ) = 1     The column level security is in this part:

        PP2_SECURITY.PERM_BIT( PP2_SECURITY.PERMISSION( 'OBJECT',
'PK_TABLE_NAME', FK_REC_SEC_ID ), 1 )
    If this procedure returns 1, they can see the column, otherwise they can't. The decode returns either the contents of the column from the base table or NULL, depending on their permission.

    The row level security is in the view's WHERE clause:

        WHERE PP2_SECURITY.VIEW_PERM( 'OBJECT', 'N/A', FK_REC_SEC_ID ) = 1     Again, if this returns 1, they can see the row, otherwise not, so the view's output only includes rows they are allowed to see.

    This all works fine & fast for something like this:

        SELECT * FROM V_OBJECT;     It all works and the performance on that is just fine. However, when I do this:

        SELECT * FROM V_OBJECT WHERE PK_COL_NAME = 'FOO';     That's not so fine. It seems to take 10 seconds or so before it selects anything (vs. almost no time for a similar query on the base table) and then it doesn't seem like it's using any indexes, as it seems to be traversing the entire table. My guess is that the initial delay is the query optimizer trying to figure out what indexes to use, etc??? and then it decides to not use any when it can't figure it out.

    So, my question is, how can I improve this, or is there a better way?

    Any help will be gratefully accepted.

Thanks,
Tom Received on Sat Jan 20 2001 - 02:25:02 CST