| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Basic SQL and package question
<fitzjarrell_at_cox.net> schreef in bericht
news:030dd307-1486-4af0-a0c4-24d24918d1dc_at_t1g2000pra.googlegroups.com...
> On Dec 20, 2:06 am, "shakespeare" <what..._at_xs4all.nl> wrote:
>> <fitzjarr..._at_cox.net> schreef in >> berichtnews:ec811124-01c9-4f3d-b3b7-ac261fe274e6_at_d21g2000prf.googlegroups.com... >> >> >> >> >> >> > Comments embedded. >> > On Dec 19, 3:23 pm, Guy <guh..._at_yahoo.com> wrote: >> >> I have package procedure which returns 1 if a code is valid. Si I try: >> >> SELECT >> >> A, >> >> B, >> >> PACKAGE_1.PROC_1(A.CODE) AS CODE_PERMISSION >> >> FROM >> >> TABLE >> >> WHERE >> >> CODE_PERMISSION = 1 >> >> >> This returns and error message: CODE_PERMISSION invalid identifier. >> >> > And it should, as you cannot use a column alias from the SELECT list >> > in the WHERE clause of the same SELECT statement. >> >> >> So I have to resort to: >> >> SELECT >> >> A, >> >> B >> >> FROM >> >> TABLE >> >> WHERE >> >> PACKAGE_1.PROC_1(A.CODE) = 1 >> >> >> But I have been told that calling a procedure in the "where" clause >> >> was invalidating any index on this table, which is uge. >> >> > And a function-based index could cure that. If you'd written a >> > function instead of a procedure. >> >> >> Any solution >> >> to this ? Thanks. >> >> > Presuming you change your procedure to a function one way of using >> > your select-list alias is: >> >> > with perm as ( >> > select a, b, package_1.func_1(code) code_permission >> > from table >> > ) >> > select a, b, code_permission >> > from perm >> > where code_permission = 1; >> >> > However, why are you worried about an index when the 'column' in the >> > WHERE clause doesn't exist in the table and, as it is at the moment, >> > can't be indexed anyway because you wrote a procedure, not a >> > function? You, at the moment, have nothing in the way of indexes to >> > ignore. Had you written a function instead of a procedure you could >> > have possibly created a function-based index, and then using the >> > function in the WHERE clause would be of no concern as an index (your >> > function-based index) would be used: >> >> > create index table_fbi_code on >> > table(package_1.func_1(code)); >> >> > select a, b, package_1.func_1(code) code_permission >> > from table >> > where package_1.func_1(code) = 1; >> >> > And, voila, you have an indexed access path. >> >> > You need to read up on this: >> >> >http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema... >> >> > David Fitzjarrell >> >> I doubt if the procedure/function on hand will be deterministic, since >> permission is checked in a pl/sql global table:>>> I need to call the >> package procedure because a PL/SQL global table >> >>> contains the list of effective permissions to the connected users >> >> so I don't think a fbi will work. If the values in this table change for >> whatever reason, the fbi won't (or will it?) >> >> Shakespeare- Hide quoted text - >> >> - Show quoted text - >
>
>
> >
>> 00:00:01 |
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ---------------------------------------------------------------------------
> | 0 | UPDATE STATEMENT | | 1 | 13 | 3 (0)|
> 00:00:01 |
> | 1 | UPDATE | EMP | | |
> | |
> |* 2 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (0)|
>
>
>
> >
>
>
>
>
>> SQL>
> SQL>
> SQL> set autotrace on
>> ---------- ---------- --------- ---------- --------- ----------
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM DEPTNO
>
> >
>> 1 (0)| 00:00:01 |
> ---------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
> ---------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 11 | 957 |
> 2 (0)| 00:00:01 |
> | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 957 |
> 2 (0)| 00:00:01 |
> |* 2 | INDEX RANGE SCAN | EMP_FBI | 1 | |
>
>
>
> >
>
>
> >
>> 00:00:01 |
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ---------------------------------------------------------------------------
> | 0 | UPDATE STATEMENT | | 14 | 364 | 3 (0)|
> 00:00:01 |
> | 1 | UPDATE | EMP | | |
> | |
> | 2 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)|
>
> >
>
>> ---------- ---------- --------- ---------- --------- ----------
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM DEPTNO
>
> >
>> 1 (0)| 00:00:01 |
> ---------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
> ---------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 9 | 783 |
> 2 (0)| 00:00:01 |
> | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 9 | 783 |
> 2 (0)| 00:00:01 |
> |* 2 | INDEX RANGE SCAN | EMP_FBI | 1 | |
>
>
>
> >
>
>
> >
Well, this is a totally different case: a fbi based on two columns in the same row vs a value retrieved from a function which has no deterministic outcome: a user may have some permssion at one time, then the index is built and then the value from the procedure changes. Let's say I have a user with the permission to log in between 10:00 and 16:00, so in that time-window the function returns 1; outside the window it returns 0. (or the same for monday..friday or whatever). Or I just update a value in the permissions table ( which is btw not the table having the fbi). I'm quite sure this won't work..... just because fbi's need deterministic functions.
Shakespeare Received on Thu Dec 20 2007 - 14:00:31 CST
 |
 |