0

I have oracle proc on which I am passing data to filter the records. The data passed is election_id and this election_id can be anywhere from 10 to 20000 at a time. The varchar datatype is not sufficient so management have decided to go for Global temp table. The DBA has asked for a script to create a GTT, but the issue is I do not know what should I place in GTT. Based on my proc, do I put whole table in GTT or some specific column or do I place the election_id. Also the election_id is used in other procs as well so do I create multiple GTT ? I am kind a lost right now please help me to tackel this issue. Help will be highly appreciated.

create or replace PROCEDURE County_election(
    election_id IN varchar2, //this data is too large so we creating GTT
    date_occured  IN Date,
    state IN varchar2,
    country varchar2
)

with election_id_list as(
parsing comma separated election_id  and selecting from dual
)

regular query
SELECT s.state, s.country, t.mayor, m.name, COUNT (DISTINCT s.seat) seat_won
FROM COUNTY s
    JOIN  Area t ON t.e_id = s.e_id
JOIN Memebers m on m.mem_id=t.mem_id and mem_id IN (sub query)
where s.election_id in (select  election_id from election_id_list ) //large data is used in where clause to filter the data
sticky bit
  • 36,626
  • 12
  • 31
  • 42
user13079741
  • 122
  • 1
  • 10
  • I think that the problem is that you don't specify the long of varchar2, it can takes 4000 characters. imagine a number with 4000 digits. you can assign the size with a parenthesis and the number inside like this varchar2(100) – Emiliano Mar 18 '20 at 03:43

1 Answers1

1

I would suggest keeping election_id column in GTT and use it in join in main table. 

CREATE GLOBAL TEMPORARY TABLE election_id_list (
   election_id  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO election_id_list <parsing comma separated election_id  and selecting from dual>


SELECT s.state, s.country, t.mayor, m.name, COUNT (DISTINCT s.seat) seat_won
FROM COUNTY s
    JOIN  Area t ON t.e_id = s.e_id
JOIN Memebers m on m.mem_id=t.mem_id and mem_id IN (sub query)
JOIN election_id_list 
on s.election_id =election_id_list.election_id
Digvijay S
  • 2,665
  • 1
  • 9
  • 21
  • I am confused the proc has election_id as varchar2 but if large data is passed then varchar2 will not be able to store those data and it will throw ora-01460 error. So how this GTT will optimize my query or where this GTT will come into play on handling large data? – user13079741 Mar 18 '20 at 02:57
  • Instead of storing data in large string in a single column you are storing in rows. `1 2 3 4 5` vs `1` `2` `3` – Digvijay S Mar 18 '20 at 03:08
  • I got that part but what I was trying to understand is in proc : create or replace proc (election_id IN Varchar2) I have Varchar2 and from java I am passing comma separated strings. I Thought proc will not continue further and will terminate cuz the Varchar2 4000 bytes will not be Handel large data. Isn’t this the scenario or am I missing something ? Also do I need multiple GTT since I have multiple election_id in different prices . And thanks for your great effort , you have made my life lot easier – user13079741 Mar 18 '20 at 03:24
  • You need to pass the string as `CLOB` – Digvijay S Mar 18 '20 at 03:30
  • If I pass the Clob then GTT column needs to be of same type right ? And I believe Clob cannot be used in where clause – user13079741 Mar 18 '20 at 03:33
  • You have to split clob based on delimiter. Check this thread https://stackoverflow.com/questions/38395183/how-to-split-a-clob-object-using-and-delimiter-in-oracle-into-multiple-recor – Digvijay S Mar 18 '20 at 03:46
  • ok I will try this and will let you know. If all good I will mark your answer as correct. I am very much thankful for such a great effort , thanks again. You have a blessed day – user13079741 Mar 18 '20 at 03:51