ushelke
asked on
What is Good...CURSOR or PLAIN SELECT Statement...
Hi...
I am writing a procedure. I am starting with opening a cursor and looping through each record. Now for each record I have to get other details from 5 different tables. I am plannig to create five different parametric cursors for all five tables. Also, I am planning to pass PK to these cursors to fetch data for the record fetched by first cursor.
First cursor is must...I know that all the five tables are going to return me one single record for the passed PK.
Is cursor approach is good ??? or should I just get the data using simple SELECT statement with WHERE clause ??? I am looking for SPEED ...I am expecting, first cursor will return 2000 rows. That means if I am using CURSOR approach I am using five tables cursor 2000 times. Where as using SELECT statement I am hitting DB table directly each time for getting the record.
I am writing a procedure. I am starting with opening a cursor and looping through each record. Now for each record I have to get other details from 5 different tables. I am plannig to create five different parametric cursors for all five tables. Also, I am planning to pass PK to these cursors to fetch data for the record fetched by first cursor.
First cursor is must...I know that all the five tables are going to return me one single record for the passed PK.
Is cursor approach is good ??? or should I just get the data using simple SELECT statement with WHERE clause ??? I am looking for SPEED ...I am expecting, first cursor will return 2000 rows. That means if I am using CURSOR approach I am using five tables cursor 2000 times. Where as using SELECT statement I am hitting DB table directly each time for getting the record.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jaramill
Well if you want to take data from these records and do some processing before inserting, then you use a CURSOR with the SQL statement being one big SQL with sub-queries. If you're doing just a straight insert then jdlambert1's suggestion is correct. INSERT INTO SELECT * FROM ... is always faster.
ASKER
I am taking this data and then going to use this data for processing purpose. I am also thinking of writing one single cursor for fetching data from all five tables rather than writing five different cursors. But not sure which is good approach. Five different cursor or one single cursor based on complicated SELECT statement with one parameter i.e my PK key from first cursor. Please comment...
If you read my last post I say what you should do, and that is one single cursor based on a complex SQL statement.
Avoiding 4 cursors would be very beneficial. Use the single cursor, regardless of how complex that makes the SELECT statement.
Also, you can test the SELECT statement by itself. If it has performance problems, you should be able to improve that by adding appropriate indexes.
Also, you can test the SELECT statement by itself. If it has performance problems, you should be able to improve that by adding appropriate indexes.
SELECT is best. But you can use it only if it selects ONE row.
If your select statement selects more then one row you have to use
cursor. This is so because PL/SQL is not able to store a set of rows.
If your select statement selects more then one row you have to use
cursor. This is so because PL/SQL is not able to store a set of rows.