Problem solve Get help with specific problems with your technologies, process and projects.

Create a view to improve search performance?

Will creating a view improve the performance of my search? (So we would be searching on the view instead of the underlying table).

Summary: We are building a resume bank, and resumes and job descriptions will be updated by the employers and employees daily. Therefore we cannot do searches based off of static "snapshots." We need our search to be able to search dynamically updated tables.

One of our searches searches job descriptions, based on any combination of user input:

1. location (multiple select box -- choices are static and provided)
2. job function (multiple select box -- choices are static and provided)
3. keyword search on a description field (text box -- any text string)

Since basically the search engine is only going to look at three fields in the underlying table, I was thinking that creating a view might speed up the retrieval time.

The results of the search will return a list of results. Each result includes:
1) the first line of the description (a headline)
2) the location

We would not need all of the information in the underlying table, until the user clicks on the headline, which retrieves the details (then a query would execute that retrieves all of the fields in the underlying table). At this point we would directly query the table.

My thinking for using the view was that possibly it would use memory cache better? Or does it have the opposite effect, creating an unnecessary intermediate step?

The view by itself won't help performance. An index (possibly multiple indices) would be more likely to help than anything, since it would drastically reduce the number of I/O operations needed to find the "interesting" rows. Keep in mind that different database engines handle index processing differently, so there isn't a "one size fits all" answer to this question.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Database Design Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your database design -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.