Re: Column Questions

comments inline

"Dr. Mueller" <nospam_at_nospam.com> wrote in message news:4JgQ6.49702$4f7.3663104_at_bgtnsc06-news.ops.worldnet.att.net...
> > > 1) Is there a naming convention for indexes and triggers? I've seen a
 few
> > > examples for indexes, people use K_ and PK_. Just wondered what you
 thought
> > >
> >
> > there are quite a few naming conventions in common use.
> > we typically use a unique 6 character "table identifier" as
> > the first part of the table name (e.g. TVX010_SUBSCRIBER
> > and the use the table id in the names of constraints, indexes
> > and triggers on the table... e.g. TVX010_PK, TVX010_IX1,
> > TR_TVX010. the table identifier is not used in the view name
> > (e.g. SUBSCRIBER)
>
> Oh I see. An abbreviated version of the table name? Same for sequences
 as
> well? ...and the reason you don't use it in the view name is because a
 view
> can reference any number of tables and to prefix it with a table
 identifier
> wouldn't make sense?
>

not so much an "abbreviation" as a unique identifier. only one table in the schema can be identifed as TVX010. this rule is not enforced by oracle, but by our naming convention.

the primary key (and its related index) would be named TVX010_PK. unique indexes would be named TVX010_UX1, TVX010_UX2, etc. non-unique indexes would be named TVX010_IX1, TVX010_IX2, etc. foreign key constraints are named TVX010_FK1, TVX010_FK2, etc. check constraints are named TVX010_CK1, TVX010_CK2, etc. triggers start with TR_ followed by TVX010. if there were more than one trigger, we would add a suffix to differentiate them.

the views, which the applications and users access, do not have the "meaningless" table identifier, only the meaningful part of the table name, e.g. SUBSCRIBER. and views that join multiple tables, can be named without reference to the ids of the underlying tables e.g. SUBSCRIBER_SERVICE for our custom-written applications, the application connects as a username which is NOT the owner of the objects. only the required system privileges (create session, alter session, create synonym) and object privileges on the views and packages are granted to the user. there are several benefits, and only a few drawbacks, to this.

> >
> > > 2) I want to create a DESCRIPTION column, which is a VARCHAR2(256).
 The
> > > problem is I want to search it at some point, is it considered an "OK"
 thing
> > > to create a non-unique index from this?
> > >
> >
> > an index on the DESCRIPTION column may speed up some queries.
> > if you mean by "searching", runnint a query using a construct such as
> > WHERE DESCRIPTION LIKE '%somestring%', then, no, Oracle will
> > not use an index on DESCRIPTION.
>
> Yes, I mean to search for a particular word in the description, for
 example
> "Microsoft" or "Product". I just wondered if it were acceptable to index
> such a column. So Oracle wouldn't use an index on this column? ...even
> although I created one?
>

oracle may use the index if it can perform a range scan on the column e.g.
  WHERE DESCRIPTION = 'Product'
  WHERE DESCRIPTION LIKE 'Product%'

but oracle will NOT use the index if you use a function on the indexed column or you are not searching on the leading portion of the column e.g.
  WHERE UPPER(DESCRIPTION) = 'PRODUCT'
  WHERE DESCRIPTION LIKE '%Product%'

for "keyword" search capability, we developed our own custom keyword "index" scheme. this involved adding separate "keyword" tables and a different approach to writing the SELECT statements.

here is builtin functionality (introduced with Oracle8) for full text searching
(as part of intermedia CONTEX ?) which i have not used before.

HTH Received on Sun May 27 2001 - 21:50:09 CDT