1 2 3 Previous Next 36 Replies Latest reply: Dec 14, 2012 6:42 AM by ranit B Go to original post RSS
      • 15. Re: finding length of column
        Rahul_India
        user10302525 wrote:
        if it is the case that data should not be more than 10 then you should have created table with restriction on column length, even if you fix the data now, it can always come back. So after you do this cleanup you will have to fix the structures to avoid the problem coming again.
        agreed .

        newbie :

        just take a back up of your table
        create table emp_bck as select *from emp
        drop emp.then recreate table with check constraint and dump the data from emp_back to emp.
        Data length greater than 10 will be rejected
        • 16. Re: finding length of column
          newbie
          C wrote:
          if it is the case that data should not be more than 10 then you should have created table with restriction on column length, even if you fix the data now, it can always come back. So after you do this cleanup you will have to fix the structures to avoid the problem coming again.
          Yes you are right, we are doing loading from source to destination tables. The destination table has the limitation that the length is 10.
          When loading is done we are find the column of null values in destination table which got rejected by the size more than 10.

          Here we are checking the source data which have more than size 10. Hope you may clear.

          Thanks and Regards
          • 17. Re: finding length of column
            newbie
            Hi Rahul

            Please read my updated post for clarification.

            Thanks and regards
            • 18. Re: finding length of column
              Mahir M. Quluzade
              newbie wrote:
              user10302525 wrote:
              what is it that you want to do after finding such records? and since your table is huge full scan will take time.
              Hi

              That is a business rule that column shouldn't have length more than 10 as per standards. Hence we are finding those records and modify or cleaning could be done.

              Regards
              I didn't read this reply.

              You can add new column size 10, and update this column values from SUBSTR(ename,10) or wht is you want. and drop column from table and change name of new column to droped column name
              .
              • 19. Re: finding length of column
                Rahul_India
                newbie wrote:
                Hi Rahul

                Please read my updated post for clarification.

                Thanks and regards
                Ok.
                So just do it for your source table.
                I dont see any problem in that.
                • 20. Re: finding length of column
                  BluShadow
                  newbie wrote:
                  We shouldnt create the index. Please let me know is there other solution.
                  If you don't create an index, then the optimiser has no other means of determining which columns meet your criteria, apart from doing a full table scan of all the data.
                  That's the point of indexes.
                  You want to improve access... create an index. You don't want to create an index... you don't improve access. simple as that.
                  • 21. Re: finding length of column
                    newbie
                    >
                    I didn't read this reply.

                    You can add new column size 10, and update this column values from SUBSTR(ename,10) or wht is you want. and drop column from table and change name of new column to droped column name
                    .
                    We have that column and other ID column i.e(uni_code (which have length more than 10) and uni_id these 2 cols), here we need to find out those ID's which have the uni_code more than length 10.

                    We want those ID's and uni_code which have size more than 10.
                    • 22. Re: finding length of column
                      Mahir M. Quluzade
                      BluShadow wrote:
                      newbie wrote:
                      We shouldnt create the index. Please let me know is there other solution.
                      If you don't create an index, then the optimiser has no other means of determining which columns meet your criteria, apart from doing a full table scan of all the data.
                      That's the point of indexes.
                      You want to improve access... create an index. You don't want to create an index... you don't improve access. simple as that.
                      No Comment!
                      • 23. Re: finding length of column
                        976379
                        Hi,
                        You can create a temporary index on the column and then remove the same after your task is done.

                        for restricting the column you can create a check constrain after the cleaning of the data.

                        If you dont want to create any constrain then you can implement the same by creating a trigger too ( not a very good idea) .

                        Thanks.
                        • 24. Re: finding length of column
                          newbie
                          BluShadow wrote:
                          newbie wrote:
                          We shouldnt create the index. Please let me know is there other solution.
                          If you don't create an index, then the optimiser has no other means of determining which columns meet your criteria, apart from doing a full table scan of all the data.
                          That's the point of indexes.
                          You want to improve access... create an index. You don't want to create an index... you don't improve access. simple as that.
                          Hi BluShadow

                          Thank you very much for your reply.

                          The interesting thing is that when we execute the below query to find out the max slength of column against on that table it took just 10min.
                          select max(length(UNI_CODE)) as MAXLENGHTH from emp@AUS where rownum<600000000;
                          But when i fire the below query is still didnt executed it is more than 6hr's now. :(
                          select uni_id from emp@AUS where length(UNI_CODE)>10
                          Thanks and Regards
                          • 25. Re: finding length of column
                            newbie
                            KriC wrote:
                            Hi,
                            You can create a temporary index on the column and then remove the same after your task is done.

                            for restricting the column you can create a check constrain after the cleaning of the data.

                            If you dont want to create any constrain then you can implement the same by creating a trigger too ( not a very good idea) .

                            Thanks.
                            Hi KriC

                            Thanks for your reply. I didnt get you clearly. You mean to say that i need to created index temporarily and then remove later?

                            Thanks
                            • 26. Re: finding length of column
                              Mahir M. Quluzade
                              You can drop, B-TREE index or Temporary index every time, if you not need.
                              But I dont recomend use trigger, because you have very large tables.
                              • 27. Re: finding length of column
                                newbie
                                Mahir M. Quluzade wrote:
                                Then you must create function based index on table, for performance.
                                create index emp_length_idx on emp(length(ename));
                                and you can get result
                                select count(ename) from emp where length(ename)>10
                                Mahir please help the table is on the db link. how can i create index then?

                                Thanks
                                • 28. Re: finding length of column
                                  Mahir M. Quluzade
                                  Can you try this ?
                                  create index emp_length_idx@db_link on emp@db_link(length(ename));
                                  Paste result here, please!

                                  Edited by: Mahir M. Quluzade on Dec 13, 2012 5:33 PM
                                  • 29. Re: finding length of column
                                    Mahir M. Quluzade
                                    I think you are already get error :
                                    SQL Error: ORA-02021: DDL operations are not allowed on a remote database
                                    02021. 00000 -  "DDL operations are not allowed on a remote database"