Varchar options on Oracle

Hi,
is your question about Oracle tables / columns in Lawson database or it's a general one for Oracle ?
- if it's for Lawson db then : Lawson tables in Oracle db do not have VARCHAR, they have all CHAR instead. So, you don't have choice here.
- If it's for general Oracle databases then there is no problem at all, to use VARCHAR on an indexed column.

We can imagine the day where Lawson uses VARCHAR ( an very old attribut in Oracle, back to Oracle V6 ), we will save a lot of spaces . In a Lawson Oracle db, the Indexes take more space than the real data. In our Prod db, we have about 650Gb for indexes for 400Gb of real data.

Lawson does use VARCHAR2 - it just isn't a default but you can set an option to use VARCHAR2 instead of CHAR. If you aren't doing this I stongly suggest doing so. Seems to be all benefit & no loss to me. You will sometimes hear of a possible performance degredation in some (unlikely) circumstances but generally I think the performance will be much better because there are fewer pages to read.

It is a classic storage vs. CPU tradeoff. But most people only focus on the immediate cost rather than the long-term benefit of (reduced) storage. Take the example of an employee name. Assuming it doesn't change, yes, it (minimally) impacts performance when initially storing the record. But the benefit is then "enjoyed" for the lifetime of the record in reduced storage costs, faster backups, etc.

________________________________
From: forums-lsf-s3-sys-admin@lawsonguru.com
To: John Henley
Sent: Fri Jun 05 04:35:03 2009
Subject: RE: Varchar options on Oracle [00006097:00007616]

[https://images.lawsonguru.com/logos/LawsonGuru_com_350.jpg] <https://www.lawsonguru.com/>
S3 Systems Administration Forum Notification
A message was posted to a thread you were tracking.
Ben Coonfield Posted:06/05/2009 07:33 AM Subject: RE: Varchar options on Oracle

Lawson does use VARCHAR2 - it just isn't a default but you can set an option to use VARCHAR2 instead of CHAR. If you aren't doing this I stongly suggest doing so. Seems to be all benefit & no loss to me. You will sometimes hear of a possible performance degredation in some (unlikely) circumstances but generally I think the performance will be much better because there are fewer pages to read.
________________________________

To view the complete thread and reply via your browser, please visit:
https://www.lawsonguru.co...fc/7616/Default.aspx

You were sent this email because you opted to receive email notifications when someone posted and/or responded to a message on this forum.
To unsubscribe to this thread please visit your user profile page and change your subscription options.

Thank you,
LawsonGuru.com

Hi Ben,
How to setup for varchar2 ( in dbdef i guess ). It's applicable for customized tables only or can we apply that for standard Lawson tables as well ?

This is covered in detail in the book "Lawson Administration: Data Access Using Oracle ". But in short, there are two different ways, the old one which I use, and a newer one which Lawson is now encouraging but which seems to achive the same result. Either one will work for any table in dbdef, either lawson-defined tables or custom ones.

The old way is just to create a table named LAW_DBA_TABLE in your lawson schema, with the same columns as listed in the documentation. You then populate this table by adding one row to LAW_DBA_TABLE for every table in your system. You can enter the ones you want manually, or you can automatically enter them all by using the lawson utility dbatables9 or dbatables. Then, for every table you want to use VARCHAR, you update the UVARCHAR column for that table to have a value of "Y" or "K". "Y" will convert every CHAR to a VARCHAR2 except the ones used in an index, "K" will convert ALL CHARs.

The change will not take effect until the next time the table is rebuilt by a dbreorg, which you can force if necessary with a dbreorg -G.

The other method uses the editda utility to do about the same thing, it just stores its data in a different format. Either way, you can set this option separately for each table.