| 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
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 -
Certainly it will:
SQL> update emp
2 set comm = 0
3 where comm is null;
10 rows updated.
Execution Plan
| 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)|00:00:01 |
Predicate Information (identified by operation id):
2 - filter("COMM" IS NULL)
Note
Statistics
5 recursive calls
12 db block gets
15 consistent gets
0 physical reads
0 redo size
387 bytes sent via SQL*Net to client
326 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create index emp_fbi on
2 emp(sal+comm);
Index created.
SQL> SQL> set autotrace on SQL> SQL> select *
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80
800 0 20
7900 JAMES CLERK 7698 03-DEC-81
950 0 30
7876 ADAMS CLERK 7788 12-JAN-83
1100 0 20
7934 MILLER CLERK 7782 23-JAN-82
1300 0 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 0 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 0 30
7566 JONES MANAGER 7839 02-APR-81
2975 0 20
11 rows selected.
Execution Plan
| 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 | |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("SAL"+"COMM"<3000)
Note
Statistics
28 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
816 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL>
SQL> update emp
2 set sal=sal*1.2;
14 rows updated.
Execution Plan
| 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)|00:00:01 |
Note
Statistics
5 recursive calls
72 db block gets
15 consistent gets
0 physical reads
9660 redo size
390 bytes sent via SQL*Net to client
310 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> select *
2 from emp
3 where (sal+comm) < 3000;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80
960 0 20
7900 JAMES CLERK 7698 03-DEC-81
1140 0 30
7876 ADAMS CLERK 7788 12-JAN-83
1320 0 20
7934 MILLER CLERK 7782 23-JAN-82
1560 0 10
7844 TURNER SALESMAN 7698 08-SEP-81
1800 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1500
500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1920
300 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1500
1400 30
7782 CLARK MANAGER 7839 09-JUN-81
2940 0 10
9 rows selected.
Execution Plan
| 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 | |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("SAL"+"COMM"<3000)
Note
Statistics
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
732 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> You don't test your theories before you make them public?
David Fitzjarrell Received on Thu Dec 20 2007 - 07:58:19 CST
 |
 |