8 Replies Latest reply on Mar 15, 2019 6:14 PM by Nick Fury

    table with large number of columns (100 or more)

    Murray Sobol

      SQL Developer 18.3.0.277 Build 277.2354

       

      When I issue this SQL statement:

      select * from a1_user

       

      I get no results  nothing nada!!!

       

      If I issue this sql:

      select user_id from a1_user

      I get the results I expect.

       

      Table definition for a1_user:

       

      CREATE TABLE a1_user
      (
        user_id                        varchar2(40)                not null,
        full_name                      varchar2(50)                not null,
        password                        varchar2(90)                null,
        hide_price_flag                char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_hide_price_flag CHECK (hide_price_flag IN ('Y','N')),
        signature                      varchar2(128)              null,
        password_date                  date        DEFAULT sysdate not null,
        inactive_flag                  char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_inactive_flag CHECK (inactive_flag IN ('Y','N')),
        web_flag                        char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_web_flag CHECK (web_flag IN ('Y','N')),
        add_date                        date        DEFAULT sysdate not null,
        sop_cost_price_flag            char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_sopcost_price_flag CHECK (sop_cost_price_flag IN ('Y','N')),
        language_code                  varchar2(10)  DEFAULT 'EN'  not null,
        e_mail                          varchar2(128)              null,
        override_iv_cost_flag          char(1)      DEFAULT 'Y'  not null
            CONSTRAINT ckc_a1_user_overridivcost_flag CHECK (override_iv_cost_flag IN ('Y','N')),
        abc_flag                        char(10)                    null,
        scale_operator_flag            char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_scaleoperator_flag CHECK (scale_operator_flag IN ('Y','N')),
        workstation_serial_nbr          number(12,0)                null,
        po_receiving_cost_flag          char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_poreceivcost_flag CHECK (po_receiving_cost_flag IN ('Y','N')),
        prophetx_user_id                varchar2(250)              null,
        market_interface_url            varchar2(512)              null,
        allow_override_ot_price_flag    char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_allovridotprc_flag CHECK (allow_override_ot_price_flag IN ('Y','N')),
        access_to_co_bu_flag            char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_accesstocobu_flag CHECK (access_to_co_bu_flag IN ('Y','N')),
        last_login_date                date                        null,
        dollar_limit_serial_nbr        number        DEFAULT 1    not null,
        user_dollar_limit_serial_nbr    number        DEFAULT 0    not null,
        access_to_rel_in_te_flag        char(1)      DEFAULT 'Y'  not null
            CONSTRAINT ckc_a1_user_acctorelinte_flag CHECK (access_to_rel_in_te_flag IN ('Y','N')),
        credit_admin_full_name          varchar2(50)                null,
        credit_admin_flag              char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_creditadmin_flag CHECK (credit_admin_flag IN ('Y','N')),
        credit_admin_inactive_flag      char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_cradminactive_flag CHECK (credit_admin_inactive_flag IN ('Y','N')),
        duplicate_na_flag              char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_duplicate_na_flag CHECK (duplicate_na_flag IN ('Y','N')),
        allow_consolid_customer_flag    char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_allconsolcust_flag CHECK (allow_consolid_customer_flag IN ('Y','N')),
        view_na_pin_number_flag        char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_viewnapinnbr_flag CHECK (view_na_pin_number_flag IN ('Y','N')),
        view_only_na_profile_flag      char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_viewonlynapro_flag CHECK (view_only_na_profile_flag IN ('Y','N')),
        prophetx_interface_password    varchar2(250)              null,
        release_build_info              varchar2(20)                null,
        allow_contact_inactivate_flag  char(1)      DEFAULT 'Y'  not null
            CONSTRAINT ckc_a1_user_allowcntinact_flag CHECK (allow_contact_inactivate_flag IN ('Y','N')),
        production_planner_flag        char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_prod_planner_flag CHECK (production_planner_flag IN ('Y','N')),
        access_to_blank_accr_ven_flag  char(1)      DEFAULT 'Y'  not null
            CONSTRAINT ckc_a1_user_accblkaccrved_flag CHECK (access_to_blank_accr_ven_flag IN ('Y','N')),
        stdio_windows_service_flag      char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_stdio_winserv_flag CHECK (stdio_windows_service_flag IN ('Y','N')),
        stdio_ws_server_name            varchar2(256)              null,
        stdio_last_run                  date                        null,
        default_search_method          char(1)      DEFAULT 'S'  not null,
        name_search_chars              number(5)    DEFAULT -1    not null,
        name_contains_search_chars      number(5)    DEFAULT 3    not null,
        city_search_chars              number(5)    DEFAULT 3    not null,
        commodity_search_chars          number(5)    DEFAULT 0    not null,
        product_search_chars            number(5)    DEFAULT 0    not null,
        formula_search_chars            number(5)    DEFAULT 0    not null,
        commodity_contains_chars        number(5)    DEFAULT 3    not null,
        commodity_default_srch_method  char(1)      DEFAULT 'L'  not null,
        formula_default_search_method  char(1)      DEFAULT 'S'  not null,
        origin_dest_search_chars        number(5)    DEFAULT 3    not null,
        query_access_mpc_contract_flag  char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_qry_acsmpccon_flag CHECK (query_access_mpc_contract_flag IN ('Y','N')),
        city_default_search_method      char(1)      DEFAULT 'S'  not null,
        city_contains_search_chars      number(5)    DEFAULT 3    not null,
        formula_contains_search_chars  number(5)    DEFAULT 3    not null,
        origin_dest_search_method      char(1)      DEFAULT 'S'  not null,
        origin_dest_contain_srch_chars  number(5)    DEFAULT 3    not null,
        name_search_criteria_1          varchar2(30)                null,
        name_search_criteria_2          varchar2(30)                null,
        name_search_criteria_3          varchar2(30)                null,
        name_search_criteria_4          varchar2(30)                null,
        commodity_search_criteria_1    varchar2(30)                null,
        commodity_search_criteria_2    varchar2(30)                null,
        commodity_search_criteria_3    varchar2(30)                null,
        commodity_search_criteria_4    varchar2(30)                null,
        pb_build_number                number                      null,
        pb_fixes_revision              number                      null,
        pb_major_revision              number                      null,
        pb_minor_revision              number                      null,
        screen_width                    number                      null,
        screen_height                  number                      null,
        payment_splits_flag            char(1)      DEFAULT 'Y'  not null
            CONSTRAINT ckc_a1_user_paymentsplits_flag CHECK (payment_splits_flag IN ('Y','N')),
        tkt_charges_adj_storage_flag    char(1)      DEFAULT 'Y'  not null
            CONSTRAINT ckc_a1_user_tktchgadjstor_flag CHECK (tkt_charges_adj_storage_flag IN ('Y','N')),
        agtech_doc_design_access_flag  char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_docdesignacc_flag CHECK (agtech_doc_design_access_flag IN ('Y','N')),
        asm_in_frame_flag              char(1)      DEFAULT 'N'  not null
            CONSTRAINT ckc_a1_user_asm_in_frame_flag CHECK (asm_in_frame_flag IN ('Y','N')),
        user_sid                        varchar2(80)                null,
        CONSTRAINT pk_a1_user PRIMARY KEY (user_id)
            using index
            tablespace smartsoft_index
      )
      tablespace smartsoft_data
      /

        • 1. Re: table with large number of columns (100 or more)

          Ok - I don't see how this is connected with sql developer?

           

          Do you get the same result using sql*plus?

           

          Do you get the same result using a user with DBA privileges?

          When I issue this SQL statement:

          select * from a1_user

           

          I get no results  nothing nada!!!

          Well - truth be told we have NO WAY of knowing:

           

          1. if that table has ANY data in it

          2. if there is a VPD policy that doesn't allow the user you are using access to the data in the table

           

          If I issue this sql:

          select user_id from a1_user

          I get the results I expect.

          And what 'results' are those?

           

          You haven't really given us ANYTHING to go on here.

           

          Do tests with other users and show us why this is an issue for the sql developer forum.

           

          If you still have an issue then SHOW US:

           

          1. WHAT you do

          2. HOW you do it

          3. WHAT results you get

          • 2. Re: table with large number of columns (100 or more)
            Murray Sobol

            rp0428:  your reply is NOT helpful in ANY WAY!!!

            Please refrain from responding to my posts since you have NOTHING to offer!!!

            If you re-read my post the information IS THERE!!

            The table definition is provided; results should be provided whether or not data is present.

            i.e; 0 rows returned or results returned.

            • 3. Re: table with large number of columns (100 or more)
              Glen Conway

              On 18.3 against Oracle 11g XE, only changing your 2 tablespace references to something I have (e.g., USERS), both SELECT * and SELECT user_id work as expected.  Must be something in your environment.

               

              Edit:  You may want to reconsider the advice rp0428 offers.

              • 4. Re: table with large number of columns (100 or more)
                thatJeffSmith-Oracle

                you haven't given us much to go on - we need more info to help you

                 

                give us a few inserts to play with, i don't have time to write a 100 column insert statement to test with

                • 5. Re: table with large number of columns (100 or more)
                  Glen Conway

                  Yes, explicit data might help the investigation.  The test case you provide has 76 columns and only the most typical of data types: CHAR, NUMBER, DATE, VARCHAR2.  Initially I thought you might have something more complex.  Is there anything showing up under View -> Log in the Messages or Logging Page tabs?

                  • 6. Re: table with large number of columns (100 or more)

                    rp0428: your reply is NOT helpful in ANY WAY!!!

                    Then you should read it over and over until you understand what I ask you to do is EXACTLY what is needed to help you.

                    If you re-read my post the information IS THERE!!

                    No it isn't - there are NO RESULTS shown for either query.

                     

                    And that is CORRECT if there is no data in the table or you don't have privileges on the data you are querying.

                    The table definition is provided; results should be provided whether or not data is present.

                    i.e; 0 rows returned or results returned.

                    I agree - 'results should be provided'

                     

                    Except you haven't SHOWN US any 'results' for either query.

                     

                    And you haven't executed the test for a DBA user so we can see if a privileged user gets different results.

                     

                    For all we know the second query doesn't produce any results either and the table is really empty.

                     

                    The key difference between the two queries is that the second only selects the 'USER_ID' and Oracle could get those values from the primary key index without accessing the table. But the first query selects ALL columns and requires a full table scan.

                     

                    You could have seen that yourself if you had created and reviewed the two execution plans. And if you had posted both plans we could have pointed it out to you.

                     

                    Post the info needed to help you - we can't guess what is in your wrapped package under the Christmas tree.

                    • 7. Re: table with large number of columns (100 or more)
                      Murray Sobol

                      I am not pursuing this question.

                      consider the case closed.r

                      • 8. Re: table with large number of columns (100 or more)
                        Nick Fury

                        How many sessions to you have open that you are running this sql from?

                         

                        How many objects in the database with the name a1_user?

                         

                        Just a thought...