Re: Use Partition speed Query?

Option 1 is slower than option 2 because it is processing 2 partitions. Your boundary conditions are:

> Partition Part1997 Less than '19980000',
> Partition Part1998 Less than '19990000',

And you query is
>where salecard like '1998%'

But '19980' is less that '1998000' therefore belongs in partition Part1997, and since it is like '1998%' Oracle has to scan that partition as well as the one you intended.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

wy_at_fudan.edu wrote in message <82a5p1$9ju$1_at_nnrp1.deja.com>...
>I have create a partition table!
>Create Table SubSaleCard
>( SaleCard_No char(10),
> Uni_No char(30),
> Client char(20),
> Price Number(14,4),
> Quantity Number(4),
> Amount Number(14,4),
> Op_Date Date,
> Op_Code Char(10),
> Parimary Key(Salecard_No,Uni_No))
>Partition Ranged by(Salecard_No)
> Partition Part1999 Less than '20000000',
> Partition PartOther Less than MaxValue);
>
>Here is a table with 254323 records.
>In Part1997 80000 more record
>In Part1998 100000 more record
>In Part1999 70000 more record
>
>when I open a query
>one:
>Select avg(Price * Quantity) from SubSalcard
>where salecard like '1998%'
>two:
>Select avg(Price * Quantity) from SubSalcard partition(Part1998)
>where salecard like '1998%'
>three:
>Select avg(Price * Quantity) from Sub_Salcard partition(Part1998)
>where salecard like '1998%'
>--Sub_salecard is the same structure table but not partition
>four:
>Select avg(Price * Quantity) from Sub_Salcard1998 partition(Part1998)
>where salecard like '1998%'
>--sub_salecard1998 is the table only contain the 1998% record
>
>the query result:
>Two and four have the same speed.
>one and three have the same speed more slow than Two
>
>can the oracle analyse the query and select the correspond partition
>to improve the performance?
Received on Sat Dec 04 1999 - 09:04:08 CST