| Rarely used columns [message #440951] |
Thu, 28 January 2010 01:29  |
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 #440972 is a reply to message #440955] |
Thu, 28 January 2010 02:55  |
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 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 28 January 2010 18:39Enter 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
