each session will allocate it's own unique segment. a GTT has no "segment" associated with it
until you use it and when you
a) commit or
b) logout
the segment goes "poof"
so, you have 365 sesssions -- and therefor 365 segments that live for a brief moment of time
Tuesday, August 30, 2011
Monday, August 29, 2011
application context
Updated May 4th, 2009
See this link for a superior approach:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908
Sounds like a good use of REF CURSORS to me. Lets say you wanted to build a generic
routine that would look at the inputs passed to it and build a WHERE clause for each
NON-NULL parameter passed. This would result in a large number of statically defined
cursors so we'll use a ref cursor instead.
I'll demonstrate below. I'll write a write that will print out some EMP data. This
routine will take upto 3 inputs to constrain the result set. I want to have upto 8
different cursors possible here
o 1 with NO where clause (all inputs null)
o 3 with a single predicate
o 3 with "pairs" of predicate conditions
o 1 with all three predicate conditions
Additionally, since the use of BIND VARIABLES is one of the MOST important things in
programming Oracle -- I'll want to make sure I use them as well. This will be trickly
since I don't know if I'll have 0, 1, 2, or 3 of them until runtime. I'll use an
application context to solve that problem.
Here is a sample implementation:
ops$tkyte@ORA8I.WORLD> create or replace context MY_CTX using MY_PROCEDURE
2 /
Context created.
That created our application context and bound it to our yet to be created procedure
"MY_PROCEDURE". Note that only MY_PROCEDURE will be able to set values in this context.
See
http://asktom.oracle.com/~tkyte/article2/index.html
for more info on application contexts and their use
ops$tkyte@ORA8I.WORLD> create or replace
procedure p ( p_str in varchar2 )
2 is
3 l_str long := p_str || chr(10);
4 l_piece long;
5 n number;
6 begin
7 loop
8 exit when l_str is null;
9 n := instr( l_str, chr(10) );
10 l_piece := substr( l_str, 1, n-1 );
11 l_str := substr( l_str, n+1 );
12 loop
13 exit when l_piece is null;
14 dbms_output.put_line( substr( l_piece, 1,
250 ) );
15 l_piece := substr( l_piece, 251 );
16 end loop;
17 end loop;
18 end;
19 /
Procedure created.
P is just a little procedure I use to print things out nicer then dbms_output would. I
use it below to dump the dynamically generated query so we can see what was built for
each execution. It is not really relevant to the example, just part of the
demonstration...
ops$tkyte@ORA8I.WORLD> create or replace
2 procedure my_procedure( p_ename in varchar2 default NULL,
3 p_hiredate in date default NULL,
4 p_sal in number default NULL)
5 as
6 type rc is REF CURSOR;
7
8 l_cursor rc;
9 l_query varchar2(512)
10 default 'select * from emp where 1 = 1 ';
11
12 cursor l_template is select * from emp;
13 l_rec l_template%rowtype;
14
Here I use what I call a "TEMPLATE" cursor. I like to use these with my ref cursors.
I use them to define a record to fetch into. Here, in this simple example, I could have
skipped it and just defined l_rec as EMP%rowtype -- but I wanted to show how this would
work if you didn't select * from a single table but had many columns from many tables.
This just helps me create a nice record. The template query ONLY has a SELECT and a
FROM. I never put a WHERE clause on it (even when joining) since I never use it any
where. I just use it to get the default datatypes, names and so on for a record
definition right below it.
15 begin
16
17 if ( p_ename is NOT NULL ) then
18 dbms_session.set_context( 'MY_CTX', 'ENAME',
19 '%'||upper(p_ename)||'%');
20 l_query := l_query ||
21 ' and ename like
22 sys_context( ''MY_CTX'', ''ENAME'' ) ';
23 end if;
24
for each input -- i'm inspecting it to see if it is non-null. If it is, I add to the
where clause and set the value in the context. Notice how in the where clause -- I
always use the SYS_CONTEXT function. I NEVER put the literal value in to the query (that
would be very bad and would trash the shared pool -- very extremely important to use bind
variables). Note also the use of '' to get a single ' into the where clause!
25 if ( p_hiredate is NOT NULL ) then
26 dbms_session.set_context( 'MY_CTX', 'HIREDATE',
27 to_char(p_hiredate,'yyyymmddhh24miss'));
28 l_query := l_query ||
29 ' and hiredate >
30 to_date(
31 sys_context( ''MY_CTX'',
32 ''HIREDATE'' ),
33 ''yyyymmddhh24miss'') ';
34 end if;
Note here how I am careful to preserve the date and time component -- if necessary!
Always wrap the sys_context in a TO_DATE call if you are comparing to a DATE to avoid
implicit conversions in the query at runtime!
35
36 if ( p_sal is NOT NULL ) then
37 dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
38 l_query := l_query ||
39 ' and sal >
40 to_number(
41 sys_context( ''MY_CTX'',
42 ''SAL'' )
43 ) ';
44 end if;
45
Same caveat for the NUMBER here. Use TO_NUMBER to avoid IMPLICIT conversions
46 p( l_query );
47
48 open l_cursor for l_query;
49
50 loop
51 fetch l_cursor into l_rec;
52 exit when l_cursor%notfound;
53
54 dbms_output.put_line( l_rec.ename || ',' ||
55 l_rec.hiredate || ',' ||
56 l_rec.sal );
57 end loop;
58
59 close l_cursor;
60 end;
61 /
Procedure created.
and that is it. I now have a routine that will open 1 of 8 possible different
cursors. Here is a small test run just to see how it works
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> exec my_procedure
select * from emp where 1 = 1
SMITH,17-dec-1980 00:00:00,800
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
KING,,5
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a' )
select * from emp where 1 = 1 and ename like
sys_context( 'MY_CTX', 'ENAME' )
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_sal => 1000 )
select * from emp where 1 = 1 and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_hiredate => add_months(sysdate,-240) )
select * from emp where 1 = 1 and hiredate >
to_date(
sys_context( 'MY_CTX',
'HIREDATE' ),
'yyyymmddhh24miss')
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a', p_sal => 2000 )
select * from emp where 1 = 1 and ename like
sys_context( 'MY_CTX', 'ENAME' ) and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD>
See this link for a superior approach:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908
Sounds like a good use of REF CURSORS to me. Lets say you wanted to build a generic
routine that would look at the inputs passed to it and build a WHERE clause for each
NON-NULL parameter passed. This would result in a large number of statically defined
cursors so we'll use a ref cursor instead.
I'll demonstrate below. I'll write a write that will print out some EMP data. This
routine will take upto 3 inputs to constrain the result set. I want to have upto 8
different cursors possible here
o 1 with NO where clause (all inputs null)
o 3 with a single predicate
o 3 with "pairs" of predicate conditions
o 1 with all three predicate conditions
Additionally, since the use of BIND VARIABLES is one of the MOST important things in
programming Oracle -- I'll want to make sure I use them as well. This will be trickly
since I don't know if I'll have 0, 1, 2, or 3 of them until runtime. I'll use an
application context to solve that problem.
Here is a sample implementation:
ops$tkyte@ORA8I.WORLD> create or replace context MY_CTX using MY_PROCEDURE
2 /
Context created.
That created our application context and bound it to our yet to be created procedure
"MY_PROCEDURE". Note that only MY_PROCEDURE will be able to set values in this context.
See
http://asktom.oracle.com/~tkyte/article2/index.html
for more info on application contexts and their use
ops$tkyte@ORA8I.WORLD> create or replace
procedure p ( p_str in varchar2 )
2 is
3 l_str long := p_str || chr(10);
4 l_piece long;
5 n number;
6 begin
7 loop
8 exit when l_str is null;
9 n := instr( l_str, chr(10) );
10 l_piece := substr( l_str, 1, n-1 );
11 l_str := substr( l_str, n+1 );
12 loop
13 exit when l_piece is null;
14 dbms_output.put_line( substr( l_piece, 1,
250 ) );
15 l_piece := substr( l_piece, 251 );
16 end loop;
17 end loop;
18 end;
19 /
Procedure created.
P is just a little procedure I use to print things out nicer then dbms_output would. I
use it below to dump the dynamically generated query so we can see what was built for
each execution. It is not really relevant to the example, just part of the
demonstration...
ops$tkyte@ORA8I.WORLD> create or replace
2 procedure my_procedure( p_ename in varchar2 default NULL,
3 p_hiredate in date default NULL,
4 p_sal in number default NULL)
5 as
6 type rc is REF CURSOR;
7
8 l_cursor rc;
9 l_query varchar2(512)
10 default 'select * from emp where 1 = 1 ';
11
12 cursor l_template is select * from emp;
13 l_rec l_template%rowtype;
14
Here I use what I call a "TEMPLATE" cursor. I like to use these with my ref cursors.
I use them to define a record to fetch into. Here, in this simple example, I could have
skipped it and just defined l_rec as EMP%rowtype -- but I wanted to show how this would
work if you didn't select * from a single table but had many columns from many tables.
This just helps me create a nice record. The template query ONLY has a SELECT and a
FROM. I never put a WHERE clause on it (even when joining) since I never use it any
where. I just use it to get the default datatypes, names and so on for a record
definition right below it.
15 begin
16
17 if ( p_ename is NOT NULL ) then
18 dbms_session.set_context( 'MY_CTX', 'ENAME',
19 '%'||upper(p_ename)||'%');
20 l_query := l_query ||
21 ' and ename like
22 sys_context( ''MY_CTX'', ''ENAME'' ) ';
23 end if;
24
for each input -- i'm inspecting it to see if it is non-null. If it is, I add to the
where clause and set the value in the context. Notice how in the where clause -- I
always use the SYS_CONTEXT function. I NEVER put the literal value in to the query (that
would be very bad and would trash the shared pool -- very extremely important to use bind
variables). Note also the use of '' to get a single ' into the where clause!
25 if ( p_hiredate is NOT NULL ) then
26 dbms_session.set_context( 'MY_CTX', 'HIREDATE',
27 to_char(p_hiredate,'yyyymmddhh24miss'));
28 l_query := l_query ||
29 ' and hiredate >
30 to_date(
31 sys_context( ''MY_CTX'',
32 ''HIREDATE'' ),
33 ''yyyymmddhh24miss'') ';
34 end if;
Note here how I am careful to preserve the date and time component -- if necessary!
Always wrap the sys_context in a TO_DATE call if you are comparing to a DATE to avoid
implicit conversions in the query at runtime!
35
36 if ( p_sal is NOT NULL ) then
37 dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
38 l_query := l_query ||
39 ' and sal >
40 to_number(
41 sys_context( ''MY_CTX'',
42 ''SAL'' )
43 ) ';
44 end if;
45
Same caveat for the NUMBER here. Use TO_NUMBER to avoid IMPLICIT conversions
46 p( l_query );
47
48 open l_cursor for l_query;
49
50 loop
51 fetch l_cursor into l_rec;
52 exit when l_cursor%notfound;
53
54 dbms_output.put_line( l_rec.ename || ',' ||
55 l_rec.hiredate || ',' ||
56 l_rec.sal );
57 end loop;
58
59 close l_cursor;
60 end;
61 /
Procedure created.
and that is it. I now have a routine that will open 1 of 8 possible different
cursors. Here is a small test run just to see how it works
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> exec my_procedure
select * from emp where 1 = 1
SMITH,17-dec-1980 00:00:00,800
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
KING,,5
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a' )
select * from emp where 1 = 1 and ename like
sys_context( 'MY_CTX', 'ENAME' )
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_sal => 1000 )
select * from emp where 1 = 1 and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_hiredate => add_months(sysdate,-240) )
select * from emp where 1 = 1 and hiredate >
to_date(
sys_context( 'MY_CTX',
'HIREDATE' ),
'yyyymmddhh24miss')
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a', p_sal => 2000 )
select * from emp where 1 = 1 and ename like
sys_context( 'MY_CTX', 'ENAME' ) and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD>
Difference Between Full Index Scans and Fast Full Index Scans
A fast full index scan reads the entire index, unsorted, as it exists on disk. It is basically using the index as a “skinny” version of the table. The query in question would be accessing only attributes in the index. (We are not using the index as a way to get to the table—we are using the index instead of the table.) We use multiblock I/O and read all the leaf, branch, and root blocks. We ignore the branch and root blocks when executing the query and just process the (unordered) data in the leaf blocks.
A full index scan reads the index a block at a time, from start to finish. It reads the root block, navigates down the left-hand side of the index (or the right-hand side for a descending full scan), and then when it hits the leaf block, it reads across the entire bottom of the index—a block at a time—in sorted order. It uses single-block, not multiblock, I/O for this operation.
Reference from "Ask Tom"
A full index scan reads the index a block at a time, from start to finish. It reads the root block, navigates down the left-hand side of the index (or the right-hand side for a descending full scan), and then when it hits the leaf block, it reads across the entire bottom of the index—a block at a time—in sorted order. It uses single-block, not multiblock, I/O for this operation.
Reference from "Ask Tom"
Subscribe to:
Posts (Atom)