How is this table patitioned?
Bas
-----Oorspronkelijk bericht-----
Van: Chamberlain John
[mailto:oracledba-ezmlmshield-x9028498.[Email address protected]
Verzonden: dinsdag 4 juli 2006 17:17
Aan: LazyDBA Discussion
Onderwerp: RE: INDEX and PARTION
Is the index one index across all 4 fields or individual indexes for
each field? My guess is one index. Look at the where clause. There is no
reference to "PRODKEY". Indexes are evaluated by Oracle starting with
the left most field. The index will never be used unless this field is
referenced.
You should create an index on MKTKEY ,PERKEY, DOLLARS for best access.
Maybe add PRODKEY to the end so it will only need to read the index.
Don't forget to analyze the table as this often improves performance
dramatically.
Also, why are you using IN operators for a single value? Equals will be
better.
And what is the table DATA2 (DATA2.DOLLARS)? I guess you have
abbreviated the query a bit!
HTH
John.
-----Original Message-----
From: Tavousi Mahnaz
[mailto:oracledba-ezmlmshield-x50520707.[Email address protected]
Sent: 04 July 2006 15:47
To: LazyDBA Discussion
Subject: INDEX and PARTION
Hello DBAs,
This question is bugging me for a while and I cannot fins any answer for
it.
I have a table with 126 partitions in it. When I run my query ( the
following on) it uses the full table scan instead of using index.
I am wondering what is the reason because if it uses the index the
performance is different.
How can I force it to use index in partition.
SELECT PRODKEY
FROM SALES_LEVEL DATA
WHERE DATA.MKTKEY IN (74) AND
DATA.PERKEY IN (2003121301) AND
DATA2.DOLLARS > 50000
There is index for PRODKEY, MKTKEY ,PERKEY and DOLLARS .
EXECUTION PLAN:
SELECT STATMENT ALLROWS
PX COORDINATOR
PX SEND(QC (RANDOM) SYS:[Email Address Removed] PX BLOCK(ITERATOR)
TABLE ACCESS(FULL)
Thanks for your advise,
Mahnaz Tavousi
> ACNielsen Company of Canada
> Tel: 905-475-3344 ext 2615
> e-mail: Mahnaz.[Email address protected]
>
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page