Use Partition speed Query?

From: <wy_at_fudan.edu>
Date: Sat, 04 Dec 1999 04:36:50 GMT
Message-ID: <82a5p1$9ju$1@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 Part1997 Less than '19980000',
 Partition Part1998 Less than '19990000',
 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?
or has some way to substitute the name subsalecard partition(part1998) with subsalecard1998 using synonym.
I afraid that using view would lead to bad performance(in some case it can not use the indexes).

Best Regards.

YangWang.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 03 1999 - 22:36:50 CST