|
Home > Archive > MS SQL Server > April 2006 > Performance problem with Partition tables in SQL Server 2005
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Performance problem with Partition tables in SQL Server 2005
|
|
|
| I create partition table and regular table, both with the exact structure and
data.
The aim was to check whether there is an improvement in the performance or
not.
I execute the following queries (myPartTable is the partition table):
select * from myTable where instance_id = 3
select * from myPartTable where instance_id = 3
select * from myPartTable where instance_id = 3 and $partition.IntRangePF(3)
= 3
The result:
- Significant difference in the performance of the second and the third query.
- No major difference between the first and second query. In most of the
cases, the first even show better performance.
My question:
1) Why there is a significant improvement between the second and the third
query?
Isn’t SQL Server supposed to recognize and access the partition without
specify its number?
2) Shouldn't I get some improvement in the performance when using partition
table?
-----------------------------------------
Attached a sample code to create the test.
Create Partition Function:
CREATE PARTITION FUNCTION IntRangePF(int)
AS RANGE left FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Create Partition Scheme:
CREATE PARTITION SCHEME IntRangePScheme AS PARTITION IntRangePF
ALL TO ([PRIMARY])
Create the tables:
CREATE TABLE myPartTable (
id int NOT NULL,
instance_id int NOT NULL,
description varchar(
32) NOT NULL,
temp1 float NULL,
temp2 float NULL,
temp3 float NULL,
constraint table1_P_PK primary key nonclustered(instanc
e_id, id) on
[PRIMARY] ,
) ON IntRangePScheme (instance_id)
CREATE CLUSTERED INDEX index_myPartTable ON myPartTable(
[instance_id] ASC,
[id] ASC )
CREATE TABLE myTable (
id int NOT NULL,
instance_id int NOT NULL,
description varchar(
32) NOT NULL,
temp1 float NULL,
temp2 float NULL,
temp3 float NULL,
constraint myTable_PK primary key nonclustered(instanc
e_id, id) on
[PRIMARY] ,
)
CREATE CLUSTERED INDEX index_myTable ON myTable(
[instance_id] ASC,
[id] ASC )
Insert Data
DECLARE @i int
SET @i = 1;
WHILE @i < 100
BEGIN
SET @i = @i + 1;
insert into myTable values (@i, 1, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 2, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 3, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 4, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 5, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 6, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 7, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 8, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 9, 'ddddd', 1, 1, 1);
insert into myTable values (@i, 10, 'ddddd', 1, 1, 1);
END
insert into myPartTable select * from myTable
| |
| David Browne 2006-03-12, 9:23 am |
|
"???" <@discussions.microsoft.com> wrote in message
news:B2FD061B-378D-4E4D-BC2A- 04D5E3B9371A@microso
ft.com...
>I create partition table and regular table, both with the exact structure
>and
> data.
> The aim was to check whether there is an improvement in the performance or
> not.
>
> I execute the following queries (myPartTable is the partition table):
> select * from myTable where instance_id = 3
> select * from myPartTable where instance_id = 3
> select * from myPartTable where instance_id = 3 and
> $partition.IntRangePF(3)
> = 3
>
> The result:
> - Significant difference in the performance of the second and the third
> query.
> - No major difference between the first and second query. In most of the
> cases, the first even show better performance.
>
> My question:
> 1) Why there is a significant improvement between the second and the third
> query?
> Isn't SQL Server supposed to recognize and access the partition without
> specify its number?
> 2) Shouldn't I get some improvement in the performance when using
> partition
> table?
>
> -----------------------------------------
> Attached a sample code to create the test.
>
> Create Partition Function:
> CREATE PARTITION FUNCTION IntRangePF(int)
> AS RANGE left FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
>
> Create Partition Scheme:
> CREATE PARTITION SCHEME IntRangePScheme AS PARTITION IntRangePF
> ALL TO ([PRIMARY])
>
> Create the tables:
> CREATE TABLE myPartTable (
> id int NOT NULL,
> instance_id int NOT NULL,
> description varchar(32) NOT NULL,
> temp1 float NULL,
> temp2 float NULL,
> temp3 float NULL,
> constraint table1_P_PK primary key nonclustered(instanc
e_id, id) on
> [PRIMARY] ,
> ) ON IntRangePScheme (instance_id)
>
> CREATE CLUSTERED INDEX index_myPartTable ON myPartTable(
> [instance_id] ASC,
> [id] ASC )
>
When you create a clustered index on a table you move the table into the
filegroup or partition scheme for the clustered index. So when you created
a clustered index index_myPartTable on the primary filegroup instead of on
your partition scheme you "unpartitioned" the table.
Change like this:
CREATE TABLE myPartTable (
id int NOT NULL,
instance_id int NOT NULL,
description varchar(32) NOT NULL,
temp1 float NULL,
temp2 float NULL,
temp3 float NULL,
constraint table1_P_PK primary key nonclustered(instanc
e_id, id) on
[PRIMARY] ,
) ON IntRangePScheme (instance_id)
CREATE CLUSTERED INDEX index_myPartTable ON myPartTable(
[instance_id] ASC,
[id] ASC ) ON IntRangePScheme (instance_id)
Or, all in one statement
CREATE TABLE myPartTable (
id int NOT NULL,
instance_id int NOT NULL,
description varchar(32) NOT NULL,
temp1 float NULL,
temp2 float NULL,
temp3 float NULL,
constraint table1_P_PK
primary key clustered(instance_i
d, id)
ON IntRangePScheme (instance_id) ,
)
Then you will see that the partition elimination reduces the IO's required
for your queries.
David
| |
|
| I change the clustered index (the other one automatically created on the
partition scheme), but the performance are still the same.
When running the 3 query together, I am getting 43% query cost (in the
execution plan panel) for the first query, 42% for the second and 15% for the
last.
The main question is why do I get difference between the 2nd and 3rd query.
"David Browne" wrote:
>
> "???" <@discussions.microsoft.com> wrote in message
> news:B2FD061B-378D-4E4D-BC2A- 04D5E3B9371A@microso
ft.com...
>
> When you create a clustered index on a table you move the table into the
> filegroup or partition scheme for the clustered index. So when you created
> a clustered index index_myPartTable on the primary filegroup instead of on
> your partition scheme you "unpartitioned" the table.
>
>
> Change like this:
>
> CREATE TABLE myPartTable (
> id int NOT NULL,
> instance_id int NOT NULL,
> description varchar(32) NOT NULL,
> temp1 float NULL,
> temp2 float NULL,
> temp3 float NULL,
> constraint table1_P_PK primary key nonclustered(instanc
e_id, id) on
> [PRIMARY] ,
> ) ON IntRangePScheme (instance_id)
>
> CREATE CLUSTERED INDEX index_myPartTable ON myPartTable(
> [instance_id] ASC,
> [id] ASC ) ON IntRangePScheme (instance_id)
>
>
> Or, all in one statement
>
> CREATE TABLE myPartTable (
> id int NOT NULL,
> instance_id int NOT NULL,
> description varchar(32) NOT NULL,
> temp1 float NULL,
> temp2 float NULL,
> temp3 float NULL,
> constraint table1_P_PK
> primary key clustered(instance_i
d, id)
> ON IntRangePScheme (instance_id) ,
> )
>
>
> Then you will see that the partition elimination reduces the IO's required
> for your queries.
>
> David
>
>
>
| |
|
| does you % are real execution or estimated plan?
when you explicitly reference a partition SQL know which partition to read
directly.
when you go through the entire table, SQL doesn't know which partition is
really used until you run it.
but you are right, sometimes I found a query like your slow. SQL scan all
the partitions instead-of scanning only the required partitions.
This completly slow down the query!
"???" <@discussions.microsoft.com> wrote in message
news:E515D9C3-B0A6-4E37-BCB6- 9128EC4D9BCC@microso
ft.com...[color=darkred]
>I change the clustered index (the other one automatically created on the
> partition scheme), but the performance are still the same.
>
> When running the 3 query together, I am getting 43% query cost (in the
> execution plan panel) for the first query, 42% for the second and 15% for
> the
> last.
>
> The main question is why do I get difference between the 2nd and 3rd
> query.
>
> "David Browne" wrote:
>
| |
| David Browne 2006-03-12, 1:23 pm |
|
"???" <@discussions.microsoft.com> wrote in message
news:E515D9C3-B0A6-4E37-BCB6- 9128EC4D9BCC@microso
ft.com...
>I change the clustered index (the other one automatically created on the
> partition scheme), but the performance are still the same.
>
> When running the 3 query together, I am getting 43% query cost (in the
> execution plan panel) for the first query, 42% for the second and 15% for
> the
> last.
>
> The main question is why do I get difference between the 2nd and 3rd
> query.
>
The second and third both hit a single partition for me. Look at the actual
execution plan. Hover the index scan and you should see a PartitionID
or save the plan and view it with a text editor. You should see the
PartitionId uder the IndexScan, something like
<IndexScan Ordered="true" ScanDirection="FORWARD"
ForcedIndex="false" NoExpandHint="false">
<DefinedValues> . . . </DefinedValues>
<Object Database="[Demo]" Schema="[dbo]"
Table="[myPartTable]" Index="[table1_P_PK]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Demo]" Schema="[dbo]"
Table="[myPartTable]" Column="instance_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(3)">
<Const ConstValue="(3)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator ScalarString="(3)">
<Const ConstValue="(3)" />
</ScalarOperator>
</ColumnReference>
</PartitionId>
</IndexScan>
David
| |
|
| I can see that it scan the partition, but i'm not getting the same performance.
"David Browne" wrote:
>
> "???" <@discussions.microsoft.com> wrote in message
> news:E515D9C3-B0A6-4E37-BCB6- 9128EC4D9BCC@microso
ft.com...
>
> The second and third both hit a single partition for me. Look at the actual
> execution plan. Hover the index scan and you should see a PartitionID
>
> or save the plan and view it with a text editor. You should see the
> PartitionId uder the IndexScan, something like
>
> <IndexScan Ordered="true" ScanDirection="FORWARD"
> ForcedIndex="false" NoExpandHint="false">
> <DefinedValues> . . . </DefinedValues>
> <Object Database="[Demo]" Schema="[dbo]"
> Table="[myPartTable]" Index="[table1_P_PK]" />
> <SeekPredicates>
> <SeekPredicate>
> <Prefix ScanType="EQ">
> <RangeColumns>
> <ColumnReference Database="[Demo]" Schema="[dbo]"
> Table="[myPartTable]" Column="instance_id" />
> </RangeColumns>
> <RangeExpressions>
> <ScalarOperator ScalarString="(3)">
> <Const ConstValue="(3)" />
> </ScalarOperator>
> </RangeExpressions>
> </Prefix>
> </SeekPredicate>
> </SeekPredicates>
> <PartitionId>
> <ColumnReference Column="ConstExpr1007">
> <ScalarOperator ScalarString="(3)">
> <Const ConstValue="(3)" />
> </ScalarOperator>
> </ColumnReference>
> </PartitionId>
> </IndexScan>
>
> David
>
>
>
| |
| David Browne 2006-03-14, 11:23 am |
|
"???" <@discussions.microsoft.com> wrote in message
news:987784C1-C165-40A9-B76E- B7C1F4E99F07@microso
ft.com...
>I can see that it scan the partition, but i'm not getting the same
>performance.
>
What performance difference are you expecting? And how are you measuring it?
Partitioning is fundamentally a performance feature, but it's designed for
tables a lot of rows. Partitioning makes it practical to have have tables
with billions of rows. Small databases you won't always see a large
difference with partitioning.
David
| |
|
| When I run the queries, I include Actual Execution plan, and then I examine
the performance in the Execution Plan tab.
I’m expecting to get better performance when accessing the partition table
as oppose to the regular table.
I run the same test on tables with 1,000,000 records per instance, and the
results are the same – the performance are the usually the same.
The biggest question is why I get difference between these 2 queries (when
accessing the partition table):
(Query 1) select * from myPartTable where instance_id = 3
(Query 2) select * from myPartTable where instance_id = 3 and
$partition.IntRangePF(3) = 3
When running these queries on partition table with 7251452 records, I get
the following performance:
Query 1: Query Cost = 76%, Subtree Cost = 4.26687
Plan:
Clustered Index Seek, Estimate I/O Cost = 3.46905
Query 2: Query Cost = 24%, Subtree Cost = 1.34275
Plan:
Clustered Index Seek, Estimate I/O Cost = 1.04239; Subtree Cost = 1.28184
Filter, Subtree Cost = 1.34275
*** In both cases the plan show access to partition 3.
Why adding the statement “$partition.IntRangePF(3) = 3” had such an effect
on the performance, while this statement is not suppose to effect filter in
the index. I will get the same performance if I will add
“$partition.IntRangePF(1) = 1” on the same query.
Another improvement I get with the following query:
(Query 3)select * from myPartTable where instance_id = 3 and
$partition. IntRangePF(instance_
id) = $partition.IntRangePF(3)
Running this query with the other show the following cost:
Query 1: Query Cost = 70%, Subtree Cost = 4.26687
Plan:
Clustered Index Seek, Estimate I/O Cost = 3.46905
Query 2: Query Cost = 22%, Subtree Cost = 1.34275
Plan:
Clustered Index Seek, Estimate I/O Cost = 1.04239; Subtree Cost = 1.28184
Filter (Cost 5%), Subtree Cost = 1.34275
Query 3: Query Cost = 8%, Subtree Cost = 0.501189
Plan:
Clustered Index Seek, Estimate I/O Cost = 0.317536; Subtree Cost = 0.390208
Filter (Cost 22%), Subtree Cost = 0.500957
"David Browne" wrote:
>
> "???" <@discussions.microsoft.com> wrote in message
> news:987784C1-C165-40A9-B76E- B7C1F4E99F07@microso
ft.com...
>
>
> What performance difference are you expecting? And how are you measuring it?
>
> Partitioning is fundamentally a performance feature, but it's designed for
> tables a lot of rows. Partitioning makes it practical to have have tables
> with billions of rows. Small databases you won't always see a large
> difference with partitioning.
>
> David
>
>
>
| |
| David Browne 2006-03-16, 7:23 am |
|
"???" <@discussions.microsoft.com> wrote in message
news:34C036B2-A6C7-48C4-BF41- C9BA15C94E82@microso
ft.com...
> When I run the queries, I include Actual Execution plan, and then I
> examine
> the performance in the Execution Plan tab.
> I'm expecting to get better performance when accessing the partition table
> as oppose to the regular table.
> I run the same test on tables with 1,000,000 records per instance, and the
> results are the same - the performance are the usually the same.
>
> The biggest question is why I get difference between these 2 queries (when
> accessing the partition table):
> (Query 1) select * from myPartTable where instance_id = 3
> (Query 2) select * from myPartTable where instance_id = 3 and
> $partition.IntRangePF(3) = 3
>
> When running these queries on partition table with 7251452 records, I get
> the following performance:
> Query 1: Query Cost = 76%, Subtree Cost = 4.26687
> Plan:
> Clustered Index Seek, Estimate I/O Cost = 3.46905
> Query 2: Query Cost = 24%, Subtree Cost = 1.34275
> Plan:
> Clustered Index Seek, Estimate I/O Cost = 1.04239; Subtree Cost =
> 1.28184
> Filter, Subtree Cost = 1.34275
>
Ok I see what you mean.
The estimated cost of the second query is incorrect, leading to an apparent
performance difference. The difference is not real. It is only in the
estimated cost of the queries. In particular the "Estimated Number of Rows"
for the Clustered Index Seek operator in the second query is not very
accurate. The actual cost of the queries is almost identical.
Run this to see that the actual cost of the queries is similar:
set statistics IO on
set statistics time on
select * from myPartTable where instance_id = 3
select * from myPartTable where instance_id = 3 and $partition.IntRangePF(3)
= 3
David
| |
|
|
| Scott C. 2006-04-02, 11:23 am |
| David, you are wrong. I agree with the original poster that MS SQL
Server should recognize without specifically coding of patitioning
function. As an OCA/OCP DBA, I am doing a comparison report between
Oracle and SQL Server 2005, the more I get to know of it, the more I am
disppointed with it.
set statistics IO on
set statistics time on
select * from ScottTable where partition_id=3
Table 'ScottTable'. Scan count 1, logical reads 3, physical reads 3,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 364 ms.
select * from ScottPartTable where partition_id=3
Table 'ScottPartTable'. Scan count 1, logical reads 2, physical reads 1,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 211 ms.
select * from ScottPartTable where partition_id=3 and
$partition. RangePartitionFuncti
on(3)=3
Table 'ScottPartTable'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 51 ms.
As seen above, I/O's difference makes performance difference in REAL
time. The query execution plan in SQL Server was indeed CORRECT. 2nd and
3rd clearly shows the different performance because of the difference in
physical reads (i.e. I/O, FYI).
What disappoints me in partitioning in SQL Server is that they do only
to the file group (we call it schema in Oracle). In Oracle, you have
multi-level of paritioning is possible from statement using hint to the
schema level. The worst thing might be the incapability of SQL Server's
partitioning scheme, which restricts only to the range partition. In
Oracle, we have supports not only range but also hash, list and
composite as well as index partitioning in various levels. However, in
terms of range partitioning in SQL Server, they show us CORRECT
statistic-driven data that extactly MATCHES with REAL time output.
Regards,
Scott C.
OCA/OCP
*** Sent via Developersdex http://www.droptable.com ***
| |
| David Browne 2006-04-02, 8:23 pm |
|
"Scott C." <ssambak73@hotmail.com> wrote in message
news:e2WtkCnVGHA.4404@TK2MSFTNGP10.phx.gbl...
> David, you are wrong.
No. See below.
> I agree with the original poster that MS SQL
> Server should recognize without specifically coding of patitioning
> function. As an OCA/OCP DBA, I am doing a comparison report between
> Oracle and SQL Server 2005, the more I get to know of it, the more I am
> disppointed with it.
>
> set statistics IO on
> set statistics time on
>
> select * from ScottTable where partition_id=3
>
> Table 'ScottTable'. Scan count 1, logical reads 3, physical reads 3,
> read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
> read-ahead reads 0.
>
> SQL Server Execution Times:
> CPU time = 10 ms, elapsed time = 364 ms.
>
> select * from ScottPartTable where partition_id=3
>
> Table 'ScottPartTable'. Scan count 1, logical reads 2, physical reads 1,
> read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
> read-ahead reads 0.
>
> SQL Server Execution Times:
> CPU time = 0 ms, elapsed time = 211 ms.
>
>
> select * from ScottPartTable where partition_id=3 and
> $partition. RangePartitionFuncti
on(3)=3
>
> Table 'ScottPartTable'. Scan count 1, logical reads 2, physical reads 0,
> read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
> read-ahead reads 0.
>
> SQL Server Execution Times:
> CPU time = 0 ms, elapsed time = 51 ms.
>
>
> As seen above, I/O's difference makes performance difference in REAL
> time. The query execution plan in SQL Server was indeed CORRECT. 2nd and
> 3rd clearly shows the different performance because of the difference in
> physical reads (i.e. I/O, FYI).
>
The difference in physical reads is due to caching, and only indicates which
query is first, not which is cheapest.
> What disappoints me in partitioning in SQL Server is that they do only
> to the file group (we call it schema in Oracle).
No you don't. A schema in Oracle is the same as a schema in SQL Server.
A file group in SQL Server is analagous to a table space in Oracle.
>In Oracle, you have
> multi-level of paritioning is possible from statement using hint to the
> schema level.
Huh?
>The worst thing might be the incapability of SQL Server's
> partitioning scheme, which restricts only to the range partition.
> Oracle, we have supports not only range but also hash, list and
> composite as well as index partitioning in various levels.
List partitioning is trivial to implement using SQL Server's range
partitioning. Other, more complicated schemes can be acomplished by
partitioning on persisted computed columns.
>However, in
> terms of range partitioning in SQL Server, they show us CORRECT
> statistic-driven data that extactly MATCHES with REAL time output.
>
Huh?
David
| |
| Scott C. 2006-04-03, 3:23 am |
|
Read carefully, but still if you don't understand, just nevermind. Just
gonna point out not to waste my time in here.
>The difference in physical reads is due to caching, and >only indicates
which
>query is first, not which is cheapest.
For this, just simple answer. Cache reading does not count as physical
read. Why do you think physical read takes so longer compared to memory
cache reads huh? this is fundamentally basic thing.
Just see the fact that the 3rd query runs almost 10 times faster than
the 2nd in REAL time - seems that you don't understand this one as well,
just skip it then.
>A schema in Oracle is the same as a schema in SQL Server.
No, Schema in Oracle are used in many different ways - could be user
account name or could be the entire database name, etc. A schema in SQL
Server is not firmly defined as Oracle. Where the heck do you see your
schema in SQL Server Management Studio? huh? They are not the same.
>A file group in SQL Server is analagous to a table space >in Oracle.
You can think that in a naive way but in reality, file group is much
more like a set of independent data blocks while table space
specifically defines what tables need to be included in. File groups in
SQL Server does not seem to analagous to table space in Oracle to me in
that the consecutive creation of file groups are nowhere to include any
objects.
>In Oracle, you have
> multi-level of paritioning is possible from statement using hint to
the
> schema level.
>Huh?
Just skip it if you don't understand, alrite? Your ignorance about
"HINT" does not level down my knowledge on it. Attend any Oracle
Authorized University close to your location, then you will say "Ah-ha"
instead of "huh?" Sorry no hint for HINT LOL
>List partitioning is trivial to implement using SQL >Server's range
>partitioning. Other, more complicated schemes can be >acomplished by
>partitioning on persisted computed columns.
List and range partition is basically same thing. No surprise. I was
just pointing out that the grid computing is not possible in SQL Server
since it lacks Object Oriented Type supported by Oracle. Dig in R-Tree
data struture stuff, then you will see what I mean - it has a lot things
to do with partitioning.
>However, in
> terms of range partitioning in SQL Server, they show us >CORRECT
> statistic-driven data that extactly MATCHES with REAL time output.
>
>Huh?
You didn't even f**king read the outcome of my result. Get lost and
screw all db of your company, Jeez. Don't get near to my company.
Scott C
OCA/OCP
*** Sent via Developersdex http://www.droptable.com ***
| |
| David Browne 2006-04-03, 3:23 am |
|
"Scott C." <ssambak73@hotmail.com> wrote in message
news:eVhEfRsVGHA.5592@TK2MSFTNGP09.phx.gbl...
>
>
> Read carefully, but still if you don't understand, just nevermind. Just
> gonna point out not to waste my time in here.
>
> which
>
> For this, just simple answer. Cache reading does not count as physical
> read. Why do you think physical read takes so longer compared to memory
> cache reads huh? this is fundamentally basic thing.
>
> Just see the fact that the 3rd query runs almost 10 times faster than
> the 2nd in REAL time - seems that you don't understand this one as well,
> just skip it then.
>
Complete nonsense, both in SQL Server and Oracle. Data is read from the
cache, but if it isn't in the cache it's read first from disk. So the first
time you read a page (block in Oracle), it causes a physical read and takes
longer. The next time you read the page (block) it's already in cache.
> No, Schema in Oracle are used in many different ways - could be user
> account name or could be the entire database name, etc. A schema in SQL
> Server is not firmly defined as Oracle. Where the heck do you see your
> schema in SQL Server Management Studio? huh? They are not the same.
>
Complete nonsense. Schemas are the same, the only difference is that a SQL
Server instance can mount multiple databases, and a single database can be
mounted by only one instance. In Oracle an instance can only mount one
database, but a database can be mounted by mulitple instances (RAC).
> You can think that in a naive way but in reality, file group is much
> more like a set of independent data blocks while table space
> specifically defines what tables need to be included in. File groups in
> SQL Server does not seem to analagous to table space in Oracle to me in
> that the consecutive creation of file groups are nowhere to include any
> objects.
>
Complete nonsense. They are the same. Each is the physical database
structure on which you create a table or index.
> the
>
>
> Just skip it if you don't understand, alrite? Your ignorance about
> "HINT" does not level down my knowledge on it.
Huh? Is that English?
> Attend any Oracle
> Authorized University close to your location, then you will say "Ah-ha"
> instead of "huh?" Sorry no hint for HINT LOL
>
>
> List and range partition is basically same thing. No surprise. I was
> just pointing out that the grid computing is not possible in SQL Server
> since it lacks Object Oriented Type supported by Oracle. Dig in R-Tree
> data struture stuff, then you will see what I mean - it has a lot things
> to do with partitioning.
>
>
>
> You didn't even f**king read the outcome of my result. Get lost and
> screw all db of your company, Jeez. Don't get near to my company.
>
What did I miss? Why don't you post a real example and prove your point,
instead of cursing and ranting?
David
|
|
|
|
|