Question Locked

How can I create Oracle view that splits varchar into multiple columns?

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

A dblinked table contains a varchar2(500 bytes) column, which contains a series fixed-length set of values, all concatenated into this one varchar. I need to create a local view of this dblinked table that breaks the fixed-length values into distinct columns. For example, say the original varchar column is composed of:

Name Len Offset Type
CUST_NAME 20 0 Char
SENIORITY 3 20 Num
CONCESSION_IND 4 23 Char

I need to make my local view actually break out these three values into separate columns. (I actually need to break out 126 columns from the original varchar2(500 byte) column).

Can anyone suggest an efficient way to do this? If I create some elaborate select that defines my view by picking out each field, will it perform reasonably well, or will it be painfully slow to select data from?

4 replies

by tony hopkinson

Even if you had a table with all teh data broken out already, 126 indexes on it so you could se

by tony hopkinson

or simply select the view.
Some massive select statement to break out each substring as a column, is goingbto hit you when parsing it and of course depend on the number of rows. If teh field in the where clauses are indexed, that's going to be OK

You real problem iss when you start selecting from the view itself even if you index the view, (126 indexes is not going to be funny is it).

Personally I'd start investigating the uses for this data, and may be having several views of parts of it. Or I'd say f'it and convert the entire thing into a real table and go from there.

by marv732

might be a good way to go.

If the origin db is dbA and your local one is dbB

Create a materialized view with refresh on commit in dbA (which you can then index) as well as a snapshot log on the origin table.
Then create a view in dbB which is select * from mat_view@dbA where ... whatever.

The materialized view will then update only those rows which change in the origin table thus reducing the overhead and the view is then a straight forward select with maybe a driving_site hint if required for performance.

However you do it you will still have to write the initial big mother you_know_what of a select statement but this way you might get access to the data from the origin db before the end of the year at least.

HTH
Greg

This discussion is closed to new replies.