Home » RDBMS Server » Server Administration » dynamic pl/sql question
dynamic pl/sql question [message #370323] Wed, 25 August 1999 15:32 Go to next message
ranjan
Messages: 20
Registered: August 1999
Junior Member
Hi!

I'm trying to create this procedure, but I need some suggestions.
1st I'm going to be reading in a raw file which will contain
a state code for 52 states in america and customer_id. The state code will equal to
a table name. I'm going to have 52 different tables for these
states. After reading the state_code, I need to check if the select
statement already equals to this state or not, if not then
change the table name to be this state code, then match the customer_Id's.
If customer_id's match, then write to an output file which
customer_Id's matched for this state_code.

I know I can write 52 seperate if statements to change my select statement,
but for each record it will result in 52 queries before
will get the result for this record. This will slow down our process.
We have 100 Million records to do this for. I thought about writing
a dynamic pl/sql procedure, but I have a couple of questions.

Steps that I have wrote this dynamic procedure:

1st declare my variables
2nd write dbms_sql statement for my select and leave the table_name out.
3rd parse the statement
4th execute the statement.

But I have question about this process.
If my table name already equals to current select statement, then
I just want to execute this statement. Can I do this. Will
is first look at the variables, then look at the dbms_sql and does the
parse, then executes or does it just execute, because
this record is looking at the same table that previous record looked at.

Need to know which procedure should I use, so my performance is faster
for 100 million records or another way to do this at all?

Thanks,
Ranjan
Re: dynamic pl/sql question [message #370330 is a reply to message #370323] Fri, 27 August 1999 06:56 Go to previous messageGo to next message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
I'm not an expert on dynamic PL/SQL, but here's an approach that might do the trick...

FOR <EACH FILE THE LINE IN> LOOP
   <FETCH STATE & CUST_ID ID AND VARIABLES INTO CODE STATE_CODE>
   my_cur := DBMS_SQL.open_cursor;
   my_SQL := 'SELECT 99 FROM my_table_'||state_code||
             ' WHERE cust_id = '||cust_id;
   DBMS_SQL.parse (my_cur,my_SQL,DBMS_SQL.v7);
   my_rows := DBMS_SQL.execute_and_fetch (my_cur);
   IF my_rows > 0 THEN   -- match found!
      <WRITE FILE TO OUTPUT>
   END IF;
   DBMS_SQL.close_cursor (my_cur);
END LOOP;

This is a simple approach, but it should work. You might get better efficiency from re-using the same query (for the same state) and substituting in different customer IDs, something along these lines (I'm afraid I'm a bit hazy on the details):

last_state := 'xx'
FOR <EACH FILE THE LINE IN> LOOP
   <FETCH STATE & CUST_ID ID AND VARIABLES INTO CODE STATE_CODE>
   IF state_code = last_state THEN
      <SUBTITUTE EXISTING ID RE-USE CUSTOMER AND QUERY INTO>
   ELSE
      <CLOSE THE A ONE NEW BUILD AND QUERY OLD>
      last_state := state_code;
   END IF;
   my_rows := DBMS_SQL.fetch_rows (my_cur);
   IF my_rows > 0 THEN   -- match found!
      <WRITE FILE TO OUTPUT>
   END IF;
END LOOP;

You might also consider sorting your input file into state order before (or during) processing.

Of course all this assumes that you're sensible in having one table per state rather than one table with a state_code in the primary key. By the way, I thought there were only 50 states?

Re: dynamic pl/sql question - revised [message #370331 is a reply to message #370323] Fri, 27 August 1999 06:59 Go to previous messageGo to next message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
-- Please read the code from this version, it's less confusing to the browser!

I'm not an expert on dynamic PL/SQL, but here's an approach that might do the trick...

FOR {each line in the file} LOOP
   {fetch state code & id into variables state_code and cust_id}
   my_cur := DBMS_SQL.open_cursor;
   my_SQL := 'SELECT 99 FROM my_table_'||state_code||
             ' WHERE cust_id = '||cust_id;
   DBMS_SQL.parse (my_cur,my_SQL,DBMS_SQL.v7);
   my_rows := DBMS_SQL.execute_and_fetch (my_cur);
   IF my_rows > 0 THEN   -- match found!
      {write to output file}
   END IF;
   DBMS_SQL.close_cursor (my_cur);
END LOOP;

This is a simple approach, but it should work. You might get better efficiency from re-using the same query (for the same state) and substituting in different customer IDs, something along these lines (I'm afraid I'm a bit hazy on the details):

last_state := 'xx'
FOR {each line in the file} LOOP
   {fetch state code & id into variables state_code and cust_id}
   IF state_code = last_state THEN
      {subtitute customer id into existing query and re-use}
   ELSE
      {close the old query and build a new one}
      last_state := state_code;
   END IF;
   my_rows := DBMS_SQL.fetch_rows (my_cur);
   IF my_rows > 0 THEN   -- match found!
      {write to output file}
   END IF;
END LOOP;

You might also consider sorting your input file into state order before (or during) processing.

Of course all this assumes that you're sensible in having one table per state rather than one table with a state_code in the primary key. By the way, I thought there were only 50 states?

Re: dynamic pl/sql question - revised [message #370332 is a reply to message #370331] Fri, 27 August 1999 09:19 Go to previous message
ranjan
Messages: 20
Registered: August 1999
Junior Member
Chris,

I'm trying to figure out, if pl/sql will open the cursor,
parse the statement again, if the state_code matches or
does it just execute. If state_code matches and
it parses the select statement again, then it'll
take longer for this query to run. Can you please
let me know. I really need to find this out this morning.

Thanks,
Ranjan
Previous Topic: Re: oracle sql coding
Next Topic: Using a view in a join statement
Goto Forum:
  


Current Time: Fri Oct 30 03:36:41 CDT 2020