RE: Case insensitive searches

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 22 Mar 2014 21:01:30 -0400
Message-ID: <0c0701cf4633$6e53dd20$4afb9760$_at_rsiz.com>

that's right. As I tried to explain, for any table with virtual columns a single insertable view of just the physical columns will suffice, allowing you to use the real table for everything else. As a canonical work-around to what I still consider a bug (if Hans and I can figure out all you have to do is create a view with just the physical columns, shouldn't that be pretty easy to do implicitly, especially since the virtual columns are not candidates for insertion anyway?) this is cut and dried.

Only the naming convention really up for grabs. I tacked a p on the end of the table name to indicate physical. Hans used the standard v_ indicating view. I'm tending to think either p_ as a prefix or vp_ might be better, but I'm open to suggestion.

This really should go away, but then order dependent value insertion is probably not plan A, anyway.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke
Sent: Saturday, March 22, 2014 4:43 PM
To: fuzzy.graybeard_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Case insensitive searches

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


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

Received on Sun Mar 23 2014 - 02:01:30 CET