Mega Code Archive
Possible speed benefits of NOCOPY
<![CDATA[
SQL>
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4),
8 emp1 NUMBER,
9 emp2 NUMBER,
10 emp3 NUMBER
11 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ('1', 'Database', 'Oracle', 563,.99,99, 1, 2, 3);
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ('2', 'Database', 'MySQL', 765,.99,99, 4, 5);
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ('3', 'Database', 'SQL Server',4,.99,01, 6, 7, 8);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE CopyFast AS
2 TYPE BookArray IS TABLE OF book%ROWTYPE;
3
4 PROCEDURE Passbook1(p IN BookArray);
5 PROCEDURE Passbook2(p IN OUT BookArray);
6 PROCEDURE Passbook3(p IN OUT NOCOPY BookArray);
7
8 PROCEDURE Go;
9 END CopyFast;
10 /
Package created.
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CopyFast AS
2 PROCEDURE Passbook1(p IN BookArray) IS
3 BEGIN
4 NULL;
5 END Passbook1;
6
7 PROCEDURE Passbook2(p IN OUT BookArray) IS
8 BEGIN
9 NULL;
10 END Passbook2;
11
12 PROCEDURE Passbook3(p IN OUT NOCOPY BookArray) IS
13 BEGIN
14 NULL;
15 END Passbook3;
16
17 PROCEDURE Go IS
18 v_BookArray BookArray := BookArray(NULL);
19 v_Time1 NUMBER;
20 v_Time2 NUMBER;
21
22 BEGIN
23
24 SELECT * INTO v_BookArray(1) FROM book WHERE ISBN = '3';
25 v_BookArray.EXTEND(50000, 1);
26
27 v_Time1 := DBMS_UTILITY.GET_TIME;
28 Passbook1(v_BookArray);
29 v_Time2 := DBMS_UTILITY.GET_TIME;
30 Passbook2(v_BookArray);
31
32
33 DBMS_OUTPUT.PUT_LINE('Time to pass IN: ' || TO_CHAR((v_Time2 - v_Time1) /0));
34 END Go;
35 END CopyFast;
36 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> BEGIN
2 CopyFast.Go();
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "RNTSOFT.COPYFAST", line 33
ORA-06512: at line 2
SQL>
SQL>
SQL>
SQL> drop table book;
Table dropped.
SQL>
SQL>
SQL>
]]>