On Sep 11, 11:13 am, Santana <[email protected]> wrote:
> Hello,
> i need your help for this situation :
>
> I have a table :"CUSTOMER" with two thousand of records, the required
> time to retrieve this information with a query is less than 2
> seconds :
> SELECT * FROM CUSTOMER
>
> But i need get this information through a function that require more
> than 2 MINUTES!!!
>
> FUNCTION getCUSTOMER( ) RETURN SYS_REFCURSOR AS
> CUR SYS_REFCURSOR;
> BEGIN
>
> OPEN cur FOR ' SELECT * FROM CUSTOMER ';
>
> RETURN cur;
> END;
>
> There is any database parameter that i need change ?? Any suggestion,
> please ??
>
> Regards,
> Santana
You have problems you're not telling us about with reference to how
you're using this function; I do not see the same performance
'problem' you see for 2000 records:
SQL> @customer_ref_cur_ex
SQL> create table customer(
2 cust_id number,
3 cust_name varchar2(40),
4 cust_addr1 varchar2(50),
5 cust_addr2 varchar2(50),
6 cust_pst_cd varchar2(10),
7 cust_cntct varchar2(40),
8 cust_email varchar2(80)
9 );
Table created.
Elapsed: 00:00:00.04
SQL>
SQL> alter table customer
2 add constraint customer_pk
3 primary key(cust_id);
Table altered.
Elapsed: 00:00:00.04
SQL>
SQL> begin
2 for i in 1..2000 loop
3 insert into customer
4 values (i, 'Sample'||i, i||' Avenue '||i, 'Suite
'||i,
'A'||i||'B', 'Narweegy Sping '||i, 'narweegy'||i||'@sample'||
i||'.com');
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
SQL>
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 300
SQL>
SQL> select * from customer;
CUST_ID CUST_NAME
CUST_ADDR1
CUST_ADDR2 CUST_PST_C
CUST_CNTCT CUST_EMAIL
---------- ----------------------------------------
--------------------------------------------------
-------------------------------------------------- ----------
----------------------------------------
--------------------------------------------------------------------------------
315 Sample315 315 Avenue
315 Suite
315 A315B Narweegy
Sping
315 [email protected]
316 Sample316 316 Avenue
316 Suite
316 A316B Narweegy
Sping
316 [email protected]
317 Sample317 317 Avenue
317 Suite
317 A317B Narweegy
Sping
317 [email protected]
318 Sample318 318 Avenue
318 Suite
318 A318B Narweegy
Sping
318 [email protected]
319 Sample319 319 Avenue
319 Suite
319 A319B Narweegy
Sping
319 [email protected]
...
2000 rows selected.
Elapsed: 00:00:06.39
SQL>
SQL> create or replace FUNCTION getCUSTOMER
2 RETURN SYS_REFCURSOR AS
3 cur SYS_REFCURSOR;
4 BEGIN
5
6
7 OPEN cur FOR 'SELECT * FROM CUSTOMER';
8
9
10 RETURN cur;
11 END;
12 /
Function created.
Elapsed: 00:00:00.03
SQL>
SQL> show errors
No errors.
SQL>
SQL> variable mycur refcursor
SQL>
SQL> begin
2 select getcustomer
3 into :mycur
4 from dual;
5
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> print mycur
CUST_ID CUST_NAME
CUST_ADDR1
CUST_ADDR2 CUST_PST_C
CUST_CNTCT CUST_EMAIL
---------- ----------------------------------------
--------------------------------------------------
-------------------------------------------------- ----------
----------------------------------------
--------------------------------------------------------------------------------
315 Sample315 315 Avenue
315 Suite
315 A315B Narweegy
Sping
315 [email protected]
316 Sample316 316 Avenue
316 Suite
316 A316B Narweegy
Sping
316 [email protected]
317 Sample317 317 Avenue
317 Suite
317 A317B Narweegy
Sping
317 [email protected]
318 Sample318 318 Avenue
318 Suite
318 A318B Narweegy
Sping
318 [email protected]
319 Sample319 319 Avenue
319 Suite
319
...
2000 rows selected.
Elapsed: 00:00:05.60
SQL>
I see no reason this should take 60 times longer to use the function.
You need to explain how, exactly, you're using this function if you
really want assistance.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
