Select Where Date = 201601 vs '201601'

447cdf6a-c51c-4e2f-b1b4-4d84d3a339f9

    In sql developer, 3.3.20.10:

    What is the diff between these and why do they both seem to work?  The 2nd is what I'm using but the statement has been running for hours and seems to be stuck.  I'm trying to delete over 5 million rows for a table of 80 million

     

    DELETE

    FROM TABLE

    WHERE FISCAL_WK >= '201601'

     

    VS.

     

    DELETE

    FROM TABLE

    WHERE FISCAL_WK >= 201601

     

    Ben

      • 1. Re: Select Where Date = 201601 vs '201601'
        johngarmon

        How is fiscal_wk defined?

        john3

        1 位用户发现它有用
        • 2. Re: Select Where Date = 201601 vs '201601'
          447cdf6a-c51c-4e2f-b1b4-4d84d3a339f9

          It is NUMBER (6,0)

          It represents the weeks of the year - YYYYMM for example.

          Thanks in advance.

          Today, I still have not been able to complete either DELETE and wondering why as I should be the admin permissions in my tables.

          I'm wondering also, now if DELETES are heavier scans, than say INSERTS, in general.

          1 位用户发现它有用
          • 3. Re: Select Where Date = 201601 vs '201601'

            First - your question has NOTHING to do with sql developer so you have posted in the wrong forum.

             

            Please mark the thread ANSWERED and repost it in the proper forum

            SQL & PL/SQL

             

             

            What is the diff between these and why do they both seem to work?  The 2nd is what I'm using

            The first is a number and the second is a string.

            It is NUMBER (6,0)

            It represents the weeks of the year - YYYYMM for example.

            They will both 'work' because Oracle will perform implicit conversions of the string to a number so it can perform the comparison.

             

            See the implicit conversion matrix table in the docs

            https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements002.htm#g195937

             

            Among other mistakes your org is making:

             

            1. You are NOT using a DATE datatype to store a date - why not? You should ALWAYS use the correct datatype.

            2. You are comparing a 'number' to a string - forcing Oracle to convert the string to a number for comparison.

             

            Today, I still have not been able to complete either DELETE and wondering why as I should be the admin permissions in my tables.

            I common cause is when someone THINKS the delete isn't working and kills the session. That causes Oracle to have to UNDO all of the delete work it has done. Doing that can often take 5-10 times as long as the delete would have taken to finish.

             

            I'm wondering also, now if DELETES are heavier scans, than say INSERTS, in general.

            What is a 'heavier' scan? Oracle has to:

             

            1. find each row to be deleted

            2. copy the block to UNDO so it can 'undo' the delete if needed later

            3. delete the row

            4. write the REDO info to the REDO logs

             

            There may NOT be an appropriate index that can be used. Or the cost of using that index to delete 5/80 (~ 7%) of the rows is higher than a full table scan so it uses the FTS.

             

            If you have been interrupting the delete because you are tired of waiting then you are likely the one causing the excessive delay by causing Oracle to have to undo the work it has done.

            1 位用户发现它有用
            • 4. Re: Select Where Date = 201601 vs '201601'
              BrunoVroman

              Hello Ben,

               

              <<<It is NUMBER (6,0) It represents the weeks of the year - YYYYMM for example.>>>>

               

              a) as it is a NUMBER, you should also use a NUMBER in the statement: WHERE FISCAL_WK >= 201601

               

              b) YYYYMM is probably not appropriate: there are more than 12 values (months) for a given year, rather something like 53 (weeks), like for example 201615

               

              c) as the week number is not a DATE it makes sense not to use a DATE, but still, I'm not really happy with a number... Well, to be discussed, probably with people used to DataWarehouses to know best practice about the way to store "week number".

               

              d) maybe something to investigate: the use of PARTITIONING (beware: Entreprise Edition + extra license costs)? Would you have one partition per week, deleting millions of rows would simply be dropping partitions.

               

              Best regards,

               

              Bruno Vroman.

              1 位用户发现它有用
              • 5. Re: Select Where Date = 201601 vs '201601'
                BPeaslandDBA

                The 2nd is what I'm using but the statement has been running for hours and seems to be stuck. 

                 

                Let me guess...FISCAL_WK is defined as a character datatype....The second one is using a NUMBER and Oracle has to do a type conversion, in which case it supresses any index on that column.

                 

                Chers,

                Brian