SQL> set timing off;
SQL> def cnt = 10000000;
SQL>
SQL> create or replace package detest as
2 function test1(p in varchar2) return varchar2 DETERMINISTIC;
3 function test2(p in varchar2) return varchar2 DETERMINISTIC;
4 end;
5 /
Package created.
SQL>
SQL> create or replace package body detest as
2 vCache varchar2( 2000 );
3
4 function test1(p in varchar2) return varchar2
5 as
6 begin
7 if p = 's' then
8 return 't';
9 else
10 return null;
11 end if;
12 end;
13
14 function test2(p in varchar2) return varchar2
15 as
16 begin
17 if p = 's' then
18 if vCache is null then
19 select 't' into vCache from ALL_OBJECTS where rownum < 2;
20 end if;
21 return vCache;
22 else
23 return null;
24 end if;
25 end;
26
27 end;
28 /
Package body created.
SQL>
SQL> set timing on;
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test1('z');
7 vDummy := detest.test1('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.25
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test2('z');
7 vDummy := detest.test2('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
SQL>
SQL> set timing off;
SQL>
SQL> create or replace package detest as
2 function test1(p in varchar2) return varchar2;
3 function test2(p in varchar2) return varchar2;
4 end;
5 /
Package created.
SQL>
SQL> set timing on;
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test1('z');
7 vDummy := detest.test1('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.77
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test2('z');
7 vDummy := detest.test2('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.46
SQL> spool off
четверг, 14 апреля 2016 г.
Work test of DETERMINISTIC function
пятница, 18 марта 2016 г.
How to generate hierarchy query to string
select listagg(txt) within group (order by rn)
from (
select
isn, parentisn, rn,
shortname ||
decode(nxtlvl, lvl + 1, '(', '') ||
decode(nxtlvl, lvl, ',', '') ||
decode(nxtlvl, lvl + 1, '', lvl, '', ltrim(lpad(')', lvl - nxtlvl, ')'))) txt
from (
select t.*, lead(lvl) over(order by rn) nxtlvl
from (
select isn, parentisn, shortname, rownum rn, level lvl
from dicti d
start with isn = 8167
connect by prior isn = parentisn
order siblings by shortname) t))
пятница, 19 февраля 2016 г.
Out SYS_REFCURSOR into console
create or replace PROCEDURE grachevcur(
p_refcursor IN OUT SYS_REFCURSOR
)
AS
l_desc DBMS_SQL.DESC_TAB ;
l_cols BINARY_INTEGER ;
l_cursor BINARY_INTEGER ;
v_varchar2 VARCHAR2( 4000 ) ;
v_number NUMBER ;
v_date DATE ;
l_data varchar2( 32767 ) ;
l_columnValue VARCHAR2( 32767 ) ;
l_processedRows Number := 0;
BEGIN
/* Convert refcursor "parameter" to DBMS_SQL cursor... */
l_cursor := DBMS_SQL.TO_CURSOR_NUMBER( p_refcursor );
/* Describe the cursor... */
DBMS_SQL.DESCRIBE_COLUMNS( l_cursor, l_cols, l_desc );
/* Define columns to be fetched. We're only using V2, NUM, DATE for example...
for a complete list of the col_types this link is accessible.
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504
http://forums.oracle.com/forums/thread.jspa?threadID=912475
if not a usable type, will throw new exception
*/
FOR i IN 1 .. l_cols LOOP
IF l_desc(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_number);
ELSIF l_desc(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_date);
ELSif l_desc(i).col_type = 01 or l_desc(i).col_type = 96 then
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_varchar2, 4000);
else
--raise an exception if the user's query contains a datatype not (yet) supported by this procedure
RAISE_APPLICATION_ERROR(-20000, 'Invalid Data Type for conversion to delimited file. {' || l_desc(i).col_name || '}');
END IF;
END LOOP;
/* -- print out the column names if desired
FOR i IN 1 .. l_cols LOOP
dbms_output.put_line('** ' || l_desc(i).col_name) ;
END LOOP;
*/
/* Fetch all data... */
WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
dbms_output.put_line('LINE: ' || l_processedRows || '');
FOR i IN 1 .. l_cols LOOP
if l_desc(i).col_type = 12 THEN --we are in a date
DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_date);
v_varchar2 := to_char(v_date , 'dd-MON-yyyy' ) ;
elsif l_desc(i).col_type = 2 THEN --we are in a number
DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_number);
v_varchar2 := to_char(v_number) ;
else --treat it as a string (should be varchar2,char,etc)
DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_varchar2);
IF v_varchar2 IS NOT NULL THEN
v_varchar2 := '"' || v_varchar2 || '"' ;
ELSE
v_varchar2 := '';
END IF ;
end if ;
dbms_output.put_line(' ' ||l_desc(i).col_name || '=>' || v_varchar2) ;
END LOOP;
l_processedRows := l_processedRows + 1 ;
END LOOP;
dbms_sql.close_cursor(l_cursor);
dbms_output.put_line('I found and processed ' || l_processedRows || ' rows .');
END;
/
среда, 28 октября 2015 г.
пятница, 31 июля 2015 г.
How to disable and enable depended constraints
select --'truncate table '|| TABLE_NAME || ';' 'alter table ' || TABLE_NAME || ' enable constraint ' || CONSTRAINT_NAME || ';' from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and STATUS <> 'ENABLED' --and R_CONSTRAINT_NAME = 'C_MEDHISTCONS_PK' order by TABLE_NAME select 'alter table ' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ';' from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and R_CONSTRAINT_NAME = 'C_DCSPECS_PK'
вторник, 28 июля 2015 г.
How to create indexes by foreign key
select 'create index I_' || substr(C.CONSTRAINT_NAME, 3) || ' on ' || C.TABLE_NAME || '(' ||
(select rtrim(xmlagg(xmlelement(e, CC.COLUMN_NAME,',').extract('//text()') order by CC.COLUMN_NAME).GetClobVal(),',')
from ALL_CONS_COLUMNS CC
where CC.OWNER = C.OWNER
and CC.TABLE_NAME = C.TABLE_NAME
and CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME) || ');'
from (
select distinct C.OWNER, C.TABLE_NAME, C.CONSTRAINT_NAME
from
ALL_CONSTRAINTS C,
ALL_CONS_COLUMNS CC
where C.CONSTRAINT_TYPE = 'R'
and C.OWNER = 'PARUS'
and CC.OWNER = C.OWNER
and CC.TABLE_NAME = C.TABLE_NAME
and CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and not CC.COLUMN_NAME in ('COMPANY', 'VERSION')
and CC.CONSTRAINT_NAME like 'C\_%' escape '\'
and not exists (
select *
from ALL_IND_COLUMNS IC
where IC.TABLE_OWNER = C.OWNER
and IC.TABLE_NAME = CC.TABLE_NAME
and IC.COLUMN_NAME = CC.COLUMN_NAME)) C
пятница, 10 июля 2015 г.
How to reverse string
select listagg(C) within group(order by RN desc) rvs from ( select substr(T.COL, rownum, 1) C, rownum RN from (select 'Привет' COL from DUAL) T connect by level <= length(T.COL))
Подписаться на:
Сообщения (Atom)