some more help
September 9, 2009 - 10am Central time zone
Reviewer: jv
Dear Tom,
good day to you, presently in our project thoughts on partitioning the tables are going on, after
reading this post just wanted some more help from you in understanding the below point.
quote
It would take the same amount of time to retrieve rows via an index from a 6,000 row table as a
6,000,000 row table as a 6,000,000,000 row table - the size of the table isn't really relevant to
index retrieval.
unquote
does this apply to retrieval of rows via all the index access paths, or am I totally of the mark.
Thanks for your help on this.
Regards,
jv
Followup September 14, 2009 - 10am Central time zone:
... does this apply to retrieval of rows via all the index access paths, or am I
totally of the mark.
...
that is what I said, yes
As long as the number of rows returned is the same, it'll take about the same amount of work to retrieve them from a 6,000 row table as a 6,000,000,000 row table.
September 11, 2009 - 4am Central time zone
Reviewer: Thomas
What Tom is referring to only applies to index lookups where the index structure can be exploited (like index range scan, index unique scan), not to replacements for full table scans (like index full scan or index fast full scan).
September 11, 2009 - 12pm Central time zone
Reviewer: Mike Kutz from Greensboro, NC
Partitioning is not always the correct solution to solve an issue.
I was having issues with an SQL statement that was joining a 5M row table to a 1M row table. (number of results was ~40 rows)
# of consistent gets where
9i - base - 42,310
9i - partitioned - 17,667
11g - part & compress - 2,433 (compress for all transactions)
11g - part & compress - 2,487,179 (prior to running DMBS_STATS)
11g - corrected index - 83
9i - corrected index - 123
All of my non-working plans had at least one 'full table scan'.
The real issue was a 'missing index'. I had to make a different index with a different column order of an index that already existed that i thought oracle was suppose to be using.
After I got 11g to 'work' properly, I had a fit getting our 9i production to work.
After racking my head on this for 3+ days, I added the FIRST_ROWS hint to the SQL statement and it now works.
'works' means 'results return < 0.1 sec'
'doesnt work' means 'results return > 2 sec'
look at the SQL statements being used, plans, etc., and go from there.
Followup September 14, 2009 - 1pm Central time zone:
If I was going to get 40 rows without aggregation/analytics involved (meaning 40 discrete rows - not 4,000,000 rows turned into 40 rows after aggregation), partitioning would not even be considered :)
You are correct.
Regarding Partitions
September 14, 2009 - 1am Central time zone
Reviewer: Hariharan from Chennai, India
Hi Tom,
Good Day.
Hope your trip to Bangalore, India was enjoyable and memorable.
In response to your answer to my question, it seems the table has between 50 - 70 Million records
instead of 6 million records. I apologize for the error in the number.
With reference to these many rows, I need your expert comments to the following questions.
a) Can you please explain with an example on "Partition Elimination" and how it can be implemented?
b) The team here have created List partition based on the customer ID. Hence, the data in partition
are not evenly distributed. I would like to know if the Sub-Partition on this list partition would
help? (i.e.) is it possible for me to create a hash partition on top of this list partition.
Will this create any issues?
c) Is it okay to create any local or global index on the partitioned data? (i.e.) apart from the
customer ID on which the table was partitioned, is it okay to create any other columns as Index?
Will it be helpful in increasing the performance?
d) Most of the queries issued are not based on the partitioned column. (i.e.) instead of
select <column list> from <table name> where customer_id = <some value>
they have used
select <column list> from <table name> where business_date = <some value> and object_id = < some
value>
Will this create any performance issues?
Thanks for your help.
Hari
Followup September 14, 2009 - 1pm Central time zone:
... it seems the table has between 50 -
70 Million records instead of 6 million records. ...
so? As long as the size of the result set coming back via that index is about the same - 6 rows, 6,000,000,000,000 rows - about the same.
a) explain what? are you asking to be explained what partition elimination is? If so, please do refer to the concepts guide, the data warehousing guide - it is pretty straight forward. If your predicate allows us to skip some partition because the data you are interested in cannot be in there (because of the way you partitioned), we will eliminate that partition from consideration, we won't access it. We "eliminate" it.
b) .... The team here have created List partition based on the customer ID. ... that seems unworkable long term unless you have very very very few customers.
you can list/hash partition in 11g and above, prior to 11g you had to start with range at the top and then you could either hash or list partition under it.
c) sure, it is expected you will index and it is expected some of the indexes will be LOCAL and some will be GLOBAL.
d) you will be using tons of global indexes then - not very many local indexes.
Regarding Partitions
September 15, 2009 - 1am Central time zone
Reviewer: Hariharan from Chennai, India
Thanks Tom. You are brilliant.
How come you are able to remember so many concepts in Oracle and answer all the questions? I am not
able to remember what I read or studied last week :) :)
Will definitely go through the Concepts guide and come with any queries if I have.
Please correct me if I am wrong
a) Now, In order for me to work on implementing Hash Partition on existing setup, the only option
is to remove the existing List partition and create partition based on Hash? Or is there any other
way to do?
b) The team used to run BATCH JOB of almost 2 - 3 Million records every night into this partitioned
table and they told me that it is taking almost 2-3 hrs to complete. When I checked, everything
seems to be fine, except that they are not disabling the FK constraints before the batch job. I
told them to Disable FK constraints, load the data and enable the FK constraints. But DBA says we
should not do that as he claims that enabling FK constraints would take a very long time.
Can you please clarify this point?
Thanks A Ton
Hari
Followup September 15, 2009 - 9am Central time zone:
How come you are able to remember so many concepts in Oracle and answer all the
questions?
I don't, I relearn stuff all of the time - however, the same set of questions does frequently come up over and over and over again.
a) to change a partitioning scheme involves moving every single row, it would require a reorganization, yes.
b) ... When I checked, everything seems to be fine, except that they are
not disabling the FK constraints before the batch job. ...
so????
... I told them to Disable
FK constraints, load the data and enable the FK constraints. ...
why - please provide the science behind that advice.
... But DBA says we
should not do that as he claims that enabling FK constraints would take a very
long time.
....
that sounds about right.
Look, if you touch 2-3% of the data as you say, disabling a constraint and re-enabling it isn't the correct approach.
If you have a job that is taking 2-3 hours, step 1 is always:
a) get a trace, get an AWR, get a statspack - get SOME NUMBERS so you can analyze what it is doing
If you just say things like "disable constraints", without diagnosing anything, without looking at anything, without doing any research - I would dismiss you from the room and not really ever come to you for advice.
Regarding Partitions
September 15, 2009 - 11am Central time zone
Reviewer: Hari from Chennai, India
Hi Tom,
Thanks for your comments (in fact it would be apt if I say Critics :) :) ). Just Kidding.
Let me tell you the real issue, probably you will be able to throw more lights on this.
Of course, since morning I have been analyzing trace and AWR report, just completed and found the
following:
a) Not even a single query was using Bind variable
b) Foreign Key constraints not indexed
c) High parse rates and Consistent Gets
And I have also started to provide recommendations for the issues.
However, the reason I asked for the FK constraints clarification is, whenever there is this kind of
Batch job getting executed, I have seen people DISABLING FK -> BATCH -> ENABLING FK.
I have also seen some good improvements, don't ask me for Math/Logic behind this, I do not have
one.
Just wanted to clarify if this is true and if yes/no, I was curious in understanding behind the
scenes.
Thanks
Hari
Followup September 15, 2009 - 1pm Central time zone:
a) for a batch job, if it is totally set based, that might not be a bad thing. If it does slow by slow processing - then it would be a really bad thing
b) that is probably OK if the foreign keys are to lookup tables (eg: you have a ZIP_CODE table with zip codes to state lookups, another table has zip_code as a foreign key - you don't need an index on that other table - none of the queries would go FROM zip_codes to this table, they all go FROM this table to zip_codes and zip_code in zip_codes is properly indexed - has to be - it is the primary key)
Here, indexing the foreign key could be a performance inhibitor as it would be yet another index to maintain - an index that doesn't do anything.
So, this in itself is not a bad thing, one would need more detail
c) that would indicate that the batch is slow by slow processing which means the only way to make it N times faster (not just x% faster where x < 80'ish) will be to redesign (or design) the algorithms to be set based.
...
However, the reason I asked for the FK constraints clarification is, whenever
there is this kind of Batch job getting executed, I have seen people DISABLING
FK -> BATCH -> ENABLING FK.
.....
so? that doesn't mean it is smart, good, best, desirable, etc.
...
I have also seen some good improvements, don't ask me for Math/Logic behind
this, I do not have one.
....
show me the numbers and give me the CONTEXT (the situation, the background). Then I can
a) tell you why it made sense
b) tell you when it would not make sense.
if you are touching 2-3% of the data, no sense would it make.
if you are touching 100% of the data over and over - some sense could be made.
...
Just wanted to clarify if this is true and if yes/no, I was curious in
understanding behind the scenes.
...
and for that you need two things
a) the original context - the complete set of circumstances/conditions in place at the time it "made it much faster"
b) an understanding of how things work
Then you can take (a)+(b) and extrapolate it out to other cases and predicate whether it would or would not make sense.
List partition
October 26, 2009 - 1pm Central time zone
Reviewer: pshah from Ahm IND
Hi Tom,
We want to introduce the partitioning for one of our existing table that holds historical data(for
maintanace purpose only).
Let's say table name is "TAB_1" that's having historical data for MEDICINE name and its supllier.
Supplier is varchar column.
Column Datatype
---------------- -------------------------
item_no NUMBER(15)
item_name VARCHAR2(100)
batch_no VARCHAR2(100)
. .
. .
supplier VARCHAR2(15)
We want to introduce the list partition on SUPPLIER column as after some period we want to get rid
of the data for perticular list of suppliers.
We need all suppliers which starts with "AA" till "AM" into one partition and "AN" to "AZ" into
other partition.
like
data for supplier "AAAAAA", "ABAAAAA", "AMAAAAA" should go into partition "P_1",
data for supplier "ANAAAA", "AOAAAAA", "AZAAAAA" should go into partition "P_2",
data for supplier "BNAAAA", "BOAAAAA", "BZAAAAA" should go into partition "P_3",
data for supplier "BNAAAA", "BOAAAAA", "BZAAAAA" should go into partition "P_4" and so on.
Is there any way to achive the same?
Thanks & Regards,
Followup October 26, 2009 - 3pm Central time zone:
you say you want list and then describe something that screams out "range"????
why do you think you want list when you give me range rules???!?!
Regarding partition
October 26, 2009 - 4pm Central time zone
Reviewer: pshah from Ahm IND
Our goal is to truncate the partition for the list of suppliers whose retention is over. For this
purpose if we make the list partition, we have around 4 partitions for the suppliers having same
retention. And we cannot put the entire list of supplier into one partition if it is list partition
because of 4K limit. (When I tried to create partition with all the suppliers it gave me
ORA-14116). So I just want to know if we can create range partition in this case.
Like values less that “AN” into one partition, less that “AZ” into other partition. The problem is
supplier field value is of varchar2(15). So supplier name can be of 5 char or 15 char as well. We
want to consider the first 2 or 3 character to define the range for partition. If this is possible
than what we can do is we can assign name which always starts with “AN” or less (like AN111, AN222,
AA333) to all the suppliers whose retention is same.
Regards,
Followup October 27, 2009 - 11am Central time zone:
not sure what you did wrong, but....
ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> set echo off
1 begin
2 dbms_output.put_line( 'create table t ( vendor varchar2(15), data varchar2(100)) partition
by list(vendor) ( ' );
3 for i in 1 .. 15000
4 loop
5 dbms_output.put( 'partition p' || to_char(i,'fm00000') || ' values ( ''' );
6 dbms_output.put( chr(ascii('A')+ trunc(i/(26*26))) || chr(ascii('A')+mod(trunc(i/26),26))
|| chr(ascii('A')+mod(i,26)) || to_char(i,'fm000000000000') );
7 dbms_output.put_line( '''),' );
8 end loop;
9 dbms_output.put_line( 'partition junk values ( DEFAULT ) );' );
10* end;
ops$tkyte%ORA10GR2> @x.lst
Table created.
ops$tkyte%ORA10GR2> select count(*) from user_tab_partitions where table_name = 'T';
COUNT(*)
----------
15001
1 row selected.
ops$tkyte%ORA10GR2> !cat x.lst
create table t ( vendor varchar2(15), data varchar2(100)) partition by
list(vendor) (
partition p00001 values ( 'AAB000000000001'),
partition p00002 values ( 'AAC000000000002'),
partition p00003 values ( 'AAD000000000003'),
partition p00004 values ( 'AAE000000000004'),
partition p00005 values ( 'AAF000000000005'),
partition p00006 values ( 'AAG000000000006'),
partition p00007 values ( 'AAH000000000007'),
partition p00008 values ( 'AAI000000000008'),
partition p00009 values ( 'AAJ000000000009'),
partition p00010 values ( 'AAK000000000010'),
partition p00011 values ( 'AAL000000000011'),
partition p00012 values ( 'AAM000000000012'),
partition p00013 values ( 'AAN000000000013'),
partition p00014 values ( 'AAO000000000014'),
.....
|