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
We were hit by lightning, again, and lost my wife's Bondi iMac. Those of you who know them realize that they didn't come with CD burners...so we didn't back up her music purchases. In addition, I recently lost my job. The music I purchased on my work [More]
Because a USB device was drawing too much power from your computer, one or more of your USB devices have been disabled. - This is what shows up sometimes even when no USB is connected. What could be the reason?Try a different USB cord, seen this many [More]
Does anybody know if it's possible that a new account determination was carried out when cancelling a sd-billing document with VF11 transaction? Now, if we cancel a billing document the FI all relevant data is copied from the billing document to be c [More]
I am receiving the following message when I try to open a pdf in indesign....."Adobe indesign may not support the file format, a plug-in that supports the file format may be missing, or the file may be open in another application. I really need to us [More]
Today, when I opened firefox for the first time and every time since then, I have clicked on the firefox icon and several different tabs open. Each one includes information about different things that are "new" to firefox such as WOT, google chr [More]
Hi Folks, I am in the process of trashing files apps etc that I don't need. I found a folder called Sync Services (84 kb on disk). Within that was a folder called Schemas (76 kb on disk). In that was Microsoft Office Notes.syncshema (68 KB on disk). [More]
When I select Image Sequence in QT7 and navigate to the folder with the images, I am no longer able to select more then one image. Is the something that 10.6 has broken? What am I missing?If you only get the first file to import then I suspect the im [More]
Hi, I am developing the concept of FSG reports in BI using FSG backend tables. The concurrent program for FSG Reports 'Financial Statement Generator' is a Spawned program. I don't know what is the functionality of this program and how the calculation [More]
I created a slide show in adobe premiere but when i put it into idvd to burn the menu is in 16:9 but when i play the slide show its in 4:3. how do i make the slide show 16:9 on the dvdTake a look at this tutorial to understand how to encode video for [More]
Someone know the method to implement a psophometric or weighted filter in Labview software Without using Sound and Vibration Toolkit? ThanksThe simplest way to implement a psophometric filter is to use the Sound and Vibration toolkit. Anyway if you d [More]