Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Question Unanswered: Approach for storing huge data with varying attributes

    We have a requirement in which we have very large amount of varying column headers and data (i.e. In Mbs).

    The design what we are following now for storing the data ,is having a table which is updated dynamically based on the varying datas , where both the table columns and the rows are updated (i.e. Both the table columns and the table rows are growing) .But the potential problem what we are facing with this is, because of the limitations of oracle to have a maximum of 1000 columns.

    That is after 1000 columns we are forced to create a new table because the table columns exceed 1000 .

    Any Design Pattern is there where we can have more than 1000 columns and the datas for the respective columns stored in some special pattern in oracle ??

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Approach for storing huge data with varying attributes

    Well, if you really need more than 1000 columns in a table then you will have to vertically partition into 2 or more tables:

    TABLE1( id, col1, ..., col1000 );
    TABLE2( id, col1001, ..., col2000 );
    etc.

    You then join these tables on ID to get the full set of columns.

    I have to say, though, that if you have a requirement for more than 1000 columns in a table, then you must be doing something very strange, and I would bet you are not doing it the best way. Handling 100s of columns in a table would be a nightmare; handling 1000s is suicidal. If you explain what you are doing in more detail, perhaps someone can suggest a better solution.

  3. #3
    Join Date
    Oct 2002
    Posts
    3

    Thumbs up Re: Approach for storing huge data with varying attributes

    Originally posted by andrewst
    Well, if you really need more than 1000 columns in a table then you will have to vertically partition into 2 or more tables:

    TABLE1( id, col1, ..., col1000 );
    TABLE2( id, col1001, ..., col2000 );
    etc.

    You then join these tables on ID to get the full set of columns.

    I have to say, though, that if you have a requirement for more than 1000 columns in a table, then you must be doing something very strange, and I would bet you are not doing it the best way. Handling 100s of columns in a table would be a nightmare; handling 1000s is suicidal. If you explain what you are doing in more detail, perhaps someone can suggest a better solution.
    Thanks Tony

    Let me explain u more

    we have the requirement where we need to store MBs of file .and the data what we have to store is dynamic and it can go on increasing to any size.That is the column headers and the rows of the table.

    One way what we used was storing the columns as rows. but when we store the columns as rows then the amount of time taken to retrieve the data is quite high. so we had to change this approach

    The present approach what we are using is storing it in a table which dynamically grows.But this creates scalability issue of 1000 columns.

    Do u have any other approaches

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Approach for storing huge data with varying attributes

    Originally posted by nikhil
    Thanks Tony

    Let me explain u more

    we have the requirement where we need to store MBs of file .and the data what we have to store is dynamic and it can go on increasing to any size.That is the column headers and the rows of the table.

    One way what we used was storing the columns as rows. but when we store the columns as rows then the amount of time taken to retrieve the data is quite high. so we had to change this approach

    The present approach what we are using is storing it in a table which dynamically grows.But this creates scalability issue of 1000 columns.

    Do u have any other approaches
    None other than the approach I suggested earlier. Does that one not work for you?

    When I asked for more detail, I really meant: why are you doing this - what is the actual requirement? All you have told me is that you have a requirement to store 1000s of columns of data. What I want to understand, in the hope of being able to suggest an alternative approach, is WHY do you have such a requirement? What kind of data is this?

    What you want to do is so "off the wall" (clearly Oracle doesn't expect its customers to have 1000+ column tables, and I really can't imagine a situation where I would want them) that I can only imagine that either you have requirements for which an RDBMS is not suitable, or you just haven't come up with an appropriate design yet. I just cannot imagine:
    1) what you would name the 1999th column (apart from something like COL_1999
    2) how you would keep track of which column meant what
    3) how you would write a SELECT statement against that table, other than via generated code.

    More information please!

  5. #5
    Join Date
    Oct 2002
    Posts
    3
    The requirement goes like this:

    We have to store data from many csv files into a central repository to get a uniform view and to enable fast queries.Each csv file contains information about an entity with number of attributes
    e.g.
    1.csv has Fname,Lname,.....
    2.csv has Name,Age,.....

    The approach we follow is to add a column to a table for each new attribute encountered. using this approach if we reach 1000 columns than we have a problem.

    We thought of an alternative approach where we store
    name value pairs for each entity, this approach hits the performance
    of the system since the queries would be horribly slow as it has to access multiple rows to get a single entity.

    so please suggest us an alternative approach

    warm regards
    Nikhil

  6. #6
    Join Date
    Apr 2012
    Posts
    1

    Explanation

    Hi All,

    Whith the growing amount of available statistical data there is indeed a great need for tables with more than 1000 columns. We are also facing this problem: we receive txt files of 1500-3000 columns of data from a statistical body, and our statisticians would need to use all the columns for their modelling work (when you start modelling you do not know which variables will be useful for your model: you only learn this during the work by analyzing the relations between the variables). Stata - the statistical modelling application our statisticians use - can handle as many columns as necessary, but we would still like to use the Oracle database for the storing of the data (to make sure that data is not lost, any changes to the data are logged, and different versions of the data are stored properly, ect...). Pivoting the data in the database to (rowid, columnid, value) format would work for storing, but then the issue of repivoting should be addressed. Which is impossible in Oracle (since no views or materialized views may have more than 1000 columns), and very very time consuming in Stata.

    If anyone has found a solution for this problem already, please reply.

    Best, Zita

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could always create a collection (table type) object which has array of column name and value.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by LKBrwn_DBA View Post
    You could always create a collection (table type) object which has array of column name and value.
    Here is a simple example:
    Code:
    CREATE OR REPLACE TYPE attr_rec_typ IS OBJECT
    (
      attr_name VARCHAR2( 30 ),
      attr_type CHAR( 1 ),
      attr_value VARCHAR2( 100 )
    )
    /
    
    CREATE OR REPLACE TYPE attr_arr_typ IS TABLE OF attr_rec_typ
    /
    
    CREATE TABLE attr_tab
    (
      pk             NUMBER,
      col2           VARCHAR2( 10 ),
      col3           VARCHAR2( 20 ),
      attr_arr       attr_arr_typ     --< Here you got your 1000+ columns/values
    )
    NESTED TABLE attr_arr
      STORE AS attr_arr_nt
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •