Sitemap

Amicuk Programming Answers

Restricting on a cursor result

-0001-11-30   Views:0

Advertisement

Task: 1. I would like to make reports for a system. 2. I want to make the reports with an out sys_refcursor 3. I want to pass in some cursor with ids, to restrict report. So here's the code I got so far: procedure customer_status_distribution(c_unive

Task:
1. I would like to make reports for a system.
2. I want to make the reports with an out sys_refcursor
3. I want to pass in some cursor with ids, to restrict report.
So here's the code I got so far:
procedure customer_status_distribution(c_universe sys_refcursor,
t_result out sys_refcursor) is
v_table_of_number table_of_number;
begin
fetch c_universe bulk collect
into v_table_of_number;
open t_result for with a as(
select column_value column_value
from table(cast(v_table_of_number as table_of_number)))
select t.alder, count(*)
from customer_aggregation t
where customer_id in(select column_value from a)
group by t.alder;
end;
c_universe is containing just numbers of course.
The problem with this is that it doesn't use the index on customer_aggregation.customer_id column.
I can add /*+RULE*/ and it uses the index, but I do not want that, since the actual reports are quite more complex, and will use different technuiqes for optimizing.
exists() clause does not use index.
/*+ index(customer_aggregation name_of_index)*/ doesn't do it.
Am I going about this the wrong way? Any ideas?

The replay answer
Advertisement
Following code:
fetch c_universe bulk collect
into v_table_of_number;
open t_result for
select t.alder, count(*)
from customer_aggregation t
where exists(select * from table(cast(v_table_of_number as table_of_number)) where column_value = t.customer_id)
group by t.alder;
gives:
SELECT STATEMENT      12176732          
SORT GROUP BY     12176732     1     9
NESTED LOOPS SEMI     12176729     1     9
TABLE ACCESS FULL -> CUSTOMER_AGGREGATION     1457     1521909     13697181
COLLECTION ITERATOR PICKLER FETCH          
Rewrite using in:
fetch c_universe bulk collect
into v_table_of_number;
open t_result for
select t.alder, count(*)
from customer_aggregation t
where t.customer_id in(select column_value from table(cast(v_table_of_number as table_of_number)))
group by t.alder;
gives:
SELECT STATEMENT      12176732          
SORT GROUP BY     12176732     1     9
NESTED LOOPS SEMI     12176729     1     9
TABLE ACCESS FULL -> CUSTOMER_AGGREGATION     1457     1521909     13697181
COLLECTION ITERATOR PICKLER FETCH               
Rewrite to use hints and "in":
fetch c_universe bulk collect
into v_table_of_number;
open t_result for
select /*+index(t PK_AGGR_CUSTOMER_ID)*/ t.alder, count(*)
from customer_aggregation t
where t.customer_id in(select column_value from table(cast(v_table_of_number as table_of_number)))
group by t.alder;
end;
gives:
SELECT STATEMENT      12211234          
SORT GROUP BY     12211234     1     9
NESTED LOOPS SEMI     12211231     1     9
TABLE ACCESS BY INDEX ROWID -> CUSTOMER_AGGREGATION     35959     1521909     13697181
INDEX FULL SCAN -> PK_AGGR_CUSTOMER_ID     11354     1521909     
COLLECTION ITERATOR PICKLER FETCH               
Rewrite using index hint and "exists":
fetch c_universe bulk collect
into v_table_of_number;
open t_result for
select /*+index(t PK_AGGR_CUSTOMER_ID)*/ t.alder, count(*)
from customer_aggregation t
where exists(select column_value from table(cast(v_table_of_number as table_of_number)) where column_value = t.customer_id)
group by t.alder;
gives:
SELECT STATEMENT      12211234          
SORT GROUP BY     12211234     1     9
NESTED LOOPS SEMI     12211231     1     9
TABLE ACCESS BY INDEX ROWID -> CUSTOMER_AGGREGATION     35959     1521909     13697181
INDEX FULL SCAN -> PK_AGGR_CUSTOMER_ID     11354     1521909     
COLLECTION ITERATOR PICKLER FETCH               
Finally, what I want is (explain plan wise):
the code posted using a tmp_numbers table:
which gives:
SELECT STATEMENT      10          
SORT GROUP BY     10     1     22
TABLE ACCESS BY INDEX ROWID -> CUSTOMER_AGGREGATION     3     1     9
NESTED LOOPS      8     1     22
SORT UNIQUE               
TABLE ACCESS FULL -> TMP_NUMBERS     2     1     13
INDEX RANGE SCAN -> PK_AGGR_CUSTOMER_ID (1)     2     1     
Suggestions?

Go to See the other 9 answers

Restricting on a cursor result

Category:DefaultRelease time:-0001-11-30Views:130

Task: 1. I would like to make reports for a system. 2. I want to make the reports with an out sys_refcursor 3. I want to pass in some cursor with ids, to restrict report. So here's the code I got so far: procedure customer_status_distribution(c_unive[More]

Stored procedure that returns a cursor (result set)

Category:DefaultRelease time:2015-10-11Views:130

Hi, We have a stored procedure that returns a cursor (result set) but when I compliled it and catalouged (introspected) it in the OBPM I got all the primitive type parameters (either IN or OUT) in the proc call except the cursor type (the result set)[More]

EA2 : Cursor result support?

Category:DefaultRelease time:-0001-11-30Views:130

I have been eagerly anticipating the release of 1.5 in the hope that cursor results would have support. I have been scouring the forums and have found mention of them but nowhere is there a definitive explanation of how to view the cursor results. Pl[More]

Cursor result count

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I've a staging table with one column result_id :100,101,102 etc I need to query another table say stage2 which consists of many columns including result_id so If I found result id in stage 2 then I want to update that table flag enable_flag=y and[More]

Running queries against cursor results?

Category:DefaultRelease time:-0001-11-30Views:130

I'm running a cursor in a stored procedure, and the query itself is dependent on one of the parameters passed to the SP. So, for example: create procedure annoying as SQLString varchar2(50); myCursor Types.cursorType; -- defined elsewhere in pkg. beg[More]

Restricting views of search results in E-recruiting

Category:DefaultRelease time:-0001-11-30Views:130

Hello Experts, I have a requirement in my project to restrict the visibility of search results when users run search queries to look for applications. The system should be masking the applications of the user's reporting managers, directors or VPs wh[More]

Is it possible to ref cursor(result set) as in parameter to procedure/funct

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am getting a resultset/ref cursor from the Java side to the procedure/function as in parameter. Is this possible in oracle 10g. If yes can body send the links/suggestions describing some examples. Thanks,I am getting a resultset/ref cursor from[More]

HT201304 Turning restrictions on then off resulting in duplicate TV shows

Category:DefaultRelease time:-0001-11-30Views:130

I search the forums, but didn't see a solution to this issue, though there were other posts along this line: On my iPad, 3rd generation, with iOS 7.1 installed (and even before, with 7.0.6)... When I have videos downloaded to the Videos app (such as[More]

Procedure output cursor result insert to new table

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, Is there has any method to complete following task, CREATE OR REPLACE PROCEDURE sp1( cv_1 OUT sys_refcursor) AS BEGIN OPEN cv_1 FOR SELECT OBJECT_NAME FROM USER_OBJECTS; END; CREATE TABLE TEST1(OBJECT_NAME VARCHAR2(100)); var cv_1 refcursor;[More]

Who's Who - Restrict Result List

Category:DefaultRelease time:2015-10-11Views:130

Dear, My problem today is that my Who's Who displays all the positions from the backend in the result list. For example, if Mr X was in entity A til end of Dec07 and then he changed position and was in entity B. Then Who's who displayed all the resul[More]

How to hold result of query with too many characters into a cursor?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, guys: Could anyone help me on this issue? I encounter such a error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small. The reason is because the returning result is too big to hold in a cursor. I know I should use clob,[More]

Hot
After a few minutes of running, Team Fortress 2 (and this game only) will crash and close. I've read similar questions in other threads, but I lack the expertise to be able to parse my error report and find the problem. Can anyone help me zero in on [More]
hi all according to SAP Help, the setting for checking manufacturer plants are supposed to be the following: 1) Vendor with account group001 and partner roles LF 2) Manufacturer with account group MNFR which, unless I am missing something,  allows pa [More]
Microsoft wants us to use our Microsoft Account in Windows 8.1 and to download apps from the store.  99% of my Windows Apps do nothing. The apps open and then minimize. The window can be seen in the upper left of the screen but nothing ever happens. [More]
I just finished downloading and attempting the install of ios 7 on my iPhone 5.  I'm using the newest version of iTunes.  And now, I'm stuck at the screen with the iTunes logo and the plug.  But no matter how many times I try, recovery mode will not [More]
So I bought a new stereo for my car and it has a built in controls for an iPod and a dedicated wire for my iPod to directly connect to the stereo. I placed this wire in the glove box. Now the stereo was installed fine, everything works except for my [More]
My XP machine crashed and I was forced to get a Vista machine. Vista 64 bit 7gb RAM and 640GB HD. I downloaded the latest ACR 4.6 and that allowed me to see and edit NEF files in PS but in CS3 Bridge I cannot see the thumbnails only the NEF icon. I c [More]
I tried following the instructions by attempting to use Option 2, BUT there is NO WEb Services option in the SETUP menu. How do I go about getting the Claim Code OR get the Web. Service Information that has the information that I need to register my [More]
I have an AppleTV 2 connected via Ethernet to my BT Home Hub router and HDMI to my TV. My OSX (Lion), iTunes and AppleTV software are all up to date. Everything works fine apart from renting movies. When I rent a movie, it shows I can play it rather [More]
Since I have installed Mavericks the videos that I have in iphoto no longer automatically open with a click in quicktime. I cant seem to get them to play at all. I still have iphoto 08? Any ideas?QuickTime Player 7 for Mac OS X v10.6.3 or later OTRea [More]
and i fiddled with terminal (following instructions from the internet)  to fix it and probably made it worse. how do i fix this? when i try to open the page it says Invalid URL The requested URL "/", is invalid. Reference #9.54e0fc7d.1343822724. [More]