Speedup SQL View

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi lilputian,

10 million records is quite a big table and would usually a number of seconds or even minutes to query on such a table. The speed depends on the complexity of your SQL of your view but a simple query like

SELECT J1.* FROM GL.JE_VIEW J1 WHERE J1.DIV = '500'    shouldn't take particularly long.

and do make sure the DIV table is indexed on the Table referenced by the view.

Do post the view SQL syntax as that is where we should be tackling.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lilputianAuthor Commented:
Here's the SQL view:
  gl.je_header_id, gl.je_line_num,
  case when gl.gl_sl_link_table = 'RSL' then rsl.accounted_dr else case when gl.gl_sl_link_table = 'APECL' or jr.gl_sl_link_table = 'APECL' then ael.accounted_dr else gl.accounted_dr end end accounted_dr,
  case when gl.gl_sl_link_table = 'RSL' then rsl.accounted_cr else case when gl.gl_sl_link_table = 'APECL' or jr.gl_sl_link_table = 'APECL' then ael.accounted_cr else gl.accounted_cr end end accounted_cr,
    case when gl.gl_sl_link_table = 'RSL' then pv.vendor_name   else case when gl.gl_sl_link_table = 'APECL' or jr.gl_sl_link_table = 'APECL' then ipv.vendor_name   else gl.reference_4  end end reference_4, --ref4_max_vendor,
  gl.accounted_dr je_accounted_dr, gl.accounted_cr je_accounted_cr,
  rsl.accounted_dr sl_accounted_dr, rsl.accounted_cr sl_accounted_cr,
  gl.entered_dr je_entered_dr, gl.entered_cr je_entered_cr,
  rsl.entered_dr sl_entered_dr, rsl.entered_cr sl_entered_cr,
  ph.segment1 sl_max_ponum,
  rt.attribute5 rec_sl_att5_max_wonum,  
  rt.attribute6 rec_sl_att6_max_fincntrlid,
  rt.attribute7 rec_sl_att7_max_itemnum,
  id.attribute5 inv_sl_att5_max_wonum,  
  id.attribute6 inv_sl_att6_max_fincntrlid,
  id.attribute7 inv_sl_att7_max_itemnum,
  gl.reference_1 je_ref1,
  gl.reference_2 je_ref2,
  gl.reference_3 je_ref3,
  pv.segment1 vendno, pv.vendor_name,
  gl.gl_sl_link_id, gl.gl_sl_link_table,
  jr.gl_sl_link_id jr_gl_sl_link_id, gl.gl_sl_link_table jr_gl_sl_link_table,
  rsl.je_source_name, rsl.je_category_name, rsl.je_batch_name, rsl.je_header_name, rsl.je_line_description,
  gl.last_update_date, gl.last_updated_by,
  gl.set_of_books_id, gl.code_combination_id, gl.period_name, gl.effective_date,
  gl.status, gl.creation_date, gl.created_by, gl.last_update_login,
  gl.amount_includes_tax_flag, gl.tax_document_identifier, gl.tax_document_date,
  gl.tax_customer_name, gl.tax_customer_reference, gl.tax_registration_number,
  gl.tax_line_flag, gl.tax_group_id,
  id.invoice_distribution_id, rt.transaction_id
  gl.gl_je_headers gh,
  gl.gl_je_lines gl,     -- over 11 million records
  gl.gl_code_combinations gcc,
  po.po_headers_all ph,
  po.po_vendors pv,
  ap.ap_ae_lines_all ael,    -- over 12 million records
     Select idi.invoice_distribution_id, idi.line_type_lookup_code, idi.invoice_id, idi.po_distribution_id,      
          idi.dist_code_combination_id, idi.amount, idi.attribute5, idi.attribute6, idi.attribute7
     FROM ap.ap_invoice_distributions_all idi
     WHERE idi.line_type_lookup_code = 'ITEM'
     Select idt.tax_invoice_distribution_id invoice_distribution_id,
           idt.line_type_lookup_code, idt.invoice_id,
           idt.po_distribution_id, idt.dist_code_combination_id,
           idt.tax_amount * idt.perc_dist amount,
           idt.attribute5, idt.attribute6, idt.attribute7
     FROM wfm.wfm_ap_invoice_tax_dist idt
 ) id,
  gl.gl_import_references jr,
  po.po_distributions_all ipd,
  po.po_headers_all iph,
  po.po_vendors ipv
  -- link the journal header to the journal line
  and gh.je_header_id = gl.je_header_id
  -- link the journal line to the gl code combination
  and gl.code_combination_id = gcc.code_combination_id
  -- link the category to the user category
  and gh.je_category = jc.je_category_name
  -- link the journal line to the PO sublegder
  and gl.gl_sl_link_id = rsl.gl_sl_link_id (+)
  -- link the subledger line to the transaction
  and rsl.rcv_transaction_id = rt.transaction_id (+)
  -- link the transaction to the po header
  and rt.po_header_id = ph.po_header_id (+)
  -- link the po to the vendor
  and ph.vendor_id = pv.vendor_id (+)
  and ael.source_id = id.invoice_distribution_id (+)
  -- link journal import reference to AP accounting entries
--  and NVL(jr.gl_sl_link_table,'APECL') = 'APECL'
  and jr.gl_sl_link_id = ael.gl_sl_link_id (+)
  -- link journal line to journal import references
  and gl.je_header_id = jr.je_header_id (+)
  and gl.je_line_num = jr.je_line_num (+)
  -- link the invoice distribution to the po distribution, po header and po vendor
  and id.po_distribution_id = ipd.po_distribution_id (+)
  and ipd.po_header_id = iph.po_header_id (+)
  and iph.vendor_id = ipv.vendor_id (+)
  and gcc.segment1 in ('501', '502', '524', '525')
SujithData ArchitectCommented:
What is the nature of the underlying data?

Is it a transactional system/Datawarehouse environment?
If the data is uploaded at intervals, then you can consider having materialized views instead of a view.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Wow! That SQL can blow me off anytime. Joins with lots of tables and 2 very big ones! It will surely take some time to execute that view.

The view SQL looks good actually but for such a complex SQL on big tables, you should write the result to a temp table rather than as online view. If the view already takes many minutes or even hours to execute, there is really no point in keeping it as an view. Worst such a SQL is likely to choke up lots of the server resources and will slow down others who may be accessing the database.

Change it to say PL/SQL that writes to a temp table. Schedule it to run periodically (how often will depend on the business need). Create the necessary indexes on the temp table.

Now instead of having the using to access the view, have them access this temp table.

Even if this is a datawarehouse env, I dont think creating a MV is an option, According to the author-- His q is to look at the query, It appears that user 1 and 2 are novice users and they dont know how to form a query, If you are speaking about non query rewriteable MV, still the same problem persists because of the where clause there in the query that is being run by user 1 and 2.


Please let me know if my observation is correct- Is it OK if I give you a diff query other than one the user uses?
SujithData ArchitectCommented:
>>they dont know how to form a query
The query from the users is from a VIEW. From what I understood users are not creating the view each time. So, if the data is not modified frequently(Data WH env), the view text can be used to form an MV.

>>non query rewriteable MV???
As the select is going to be directly from the MV, no question of query re-write. Moreover, you can put a filter condition for direct/indirect select from an MV.
Finally the user query will still remain the same, but the underlying object will be an MV.

1. You are right

2.You are right, The speed is going to enhanced because, in case of a view, the view is created in memory, every time the select statement is fired by user1 or 2 or both, But in case of a MV- The MV is already built physiocally so it fastens up the query parsing, but the time taken to make a tablescan is same in both the cases.


Can you please let me know the where clause variables granularity?

BTW it all is true only when this is DWH env.. If this is OLTP environment, creation of MV hurts the performance
lilputianAuthor Commented:
Thanks for all your replies.  It is not a datawarehouse environment.  

I'm sorry, I don't know what the 'WHERE clause variables GRANULARITY' means.
Could you give me an example.  Thanks.

And yes, you can give me a different query.

I believe they need to do the filtering at the view level to limit the records.  Would Stored Procedures be the way to go?  One could pass the Period as well as the division variable.  I would as well change the table joins as follows, what do you think?
SELECT ......
WHERE GCC1.SEGMENT1 = '501' And GH1.PERIOD_NAME = 'MAR-07'    
can you run select count(distinct J1.DIV )
and give me the number?
lilputianAuthor Commented:
The count is 161.
lilputianAuthor Commented:
There are 545,000 records in the GL_CODE_COMBINATIONS file.
Hi lilputian,

I do not think that breaking up the records by filtering is not a good idea.

The method suggested by Sujith and myself are similar because they are the method to prepare the consolidated data in the background. The difference is that materialized View is a utility in Oracle that allows you to do it with very little coding while my method requires hardcore programming. It's a good method but you must understand and learn how to use materialized view and this oracle utility must have be set up into the database. I am sure Sujith will be able to guide a little on the steps if you take the materialized view approach.

Data preparation in the background is the key to improving dramatically the performance for such complex consolidation on huge datasets. It requires more hardwork from but will ease the users pain.

Sorry I meant "I do not think that breaking up the records by filtering is a good idea."
try index the foreign keys
and making anothe file group to handel talbe
i recommend that u make a seprated file group for the big table
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.