This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: Dec 14, 2012 4:42 AM by ranit B Go to original post RSS
  • 15. Re: finding length of column
    Rahul_India Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Hi Rahul

    Please read my updated post for clarification.

    Thanks and regards
  • 18. Re: finding length of column
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    >
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points