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 have a good signal to the router and iPad works well. The PC will not open any browser (Firefox, explorer internet), and Thunderbird opens but will not connect and times out. So I cannot reinstall Firefox or Thunderbird. I can get messages on my iP [More]
I have multiple phones and iPads and all are linked to one iCloud account and one iTunes Account.  I want to keep the same iTunes Account so I can share music, etc., however, I want each device to have its own iCloud account.  Any help on how I can I [More]
I only have this issue with FaceTime on my MacBook Pro; FaceTime works fine on my iPad Mini and iPhone 5S. I'll be talking with someone over FaceTime and it will randomly freeze and/or say it's lost the connection and I usually have to turn my Wifi o [More]
how do i check history in safari after its been cleared? terminal?There is one way you might possibly find recent history sites that are no longer on the History list, but this trick is iffy. If you can remember how the web address starts, type it in [More]
Need some guidance. We have two DB. DB_A and DB_B. Once in every 5 months we clone the DB_A to DB_B after taking a cold backup of DB_A. DB_A is our source database and DB_B is out clone or target database. both the DB are in IBM AIX servers. The prob [More]
I am using my husband's old iPod touch and when I try to connect it to iTunes I get that error message.  It is recognized in my computer as unspecified under Devices and PrintersThanks.  I had searched for that error message but had the whole number [More]
I have a JVC GR-D72U DV recorder. I have been able to import video from this camcorder to my old Pismo Powerbook running iMovie v.3.0.3 and OS 10.4.11 for several years using Firewire. Now I have a 2012 iMac running iMovie 11 v.9.0.4 and OS 10.7.5 an [More]
Mac 10.8.5, Acrobat 10.1.1. About every 5th time I launch Acrobat it asks for a serial number. Why, how to fix?It was asking for a serial number after less than three months of use.  It gave the option to input a new serial number or start a 30 day f [More]
My contacts won´t sync to the iphone. In itunes there is not even a check I can make to the contacts for syncing it says they should sync over icloud, but they won´tAre you syncing your Mac Address Book contacts over the air with your iCloud account [More]
I am getting "ORA-04068 Existing state of Packages has been discarded" when trying to run a package from a form. Is it because of having Schema name and Package name as same?.Hi Claus, First of all - it's possible to make DM package status as Fa [More]