Search Smith

ColdFusion, SQL queries, and, of course, searching

Archive for February, 2012

Oracle 10g Pagination

Posted by David Faber on February 28, 2012

A colleague of mine alerted me to the following example of pagination in Oracle 10g:

select * from (
    select /*+ FIRST_ROWS(n) */
        , ROWNUM rnum
        , count(*) over() as total_rows
    from ( 
        /* your select statement goes here */ 
    ) a
) b 
where b.rnum >= :start_row and b.rnum <= :end_row

This does have the advantage of working with an arbitrary SQL SELECT statement, but there is a somewhat simpler way of going about it if you know in advance what the SELECT statement will be:

    SELECT your_columns, ROW_NUMBER() OVER (ORDER BY your_sort) AS rnum
         , COUNT(*) OVER() AS total_rows
      FROM your_table
     ORDER BY your_sort
) WHERE rnum >= :start_row AND rnum <= :end_row

The key is that the value of your_sort must be the same in both places.

Posted in Oracle, SQL | Tagged: , , , , , | 1 Comment »

ColdFusion: Running Solr on Jetty

Posted by David Faber on February 24, 2012

Lucid Imagination has a brief but helpful post on running Solr on Jetty. In ColdFusion 9, Solr runs on Jetty by default (at least under Windows – not sure about Linux or Solaris and I don’t have any information about CF 10). The jetty.xml file can be found under <CF_HOME>/solr/etc/jetty.xml. On a single-instance installation under Windows, then, it would be located under C:\ColdFusion9\solr\etc\jetty.xml.

In order to change the port on which Jetty listens, edit the following (lines 64-78 on the default CF Solr install):

<!-- Use this connector if NIO is not available. -->
<!-- This connector is currently being used for Solr because the
nio.SelectChannelConnector showed poor performance under WindowsXP
from a single client with non-persistent connections (35s vs ~3min)
to complete 10,000 requests)
<Call name="addConnector">
    <New class="">
      <Set name="port"><SystemProperty name="jetty.port" default="8983"/></Set>
      <Set name="maxIdleTime">50000</Set>
      <Set name="lowResourceMaxIdleTime">1500</Set>

Change the value of 8983 to a different port and then restart the ColdFusion Solr service. Solr should now be listening on a different port.

Posted in ColdFusion, Solr | Tagged: , , , | 2 Comments »

ColdFusion Solr Tutorial

Posted by David Faber on February 23, 2012

I’ve started posting a tutorial page on using ColdFusion and Solr, linked to various posts on this blog and with additional commentary.

Posted in ColdFusion, Solr | Tagged: , , , | 1 Comment »