0

I cannot use a materialized view for the base table data changed frequently. There are about 0.6 million rows in the_big_view. The query I need to do is: SELECT * FROM the_big_view WHERE student_number='*****', which returns arbitrary rows of data.

If I add an "ORDER BY student_number" clause in the "CREATE VIEW the_big_view AS ...", does that do good?

closed as too broad by Philᵀᴹ, mustaccio, RDFozz, Colin 't Hart, hot2use Aug 1 '18 at 18:06

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You'll need to provide more details. In general, improving the performance of a query that simple against a view will require changes to the view itself, or the addition or alteration of indexes, or regular update of statistics. Can you provide the EXPLAIN PLAN for the query? – RDFozz Aug 1 '18 at 17:20
1

No, an order by in the view will not help. You may want to do two things. First of all you should ensure that student_number is indexed in the underlying table. Plus if the big view joins multiple tables you should ensure that the join columns are indexed.

Second you may want to use a number like this:

WHERE student_number=123456

instead of

WHERE student_number='123456'

if student_number is numeric to avoid type conversions.

1

There are several things you want to do.

  1. Make sure your view is not doing unneeded work such as ordering the results or other data manipulation that will not be needed much.
  2. Check to ensure that you are not using other views within this view as when you start using views within a views you can lose the ability to hit indexes.
  3. Make sure you have proper indexes on not only what is joining all the tables together but what the user will search by.
  4. Go back and look at the view again and see what can be optimized in order to to improve performance and what can be removed altogether if if is not really needed.
  5. Limit the data in the view to what you need which might mean you need to create a new view. This will help limit the data returned which will improve performance.

As it is since no information about the view has been provided here the only help that can be provided is general tips about writing views.

  • Also - request/create a different view if you don't need all the data from the big_view. I've seen cases where users query a view that does several joins when they could query a base table (that they didn't have privy to yet). – Kevin Bott Aug 1 '18 at 14:32
  • @KevinBott Thanks for the comment, not sure how I missed that easy win in the first place. – Joe W Aug 1 '18 at 15:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.