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
I know that this is kind of a stupid question but I am new to dual graphics cards and this is the first mac that I have ever had, so I don't understand how you use the 2nd graphics card. When I click on the apple logo and go to about it shows the Int [More]
I am using Iphone 4 (not 4s) After upgrading to ios 5.0.1. is blocking. I live in VenezuelaWithout more information, I have to say it sounds like the phone you have was not legitimately unlocked, but had been hacked. Updating iOS will re-lock it to t [More]
Do I have to buy a monthy subscription?You do not have to buy a subscription. From the Edge Animate FAQ at http://html.adobe.com/edge/animate/faq.html under Pricing & Availability: How much is Edge Animate and how do I get it? As a limited-time intro [More]
Hi Experts Please let me know how Can I Print the A/R Invoice in regard 4 copies [crystal reports] For Example, 1 [first] COPY ORIGINAL FOR BUYER 2 [secand] COPY DUPLICATE FOR TRANSPOTER 3 [thired] COPY TRIPLICATE FOR ASSESSE 4 [fourth] COPY EXTRA FO [More]
Greetings, i have a Object View af:Table in JDeveloper 11.1.2.4.0 JSF form that includes 3 new columns that have nothing to do with the database xor the Entity Object. I bind this 3 columns to a LOV each. The 2 LOVs are normal View Objects, but the 3 [More]
So I loaded a webpage and Safari shut down.  Here is the error, what caused it in normal speak? Thanks! ~John Process:         com.apple.WebKit.WebContent [10084] Path:            /System/Library/PrivateFrameworks/WebKit2.framework/Versions/A/XPCServ [More]
I can see in iPhoto 6 that the new iPhoto Library folder structure is now based on YEAR>ROLL#, in place of YEAR>MONTH>DAY. After upgrading from iPhoto 5 to 6, I see the new ORIGINALS and MODIFIED folders with YEAR>ROLLS within, but what of the [More]
Hi Experts, I am working in JDEV 11.1.1.3.0. In my model project i have configured 2AM with 2 different data base connections, both are working fine. Now in the 2nd AM i am trying to execute VO and getting the value, but the query is not executing, i [More]
My company uses Office 365 and Skype for Business.  We have several Groups defined in Exchange.  We can add those groups to Skype, but when the groups are updated as a result of turnover, those changes we do in the Exchange group do not flow down to [More]
Is there a way I can reinstall it? Here's the error report I'm getting: Process:               QuickTime Player [74326] Path:                  /Applications/Utilities/QuickTime Player.app/Contents/MacOS/QuickTime Player Identifier:            com.app [More]