Re: Oracle vs Sybase

>
> These would work except that I already have about 1400 queries saved in
> access. I really really don't want to rewrite every single one of them.
> I have been digging into the oracle documentation a bit and NLS_COMP and
> NLS_SORT parameters seem like it's what I want to do. They both seem to
> have som quirks though. Here is what the docs say.
>
> "As a final note, when NLS_COMP is set to ANSI, a linguistic index must
> exist on the column where the linguistic order is desired."
>

I suppose this packets affect only the sorting (order by ), not more and not less... The use of index in Oracle is not the same as in a desktop db (for example FoxPro or DBase)
You don't have to use an index in order to execute a query. It can only improve your performance with them.

> This means I have to build a index on every single text field? why isn't
>

No you don't have to, you could ;-)

> possible to tell oracle just to treat all text as US english (not even
> an option!) or Ascii7 (what does that mean?) and treat all sorts and
> order by statements in a case insensitive manner?.

About the sorts, you can use just ...select * from some_table order by UPPER(name) and sorting will be fine.
An Index is not a must in Oracle. If you need it you can use it. So about your Access queries and the rewriting: 1. You can make views as select UPPER(some_field),Upper(anather_field)... and query only the views
2. You can automate the rewriting of this queries. Just query the data dictionary for all fields of varchar and make a proc which searches the text of the query and if it finds the name of a field which is in the previous query replace the string with upper(string) and the same with the part next to this field. If your tables are 1000-5000 records it will work just fine. If you see that the performance is not acceptable you just have to create an index on upper(field) which causes this performance bottleneck and so on. The biggest problem that you'll have is Access itself. I'm afraid that you have only access queries and not pass through queries. That's your BIGGEST problem. No matter which db server you use.
Let's take a look on the "convetional" aproche by migrating your access db to a db server (for example oracle):
You start the Oracle migration wizzard for access and after the end you have all tables in oracle, you have instead of tables, table links to the tables in oracle. Your queries are still the same based on the table links. Suppose you have a left outer join with to tables and a where statement name like 'alex%'
The syntax of the access query is something like select ... from table 1 left outer join table2 on key1 where name like 'alex%'
Access cannot use the same sysntax and send it ti the server because the syntax in oracle is select ... from table1, table2 where table1.key(+)=table2.key and name like 'alex%'. The access help tells us that access does this translation automatically but that's not true!!!! Access will load the whole table1 , the whole table2 and then execute the query in its own db engine. That can be a disaster!!!! So in order to port your app with a reasonable perfomrance you should rewrite your queries to passthrough. The problem is, pass through queries in access are not updateable (in foxpro they are!!!) So you must change some logic of your app in order to match this terms.If you have small tables it's possible that you don't recognize this problems immediately, but i recommend that you make sone performance test with more populated tables and see how the performance do.
If you have to rewrite all/most of your queries in pass through it'll be no problem to add the upper function to your varchar's statements...

Hope I could help you
Alex

>
 

> In sybase this is
> possible when you create the database and in interbase this is possible
> in when you create the column.
Received on Wed Mar 29 2000 - 00:00:00 CST