hi,

i was wondering if you could give me some advise with a problem im facing. It's the following:

My application queries data from a table which currently contains over 17 million records.. so its taking too much time to execute. Unfortunately, the person who created this table before me gave this table a 9-column primary key! Yes, i know, very poor! However there are several indexes of different column combinations on this table. But still the query is taking too long, sometimes more than 40 seconds.. I wanted to add clustering as well (the query also joins it with other tables), but im afraid im having no luck. Im new to using Oracle, so please give me advice on what to do. For a clearer picture, here's the script of the table, indexes and the query. I'd really appreciate your help with this:

THE TABLE:
==========
create table K_D_SALE
(
LOCATION_ID VARCHAR2(8) not null,
TXN_DT DATE not null,
CHANNEL_ID VARCHAR2(8) not null,
R_LOC_ID VARCHAR2(8) not null,
S_PERSON_ID VARCHAR2(8) not null,
CUSTOMER_ID VARCHAR2(8) not null,
WHOLESALER_ID VARCHAR2(8) not null,
SA_ITEM_ID VARCHAR2(8) not null,
GROSS_SALES NUMBER(16,4) not null,
GROSS_SALES_AM NUMBER(16,4) not null,
FREE_CASES NUMBER(16,4) not null,
FREE_CASES_AM NUMBER(16,4) not null,
I_DSC_AM NUMBER(16,4) default 0 not null,
INV_DSC_AM NUMBER(16,4) default 0 not null,
POST_DW_FL NUMBER(1) default 0 not null,
PROMOTION_ID VARCHAR2(80) not null,
STD_GROSS_SALES_AM NUMBER(16,4) default 0 not null,
STD_FREE_CASES_AM NUMBER(16,4) default 0 not null,
S_PERSON_ID_DELIV VARCHAR2(8),
WHID NUMBER
)


THE INDEXES:
============
create index K_D_SALE_K10 on K_D_SALE (TXN_DT, LOCATION_ID, R_LOC_ID, SA_ITEM_ID)

create index K_D_SALE_K11 on K_D_SALE (POST_DW_FL, SA_ITEM_ID)

create index K_D_SALE_K12 on K_D_SALE (CUSTOMER_ID, TXN_DT)

create index K_D_SALE_K13 on K_D_SALE (TXN_DT, R_LOC_ID, WHOLESALER_ID, SA_ITEM_ID, CHANNEL_ID)

create index K_D_SALE_K2 on K_D_SALE (LOCATION_ID, TXN_DT, S_PERSON_ID)

create index K_D_SALE_K3 on K_D_SALE (LOCATION_ID, CUSTOMER_ID, SA_ITEM_ID)

create index K_D_SALE_K4 on K_D_SALE (SA_ITEM_ID)

create index K_D_SALE_K5 on K_D_SALE (LOCATION_ID, R_LOC_ID, CUSTOMER_ID)

create index K_D_SALE_K6 on K_D_SALE (LOCATION_ID, R_LOC_ID, CUSTOMER_ID, SA_ITEM_ID, TXN_DT, CHANNEL_ID)

create index K_D_SALE_K7 on K_D_SALE (LOCATION_ID, S_PERSON_ID, TXN_DT)

create index K_D_SALE_K8 on K_D_SALE (WHOLESALER_ID)

create index K_D_SALE_K9 on K_D_SALE (TXN_DT)


The QUERY:
==========
create or replace procedure GetCustomerTransactions(FromDate IN date, ToDate IN date, FSID IN nvarchar2 := '', records out SYS_REFCURSOR)
is

begin

open records for

select analysis.analysis_nm as Package,
analysis.analysis_id as PackageID,
SUM(case when k_d_sale.free_cases is null then 0 else k_d_sale.free_cases end) as Free,
(case when SUM(NoFreeSale.GROSS_SALES_AM) > 0 then trunc(100*SUM(NoFreeSale.I_DSC_AM)/SUM(NoFreeSale.GROSS_SALES_AM),4) else 0 end) as Discount,
SUM(k_d_sale.gross_sales - k_d_sale.free_cases) as Sold

from psd.k_d_sale

inner join psd.s_it_ana on k_d_sale.sa_item_id = s_it_ana.sa_item_id
inner join psd.sa_item on sa_item.sa_item_id = s_it_ana.sa_item_id
inner join psd.analysis on analysis.analysis_id = s_it_ana.analysis_id and analysis.analy_grp_id='2'
inner join bispm_factsheetsstorelabels on k_d_sale.customer_id = bispm_factsheetsstorelabels.storelabelid

where k_d_sale.txn_dt >= FromDate and k_d_sale.txn_dt <= ToDate
and bispm_factsheetsstorelabels.factsheetid = FSID
and (sa_item.start_dt <= ToDate and (sa_item.end_dt >= FromDate or sa_item.end_dt is null))

group by analysis.analysis_nm, analysis.analysis_id
order by analysis.analysis_nm;

end GetCustomerTransactions;

hi,
try with adding index hints forcefully in your select query like :

select /*+index K_D_SALE_K9 */
analysis.analysis_nm as Package,
analysis.analysis_id as PackageID,
SUM(case when k_d_sale.free_cases is null then 0 else k_d_sale.free_cases end) as Free,
(case when SUM(NoFreeSale.GROSS_SALES_AM) > 0 then trunc(100*SUM(NoFreeSale.I_DSC_AM)/SUM(NoFreeSale.GROSS_SALES_AM),4) else 0 end) as Discount,
SUM(k_d_sale.gross_sales - k_d_sale.free_cases) as Sold

from psd.k_d_sale

inner join psd.s_it_ana on k_d_sale.sa_item_id = s_it_ana.sa_item_id
inner join psd.sa_item on sa_item.sa_item_id = s_it_ana.sa_item_id
inner join psd.analysis on analysis.analysis_id = s_it_ana.analysis_id and analysis.analy_grp_id='2'
inner join bispm_factsheetsstorelabels on k_d_sale.customer_id = bispm_factsheetsstorelabels.storelabelid

where k_d_sale.txn_dt >= FromDate and k_d_sale.txn_dt <= ToDate
and bispm_factsheetsstorelabels.factsheetid = FSID
and (sa_item.start_dt <= ToDate and (sa_item.end_dt >= FromDate or sa_item.end_dt is null))

group by analysis.analysis_nm, analysis.analysis_id
order by analysis.analysis_nm;

hey Parag,

thanks for your reply, but i dont think this would be useful since hinting in the select clause wont do much to help (and you can only use hints in the select clause as far as i know). Optimization needs to be done in the inner joins and where clause. Is there anything else i can do? How about clustering?... how should i do that?

ZiggY.

clustering we can do with :
create the cluster on common column of table which you want to join then use cluster based query like :

CREATE CLUSTER cc (deptno NUMBER)
CREATE INDEX id ON CLUSTER cc

Hai ziggy,

First you should try to use the following points.
Analyze, what r all the tables involved in ur query.
Use alias name for that tables for easy readability.

Before u executing the query,
first try to do explain plan for that query.

Identify driving table(Basic where condition tables) in the Query.
Check whether optimizer using
right access path or not.
If Oracle's choice of Index is wrong then use the hint INDEX(table_alias_name, index_name).
If ur query involves two many tables and not follwing the correct order wt u mentioned in the FROM clause, then use the hint ORDERED.

As u said,
Table has 1,000,000 records.
If the result set has >200000 records means go for full table scan.Then avoid index scan.

U r almost having indexes on all the columns, that it is not actually needed.
Index those columns that are used frequently to join tables in SQL statements.

From your query i cant understand the inner join flow and alias names wherever u mentioned.

Simple example, for Inner Join..
Just do something like this..
Hope u understand Quite better from the above points.
Do well.