A Cost Effective Model for Database Indexing<title> </head> <body> <link rel="stylesheet" href="/nie_www.css">  <font size="3"> <br></font>   <form style="margin:0px 0px 0px 0px" action="http://67.121.68.98:9000/" method="post" target="_self">     <table width="100%" cellspacing=0 cellpadding=0 border=0> <tr>  <td rowspan="2" width="10"> <a href="http://www.ideaeng.com/index.html"><img border="0" src="http://www.ideaeng.com/images/nie_logo_small_v1.gif" alt="new idea ENGINEERING"></a> </td>  <td rowspan="2" width="20"> <nobr> </nobr> </td>  <td valign="bottom" width="450"> <nobr> <small> <a class="navbar_link" href="/index.html">Home</a> | <a class="navbar_link" href="/products/">Products</a> | <a class="navbar_link" href="/services/">Services</a> | <a class="navbar_link" href="/pub/entsrch/">Newsletter</a> | <a class="navbar_link" href="/resources/">Resources</a> | <a class="navbar_link" href="/about_us/about.html">About Us</a> | <a class="navbar_link" href="/about_us/">Contact Info</a> | <a class="navbar_link" href="/about_us/privacy.html">Privacy Policy</a> </small> </nobr> </td>  <td rowspan=2 width="20"> <nobr> </nobr> </td>  <td valign="bottom" width="200"> <nobr> <input type="text" NAME="query" SIZE="15" style="font-size:90%"> <input type="submit" size="10" VALUE=" Search " style="font-size:70%"> </nobr> <input type="hidden" NAME="id" VALUE="36902671"> <input type="hidden" NAME="pageid" SIZE="-1" VALUE="r"> <input type="hidden" NAME="mode" SIZE="-1" VALUE="ALL"> <input type="hidden" name="n" value="0"> </td>  <td width=50%> </td>  </tr>  <tr>  <td height=2></td>  <td height=1></td> </tr>   <tr> <td colspan="6"> <hr noshade size="1">  <i> Specializing in <b>Enterprise Search</b> since 1996 - including <b>FAST</b>, <b>Autonomy IDOL, K2</b>, and <b>Ultraseek</b>, <b>OmniFind</b> and <b>Lucene</b> </i>  <hr noshade size="1"> </td> </tr>  </table>  </form>  <link rel="stylesheet" href="includes/newsletter.css" type="text/css" />   <p class="breadcrumbs"><font face="sans-serif" size="-1">  <b>Locator:</b>  <a href="/index.html"> NIE Home</a> /  <a href="../../index.html"> Publications</a> /  <a href="../../index.html"> Enterprise Search Newsletter</a> /  <a href="index.html"> Volume 5 Number 1</a>   / Article 1 </font></p>  <p> <center><i> Not a subscriber? Sign up at <a href="http://www.ideaeng.com/subscribe.html"> http://www.ideaeng.com/subscribe.html</a> </i></center> </p>  <span class="central-content">     <table border=0> <tr> <td colspan="3"> <h1>A Cost Effective Model for Database Indexing</h1> <span class="byline"> <p><small><i> By Miles Kehoe - Volume 5 Number 1 - January 2008 </i></small></p> </span> <p> <h2>Introduction</h2> <p>It used to be that databases were where companies stored structured data like accounting records or personnel records. To find information, SQL was the language to use, and if you needed help, you talked to the database pros. Unstructured data and documents were maintained in the file systems, and to find information, you use your site search tool.</p> <p>Sounds easy, doesn't it? The problem is, databases began offering support for 'binary long objects', or BLOBs; and it was only a small step from BLOBS to long text data types, and soon database administrators everywhere were loading office format files, PDFs, and other documents into relational tables. A logical storage option perhaps, but even now relational systems typically offer much less rich feature sets for full text retrieval.</p> <h3>Enter the database gateway.</h3> <p>Nearly every search vendor now supports methods for indexing and retrieving textual data within a relational database. Unfortunately, because large relational database systems cost huge amounts of money, search technology companies often charge a significant premium to index relational data. And while the solutions, often called gateways or connectors, are quite automated, they often limit your flexibility.</p> <b>Write Your Own?</b> <p>We used a database to store large numbers of web pages at a previous company, and we wrote our own gateway for our particular needs. But writing your own code can be complex as well, so when one of our customers wanted to index a relatively small number of database documents, we delivered a simpler solution that might work for you as well.</p> <h2>The Customer's Problem</h2> <p>Our customer had several hundred articles stored in an Oracle database which were served by Cold Fusion to generate web content for their users. This presented a problem: the database did not contain the actual HTML page; it only contained HMTL snippets which we expanded at display time by Cold Fusion. Each snippet, however, was stored in a database record with a unique key value. The links from the web site to a given article was of the form:</p> <a href="/cfm/getdb?id=1234>Read Story 1234</a></p> <p>Unfortunately, very little of this content was linked from existing static pages, so spidering the site would miss hundreds of the articles.</p> <h2>A Custom Tool</h2> <p>Our solution was to create a page which contained links to every article in the database, and then send the spider to that unique link or "landing" page. This type of solution will work for nearly any textual content stored in a relational database.</p> <h3>Extract Database Key Fields</h3> <p>We chose to implement the solution in Java because of other work we were doing on the system. In fact, we could have used Perl, Python, or nearly any scripting language with access to the database, Oracle in this case.</p> <p>The Oracle database contained articles for several publications, each with a unique Pub_Code. Within that limit, we wanted to capture the article ID for each article.</p> <p>Our plan was to execute a SQL query of the form:</p> SELECT ID from Pubs where Pub_Code='news"</p> <p>If you've used databases, you know that this query will return a list of ID numbers. Once you have this list, you can decide what kind of approach you will use to create the collection. The two methods that are most common are either using a web spider indexer, or to create a "bulk insert" file and use a command line indexer. Which you use may depend on the search engine you are using, how your other collections are created, and on many other variables we won't get into here.</p> <h3>Create an HTML Link Page for an Indexing Spider</h3> <p>If you want to use an indexing spider provided by your vendor, you will need to create an HTML landing page with links to each article by ID. Once you have the list of articles using the above SQL statement, it is an easy task to write code using this logic (though NOT this syntax!):</p> foreach docID in resultList</br> output an HTML link of the form </br> <a href=/cfm/getdb?id=docID>docID</a> <p>By writing this stream to an HTML file, you create a link to every document of interest. And by running an index job with this landing page as the starting point, the standard indexing tools create the collection from live database data.</p> <p>Because our customer elects to re-created their full-text index from scratch nightly, all we needed to do is schedule the customer Java application to run a few minutes before the spider was scheduled to re-create the collection. If you are doing incremental updates, you might need to add additional logic; but you can see how simple the process really is. Even with the custom code, our solution was much less expensive than the database connector and our customer has full control over the indexing process. Maybe you'll find something like this works for you as well!</p> <h3>Create bulk load file for Command Line Indexing Tools</h3> <p>If you want to use a command line utility that supports a bulk load capability, you use a similar logic but the command line syntax is different. And you may need to invoke the command line tool once for each link if your search engine doesn't support an indirect bulk format. <i><b>Note:</b> Dr Search writes about <a target="new" href="../number_01/dr_search.html"> Autonomy IDX and K2 Bulk Insert Files</a> this month.</i></p> <p>In such an indirect format, you create links to documents that are to be indexed in a file, and then point the indexer to that file.</p> <p>Assuming you will run the index command once for each link, you use similar logic as above with different output syntax.</p> foreach docID in resultList<br> output a command line in valid <br> syntax for your search engine <p>This creates a bulk load file that may be somewhat less efficient than the HTML link (or landing) landing page above, but which may work for your site depending on your implementation.</p> <h2>Summary</h2> <p>You've seen two ways to extract text from a relational database without using a special gateway or connector. It's not difficult, and depending on the complexity of your site architecture, you may find you can save a significant amount of capital expense by simply solving the problem on your own. The gateways and connectors are very nice for some sites, but implementing a full database gateway is complex - and on top of that, we're partial to being frugal!</p> </p>  </span>    <center> <hr noshade size="1"> <small> <a class="navbar_link" href="/index.html">Home</a> | <a class="navbar_link" href="/products/">Products</a> | <a class="navbar_link" href="/services/">Services</a> | <a class="navbar_link" href="/pub/entsrch/">Newsletter</a> | <a class="navbar_link" href="/resources/">Resources</a> | <a class="navbar_link" href="/about_us/about.html">About Us</a> | <a class="navbar_link" href="/about_us/">Contact Info</a> | <a class="navbar_link" href="/about_us/privacy.html">Privacy Policy</a> <br> Copyright New Idea Engineering, Inc 1996 - 2008 </small> <hr noshade size="1"> </center>  <script src="http://www.google-analytics.com/urchin.js" type="text/javascript"> </script> <script type="text/javascript"> _uacct = "UA-647237-1"; urchinTracker(); </script>  <script src="http://www.google-analytics.com/urchin.js" type="text/javascript"> </script> <script type="text/javascript"> _uacct = "UA-707279-1"; urchinTracker(); </script> </body> </html>