4 Replies Latest reply on Feb 26, 2020 11:16 PM by 3009602

    Can we overcome the limiation of no.of columns with JSON document

    3009602
    3009602 Feb 25, 2020 5:23 PM

      Hi,

       

      We have a requirement to create table in Oracle database with unlimited no. of columns.   Since there is a limitation on no. of columns, I am thinking that whether we can overcome that issue by using JSON document got introduced in 12c.   We have a table called PRCA_DIM_KROGER_I and we need to create more than 1000 columns for that. 

       

      Please let us know if that is possible, if so are there any issues in terms of performance while accessing or ingesting the data. 

       

      your inputs are very much appreciated.

       

      Regards

      ~Uppal.

       

      CONSTRAINT "PRCA_DIM_KROGER_1_pr_custatt_dim_key_pkey" PRIMARY KEY ("pr_custatt_dim_key")

      CONSTRAINT "PRCA_DIM_KROGER_1_pr_custatt_dim_key_pkey" PRIMARY KEY ("pr_custatt_dim_key")

      • 72 Views
      • Tags:
        • 1. Re: Can we overcome the limiation of no.of columns with JSON document
          thatJeffSmith-Oracle
          thatJeffSmith-Oracle Feb 25, 2020 5:46 PM (in response to 3009602)

          a table with a clob/blob with the IS_JSON constraint will give you the ability to stuff as much data in there as you want

           

          performance issues will be determined by how good your data model and SQL is

           

          Having that many columns in a relational world begs the question re: your design, that could or even should be normalized.

          • 2. Re: Can we overcome the limiation of no.of columns with JSON document
            3009602
            3009602 Feb 25, 2020 6:13 PM (in response to thatJeffSmith-Oracle)

            Thank you Jeff for your quick reply.  I know, it is a matter of Normalization, but our data engineers went with MonetDB instead of looking at design and all.   Since MonetDB is an open source,  I am just finding a way to replace MonetDB with oracle.    Basically we use that table for analytical queries and below is sample query.    Do you see any performance issues to run this kind of queries from JSON document stored in a table?

             

            select sum("PRCA_DIM_KROGER_1".s_214_key), "PRCA_DIM_KROGER_1".pr_custatt_dim_key fr

            om "PRCA_DIM_KROGER_1" where (("PRCA_DIM_KROGER_1".s_206_key not in (13)) and ("PRCA

            _DIM_KROGER_1".s_204_key not in (86, 552)) and ("PRCA_DIM_KROGER_1".s_206_key not in

            (9)) and ("PRCA_DIM_KROGER_1".s_206_key not in (6)) and ("PRCA_DIM_KROGER_1".s_206_

            key not in (7)) and ("PRCA_DIM_KROGER_1".s_208_key not in (30000)) and ("PRCA_DIM_KR

            OGER_1".s_202_key not in (5)) and ( (4=4) and (("PRCA_DIM_KROGER_1".s_201_key = 2) a

            nd ("PRCA_DIM_KROGER_1".s_206_key = 14) and ("PRCA_DIM_KROGER_1".s_202_key = 79) and

            ("PRCA_DIM_KROGER_1".s_204_key = 297) and ("PRCA_DIM_KROGER_1".s_215_key = 630)))) 

            group by "PRCA_DIM_KROGER_1".pr_custatt_dim_key                                     

            ;                                                                                   

             

            Regards

            ~Uppal

            • 3. Re: Can we overcome the limiation of no.of columns with JSON document
              thatJeffSmith-Oracle
              thatJeffSmith-Oracle Feb 26, 2020 2:31 PM (in response to 3009602)

              There are too many variables involved to give you any sort of answer that would be useful to you.

               

              I will say there are many optimizations built into the Oracle Database that can overcome design issues or even bad SQL.

               

              But why hazard a guess, when you can spin up a new Oracle DB in a few minutes, load your data, and give it a try yourself?

               

              Some things you wrote for MonetDB (had never heard of it till you mentioned it, but cool), might need re-written for Oracle to fully take advantage of Oracle features. So be prepared for that opportunity.

               

              I made your query easier to read, Oracle SQL Developer has a SQL Formatter for you...

               

              SELECT SUM("PRCA_DIM_KROGER_1".s_214_key),

                     "PRCA_DIM_KROGER_1".pr_custatt_dim_key

                FROM "PRCA_DIM_KROGER_1"

              WHERE ( ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 13 ) )

                 AND ( "PRCA_DIM_KROGER_1".s_204_key NOT IN ( 86, 552 ) )

                 AND ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 9 ) )

                 AND ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 6 ) )

                 AND ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 7 ) )

                 AND ( "PRCA_DIM_KROGER_1".s_208_key NOT IN ( 30000 ) )

                 AND ( "PRCA_DIM_KROGER_1".s_202_key NOT IN ( 5 ) )

                 AND ( ( 4 = 4 )

                 AND ( ( "PRCA_DIM_KROGER_1".s_201_key = 2 )

                 AND ( "PRCA_DIM_KROGER_1".s_206_key = 14 )

                 AND ( "PRCA_DIM_KROGER_1".s_202_key = 79 )

                 AND ( "PRCA_DIM_KROGER_1".s_204_key = 297 )

                 AND ( "PRCA_DIM_KROGER_1".s_215_key = 630 ) ) ) )

              GROUP BY "PRCA_DIM_KROGER_1".pr_custatt_dim_key;

              ;

               

              and 4=4 -- that will always evaluate to 'TRUE', what's that in there for?

               

              Some of your other predicates could be condensed

               

              AND ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 9 ) )

              AND ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 6 ) )

               

              could just be

               

              AND ( "PRCA_DIM_KROGER_1".s_206_key NOT IN ( 6,9 ) )

               

              But I don't see our optimizer tripping over things like that to be honest.

              • 4. Re: Can we overcome the limiation of no.of columns with JSON document
                3009602
                3009602 Feb 26, 2020 11:16 PM (in response to thatJeffSmith-Oracle)

                Thank you for valuable inputs.  These queries generated by the Analytical server so I am not sure how to change them yet.    However, I am going to spin up oracle and try it and see how it goes.

                 

                Thank you again.