RE: Case insensitive searches
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>, <fuzzy.graybeard@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 22 Mar 2014 20:45:47 -0500
Roger that Mark. It does seem to be a bug to me too. Since I can't
insert it directly, just let me skip it and do the positional insert
anyway. I suppose it gets dicey with lots of columns but still this
can't be THAT hard can it?
- Ric
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Saturday, March 22, 2014 9:02 PM
To: Ric Van Dyke; fuzzy.graybeard@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Case insensitive searches
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ric Van Dyke
Sent: Saturday, March 22, 2014 4:43 PM
To: fuzzy.graybeard@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hans Forbrich
Sent: Friday, March 21, 2014 5:23 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
