Speedup SQL View
Posted on 2007-04-10
I have been asked to look at a query and try to speed up execution. The query was setup as a view, using SQL Developer. It is accessed by different users.
User 1: SELECT J1.* FROM GL.JE_VIEW J1 WHERE J1.DIV = '500'
User 2: SELECT J1.* FROM GL.JE_VIEW J1 WHERE J1.DIV = '502'
JE_VIEW has approximately 10 FROM tables. I did a count on some of the tables and found that some of them were over 10 million records.
Some thoughts I had are as follows:
1) Mention only 1 FROM table and use the JOIN to reference the other tables.
2) Do the DIV check at the VIEW level, although I believe they setup the system this way so if they make a change, only 1 view would require changing. Maybe Stored procedures would be the way to go?
Any help would be appreciated.