11 Replies Latest reply on Dec 2, 2012 4:19 PM by rp0428

    partitioning a table

    nohup
      this is related to
      tuning regexp_like
      the above link does not seem to work, please search for tuning regexp_like by author 946207 thank you

      starting a new thread because partitioning questions are asked in "General questions" forum
      1)can the optimizer be told a column is ordered alphabetically and hence avoid full table scan
      2)can we partition a table alphabetically
      the table is 67000 rows
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      thanks

      Edited by: 946207 on Dec 1, 2012 11:28 AM

      Edited by: 946207 on Dec 2, 2012 11:03 PM
        • 1. Re: partitioning a table
          Osama_Mustafa
          Duplicate thread
          https://forums.oracle.com/forums/thread.jspa?threadID=2470519&tstart=0
          • 2. Re: partitioning a table
            Osama_Mustafa
            1)can the optimizer be told a column is ordered alphabetically and hence avoid full table scan
            Hope this link is useful :
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:9422487749968
            2)can we partition a table alphabetically
            The best solution to do this is RANGE Partition .

            PARTITION BY RANGE COLUMNS(fname) (
                PARTITION p_1 VALUES LESS THAN ('A'),
                PARTITION p_2 VALUES LESS THAN ('B'),
                PARTITION p_3 VALUES LESS THAN ('C'),
            .......
            
            
            );
            • 3. Re: partitioning a table
              mtefft
              You ask:
              can the optimizer be told a column is ordered alphabetically and hence avoid full table scan

              It is important to note that there is no true sense of 'rows being ordered in a table' unless you are using an index-organized table.

              For a heap-organized table (the default type), you might think you inserted the rows in order, but Oracle is free to put them wherever it sees fit. And even if they were 'in order' at the time you inserted them, later inserts, updates or deletes could change that. So you should not presume to be able to 'tell the optimizer' something that you can not be sure is true.
              1 person found this helpful
              • 4. Re: partitioning a table
                rp0428
                >
                It is important to note that there is no true sense of 'rows being ordered in a table' unless you are using an index-organized table.
                >
                WRONG! WRONG! WRONG!

                There is no ordering of rows in a table! Only an ORDER BY can ensure that Oracle returns rows in any given order for ANY table including index-organized tables.

                See this definitive article in 'The Tom Kyte Blog' where he, once again, debunks this myth.
                http://tkyte.blogspot.com/2005/08/order-in-court.html
                >
                As I was proofing my chapters yesterday, I couldn't help but notice that in three chapters I said the same thing, stronger and stronger each time. It was about using (or not using more specifically) ORDER BY. I see it time and time and time again.Someone assumes that because they observe the data coming out sorted from a query or that a query is using an index - they can and should leave off the ORDER BY. That is a career limiting move to borrow a phrase from Connor McDonald (he used it in reference to reorganizing your database but it applies totally here). It is a time bomb ticking away in your code.

                Did you know simple optimizer change - no more sort. Data could appear to be sorted today, statistics gathered and not sorted tomorrow.

                Did you know that reading data out via an index - does not imply data is sorted by the index key!
                >
                And just in case anyone missed that last sentence
                >
                Note Do not be tempted to ever leave an ORDER BY off a query. Just because your query plan includes an index does not mean the data will be returned in "some order." The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query.

                There is no substitute for ORDER BY.
                • 5. Re: partitioning a table
                  rp0428
                  >
                  1)can the optimizer be told a column is ordered alphabetically and hence avoid full table scan
                  2)can we partition a table alphabetically
                  >
                  1) Yes and No - an index on the column will tell the optimizer that the column is ordered alplhabetically. But that doesn't mean the CBO will use the index. And even if it uses the index it doesn't mean the results will be ordered according to the index.

                  See the article by Tom Kyte I provided the link for above.
                  >
                  Did you know that reading data out via an index - does not imply data is sorted by the index key!
                  >

                  2) No - you partition a table by one or more partition keys. The partitioning is done based on the value of the key, not by the order of the keys. You can create a partitioned table that doesn't have any primary key or index
                  CREATE TABLE FOO_TEST
                  (
                    EVENT_TIME  VARCHAR2(18 BYTE),
                    EVENT_TYPE  NUMBER(38)
                  )
                  PARTITION BY RANGE (EVENT_TIME)
                  (  
                    PARTITION EVT_2011 VALUES LESS THAN ('20120101000000')
                  )
                  Even if there were an index or primary key that would not affect the data itself. Rows in a table are like balls in a basket; there is no 'first' row.
                  • 6. Re: partitioning a table
                    nohup
                    thanks for the response mtefft
                    i am aware of that arrangement(rather not so arranged) of rows by Oracle
                    which is why i have also mentioned IOT in the other thread
                    :
                    rpo428,
                    thanks
                    ok there is no substitute for order by
                    what i mean is let the basket be in this way
                    basket 1 has only balls that start with a
                    basket 2 has only balls that start with b and so on
                    hence be streamlined
                    may be partition the index
                    there has to be some solution to this by Oracle Corp.
                    this is an enterprise edition database after all

                    Edited by: 946207 on Dec 1, 2012 11:14 AM
                    • 7. Re: partitioning a table
                      rp0428
                      First, when you post related threads you should cross-link them so people have access to all of the information about the problem you are trying to work with.
                      Re: partitioning(range) a table values less than 'A'

                      Some people have already been trying to help you in that other thread and anyone reading this thread needs to have the context that the other thread provides.
                      >
                      what i mean is let the basket be in this way
                      basket 1 has only balls that start with a
                      basket 2 has only balls that start with b and so on
                      hence be streamlined
                      may be partition the index
                      there has to be some solution to this by Oracle Corp.
                      this is an enterprise edition database after all
                      >
                      And I just posted more information about that in your other thread. That is the problem when you post multiple threads dealing with the same subject. If you don't cross-link them no one can follow what is happening with your issue.

                      You have to partition on a 'value' of something. That value can be one or more actual columns. Or the value can be a 'virtual' column based on a function that is computeds on an actual column.

                      But there is no 'out-of-the-box' partitioning for values that start with alphabetic characters. You need to build you own function.

                      Yes, you could create a global, functional index but then your queries would need to use that same function in the predicate or the index could not be used. A global index also affects partition maintenance performance such as when you want to drop a partition.

                      And, as I discuss in your other thread, depending on the character set there are characters that sort before, between and after both the lowercase alphabetics and the upper case alphabetics. Your partitioning scheme has to have some partition for ALL data to go or any INSERT you perform will fail.

                      I suggest you select one of these two threads to continue with and mark the other one ANSWERED so people don't have to duplicate their replies in each thread.

                      So far you haven't really provide a clear explanation in either thread of just what problem you are trying to address or why you think you need to partition 'alphabetically' to deal with those problems. It would be helpful if you provided some sample data and showed what types of queries you expect to execute and what problems you think there will be.
                      • 8. Re: partitioning a table
                        nohup
                        after creating partitions will the optimizer go for a full table scan or just scan the partitions
                        regarding the virtual column suggestion:the search will be on the column prod_details
                        the partition clause will have the name of the virtual column and not prod_details
                        the whole endeavor is to let the optimizer know about returning results fast
                        its not a 2gig table
                        its a 5mb table
                        • 9. Re: partitioning a table
                          rp0428
                          >
                          his is related to
                          Re: tuning regexp_like
                          the above link does not seem to work, please search for tuning regexp_like by author 946207 thank you
                          >
                          Of course it won't work. Don't you see that extra box-looking character you put at the end ot it? If you edit your original post and remove that character the link works just fine.
                          >
                          after creating partitions will the optimizer go for a full table scan or just scan the partitions
                          regarding the virtual column suggestion:the search will be on the column prod_details
                          the partition clause will have the name of the virtual column and not prod_details
                          the whole endeavor is to let the optimizer know about returning results fast
                          its not a 2gig table
                          its a 5mb table
                          >
                          The optimizer alreadys knows about 'returning results fast' - that is what it is designed to do. It does that best when it has all of the relevant information in the form of current stats and possibly histograms.

                          Whether the CBO opts for a full table scan or performs partition pruning depends on the particular query predicates being used. If the query uses a partition key value in an equality predicate pruning will be done.

                          Five MB is a teeny-tiny table so I still don't understand why you need to partition it at all. As I said above
                          >
                          far you haven't really provide a clear explanation in either thread of just what problem you are trying to address or why you think you need to partition 'alphabetically' to deal with those problems. It would be helpful if you provided some sample data and showed what types of queries you expect to execute and what problems you think there will be.
                          >
                          You should quit focusing on the solution you want to use and refocus on the problem. I haven't seen ANY information posted by you that indicates there even IS a problem; especially for only 5MB.

                          First you should validate that you actually have a problem. Then you need to define clearly what the problem is. Only after that should you start looking for possible solutions.
                          1 person found this helpful
                          • 10. Re: partitioning a table
                            nohup
                            Of course it won't work. Don't you see that extra box-looking character you put at the end ot it? If you edit your original post and remove that character the link works just fine.
                            i did not put that character
                            :
                            The optimizer alreadys knows about 'returning results fast' - that is what it is designed to do. It does that best when it has all of the relevant information in the form of current stats and possibly histograms.
                            i gather stats after materialized view refresh
                            the materialized view does not have any inserts
                            :
                            If the query uses a partition key value in an equality predicate pruning will be done.
                            I still don't understand why you need to partition it at all.
                            even i dont.
                            You should quit focusing on the solution you want to use and refocus on the problem.
                            i am focusing on the problem and possible solution*s*
                            i want to use the solution that returns results fast

                            Edited by: 946207 on Dec 2, 2012 5:52 PM
                            • 11. Re: partitioning a table
                              rp0428
                              >
                              i did not put that character
                              >
                              YOU created the post and YOU put the content in it so YOU put the character there. If you think someone else has your account password or that a moderator altered what you posted you should immediately change your password and report the incident to Oracle.

                              No one said you did it intentionally but you also didn't edit your original post and remove the character as I suggested so that the link would work.