Search Smith

ColdFusion, SQL queries, and, of course, searching

Archive for the ‘SQL’ Category

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 »

Moving from Oracle Text to Solr

Posted by David Faber on January 11, 2012

I’ve not had the opportunity to use Oracle’s full text search, but I thought I would offer this link for consideration:

Moving from Oracle Text to Solr/Lucene

I did have the opportunity some years ago to utilize SQL Server’s full-text searching ability (this was first available in SQL Server 7 if memory serves). At the time we were using ColdFusion with Verity as our search engine. I did not see a good way to get around the custom field limitations in Verity, so I searched for my criteria using <CFSEARCH>, then used those results to query a SQL Server database (hopefully using the IN clause instead of querying row-by-row, but it was several years ago and I could not say for certain). Things got much better when I switched to SQL Server’s full-text search as I was then able to do the keyword search and retrieve related data all in one query.

Update: The following article might also be interesting:

Text Search, your Database or Solr

One possible issue with Oracle’s full text search is that there is no provision for faceted search. That might, of course, not be an issue if one can integrate regular queries with full-text queries. This was certainly possible with SQL Server, but I don’t know if Oracle offers this capability.

Posted in Oracle, Solr, SQL | Tagged: , , , , | Leave a Comment »

Oracle: Friendly URLs

Posted by David Faber on January 3, 2012

On the one hand, we want to avoid hitting the database if we don’t have to. On the other hand, if we can use built-in functions in our queries, this can be preferable to manipulating the data after it’s been returned from the database. For example, writing this:

SELECT emp_id, INITCAP(fname) AS fname, INITCAP(lname) AS lname
  FROM emp

is certainly* better than writing this:

SELECT emp_id, fname, lname
  FROM emp

and then using your development tool to capitalize the names after the data have already been returned (in CF it would look something like this: ucase(left(fname, 1)) & lcase(mid(fname, 2, len(fname) – 1)) – clunky!!).

*As long as your database queries need not be portable.

A better example still would be the following:

SELECT emp_id, COALESCE(dept_id, 0) AS dept_id
  FROM emp

instead of:

SELECT emp_id, dept_id
  FROM emp

With the latter I have to check to see if dept_id is non-null before displaying, while with the former I can simply display as-is.

What does this have to do with friendly URLs? Friendly as in SEO-friendly. The URL is very important in search engine optimization. Suppose we have a table of articles as in a previous post:

SELECT id, title, description, pubdate, journal_name, author_name, num_reads
  FROM articles

We would like to have the article’s title in the URL. The conventional wisdom is to use hyphens (-) as word separators. We also don’t want any weird characters that are going to be url-encoded. The solution (or, at least, “a” solution) is to use regular expressions to get rid of unusual characters and replace spaces with hyphens. And here Oracle is truly our friend:

SELECT id, title, description, pubdate, journal_name, author_name, num_reads
     , REGEXP_REPLACE(REGEXP_REPLACE(LOWER(title), '[^-a-z0-9/ ]', ''), '[/ ]', '-') AS url_keyword
  FROM articles

The inner REGEXP_REPLACE replaces every character that is not (a) alphanumeric, (b) a space, or (c) a hyphen or forward slash with a null string. The outer one replaces forward slashes and spaces with hyphens. What is left over is bunches of alphanumeric characters (hopefully, words) separated by hyphens – in other words, an SEO-friendly URL string.

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