RE: Case insensitive searches

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Sat, 22 Mar 2014 15:43:26 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF022AA3A6_at_WIN02.hotsos.com>



Roger that. And as long as it's just ONE layer of views to solve issues like this life is pretty good.

The trouble is some shops get "view happy" and you have um-teen layers of views there is a major disconnect between the data storage and the presentation, then performance starts to degrade, query plans look wildly complicated for what appear to be simple queries, developers have no clue what is really going on, more views are created, and the cycle continues.

So like any feature of the database, used well and not over done it will help. Over used and it may be more like Pandora's Box.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hans Forbrich Sent: Friday, March 21, 2014 5:23 PM
To: oracle-l_at_freelists.org
Subject: Re: Case insensitive searches

On 21/03/2014 1:07 PM, Ric Van Dyke wrote:
> There is one (at least) major drawback of using virtual columns.
>
> Inserts must be done using column reference not position (see below).
Just another reason to design tables for 'storage' and design at least one set of views to map to business entities.

By overlaying a view on that table to eliminate the virtual column,

SQL> l

   1 CREATE TABLE vc

   2     ( col1 NUMBER
   3     , col2 NUMBER
   4     , col3 NUMBER GENERATED ALWAYS AS (col1 + col2) VIRTUAL
   5*     )

SQL> / Table created.

SQL> CREATE OR REPLACE VIEW v_vc as select col1, col2 from vc;

View created.

SQL> INSERT INTO v_vc VALUES (10, 20)

1 row created.

SQL> I personally believe that will eventually allow us to separate the needs to the developer (business entities) from the needs of the DBA (storage and DB objects).

/Hans

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Sat Mar 22 2014 - 21:43:26 CET

Original text of this message