Home » RDBMS Server » Performance Tuning » Rarely used columns (Oracle 10.2.0.1.0,RHEL5,pro*c)
Rarely used columns [message #440951] Thu, 28 January 2010 01:29 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
create table txn_log
(
txnid number primary key,
orgtxnid number,
termid number,
amount number,
req_msg varchar2(300),
resp_msg varchar2(300)
);

The last two columns are used to store binary data, these columns will always have values
but not selected/accessed or not used in where clause.

I query the table either by txnid or orgtxnid(indexed) only.

Is there anything I can do to increase the performance (fast search)?
Shouldn't I store binary data in varchar2?
Should I use LOB instead of varchar2?
Should I split the table into two.(I am afraid of two inserts)


Re: Rarely used columns [message #440955 is a reply to message #440951] Thu, 28 January 2010 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enter in 4NF and split the table in two: one for the most queried columns and one with the other columns.

Regards
Michel
Re: Rarely used columns [message #440972 is a reply to message #440955] Thu, 28 January 2010 02:55 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Like this?
create table txn_log
(
txnid number primary key,
orgtxnid number,
termid number,
amount number
);

create table messages
(
txnid number,
req_msg varchar2(300),
resp_msg varchar2(300)
);

insert into txn_log values (2222,1111,100,20);
insert into  messages values(2222,'req_data','resp_data');

with or without foreign key reference.

Is there any better way(performance wise) to insert into table(instead of two insert)?
Re: Rarely used columns [message #440976 is a reply to message #440972] Thu, 28 January 2010 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Messages.txnid must be a primary key and a foreign key on txn_log.txnid

insert all
into txn_log values (txnid, termid, amount)
into messages values (txnid, req_msg, resp_msg)
select 2222 txnid, 1111 termid, 100 amount, 20 amount, 'req_data' req_msg, 'resp_data' resp_msg
from dual;

Regards
Michel

[Updated on: Thu, 28 January 2010 03:19]

Report message to a moderator

Re: Rarely used columns [message #441008 is a reply to message #440955] Thu, 28 January 2010 05:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Thu, 28 January 2010 18:39
Enter in 4NF and split the table in two

Not sure where that wiki entry came from, but I'm pretty sure that's not 4NF. There is a pretty detailed description on Wikipedia that describes 4NF. The one similarity with this discussion is that both involve the separation into multiple tables.

As for the OPs problem, if most access is by those two keys, and those two keys are highly selective, then nothing is to be gained from splitting the table. This would only benefit long range scans (assuming not all queried columns are in the index) and full table scans.

Ross Leishman
Previous Topic: how to calculate cpu usage of database from statspack report
Next Topic: When is execution plan for a package determined (merged)
Goto Forum:
  


Current Time: Tue Dec 06 14:36:30 CST 2016

Total time taken to generate the page: 0.22055 seconds