Home » SQL & PL/SQL » SQL & PL/SQL » need help to create index to optimize query
need help to create index to optimize query [message #333530] Sat, 12 July 2008 04:09 Go to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Hi

My oracle version is

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


i have these two tables


CREATE TABLE TRANSACTIONS (
	YEAR NUMBER(4) NOT NULL, 
	BRANCH_ID VARCHAR2(2) NOT NULL,
	BOOK VARCHAR2(6) NOT NULL,
	SRNO NUMBER(6) NOT NULL,
	TRANS_DATE DATE NOT NULL,
	DR_ACCOUNT VARCHAR2(8),
	CR_ACCOUNT VARCHAR2(8),
	PARTY_CODE VARCHAR2(9),
	WAREHOUSE_ID VARCHAR2(2),
	SALESMAN_ID VARCHAR2(2),
	ORDER_ID NUMBER(6),
	RECV_DATE DATE,
	TO_WAREHOUSE VARCHAR2(2),
	TO_FROM_BRANCH VARCHAR2(2),
	REF VARCHAR2(30),
	AMOUNT NUMBER(12,2),
	DISC NUMBER(12,2),
	BANK VARCHAR2(25),
	STATUS VARCHAR2(1), 
	CREDIT_DAYS NUMBER(3), 
	COST NUMBER(10, 2),
	TYPE VARCHAR2(1),
	USER_ID VARCHAR2(30 ) DEFAULT USER,
	TRANS_TIME DATE DEFAULT SYSDATE,
	LAST_UPD_BY VARCHAR2(30),
	LAST_UPD_TIME DATE DEFAULT sysdate,
	ACC_BRANCH VARCHAR2(2),
	LEA_YEAR NUMBER(4),
	LEA_ACC NUMBER(4),
	INSPECTOR_ID VARCHAR2(3), 
	CONSTRAINT "TRANS_PK" PRIMARY KEY(YEAR, BRANCH_ID, BOOK,SRNO)
	USING INDEX  
	TABLESPACE SYSTEM 
	STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS  2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS 2 MAXTRANS 255)
 	TABLESPACE SYSTEM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( FREELISTS 1 FREELIST GROUPS 1) NOLOGGING  
  	PARTITION BY LIST (YEAR) 
	(PARTITION P_2003 VALUES (2003),
	PARTITION P_2004  VALUES (2004),
	PARTITION P_2005 VALUES (2005),
	PARTITION P_2006  VALUES  (2006),
	PARTITION P_2007  VALUES  (2007),
	PARTITION P_2008  VALUES (2008),
	PARTITION P_2009  VALUES  (2009));

CREATE TABLE STOCK_TRANSACTIONS (
	YEAR NUMBER(4) NOT NULL,
	BRANCH_ID VARCHAR2(2) NOT NULL,
	BOOK VARCHAR2(6) NOT NULL,
	SRNO NUMBER(6) NOT NULL,
	PRODUCT_ID VARCHAR2(3) NOT NULL,
	BRAND_ID VARCHAR2(3) NOT NULL,
	MODEL_ID VARCHAR2(4) NOT NULL,
	CONDITION VARCHAR2(1) NOT NULL,
	QTY NUMBER(6) NOT NULL,
	RATE NUMBER(10,2),
	DISC NUMBER(10,2),
	COST NUMBER(10,2),
	QTYOUT NUMBER(6),
	N_RATE NUMBER(10,2),
	BARCODE VARCHAR2(10),
	GST NUMBER(5,2),
	DEL_STATUS VARCHAR2(1),
	CONSTRAINT "STOCK_TRANS_PK" PRIMARY KEY (YEAR,BRANCH_ID,BOOK,SRNO,PRODUCT_ID,BRAND_ID,MODEL_ID,CONDITION)
	USING INDEX
	TABLESPACE SYSTEM STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS 2 MAXTRANS 255,
	CONSTRAINT "STOCK_TRANS_TRANS_FK" FOREIGN KEY (YEAR,BRANCH_ID,BOOK,SRNO) REFERENCES TRANSACTIONS (YEAR,BRANCH_ID,BOOK,SRNO))
	TABLESPACE SYSTEM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS  255  STORAGE ( FREELISTS 1 FREELIST GROUPS 1) NOLOGGING
	PARTITION BY LIST (YEAR)
	(PARTITION P_2003 VALUES (2003),
	PARTITION P_2004 VALUES (2004),
	PARTITION P_2005 VALUES (2005),
	PARTITION P_2006 VALUES (2006),
	PARTITION  P_2007 VALUES (2007),
	PARTITION  P_2008 VALUES (2008),
	PARTITION  P_2009 VALUES (2009));



i have create a view stock_leger1 joining these two tables


CREATE VIEW STOCK_LEDGER1 AS
(SELECT A.YEAR,A.BRANCH_ID,A.BOOK,A.SRNO,A.REF,A.TRANS_DATE,A.DR_ACCOUNT,A.CR_ACCOUNT,A.PARTY_CODE,A.WAREHOUSE_ID,A.TO_WAREHOUSE,A.TO_FROM_BRANCH,
B.PRODUCT_ID,B.BRAND_ID,B.MODEL_ID,B.CONDITION,B.QTY,B.RATE,B.DISC,B.GST,B.N_RATE
FROM TRANSACTIONS A,STOCK_TRANSACTIONS B
WHERE A.YEAR=B.YEAR AND
A.BRANCH_ID=B.BRANCH_ID
AND A.BOOK=B.BOOK AND
A.SRNO=B.SRNO);



can anyone help me which type of index on both tables should i create to optimize this query. the query and execution plan of the query is


SQL> set autotrace traceonly explain statistics
SQL> set term on echo on time on
14:47:21 SQL> SELECT * FROM STOCK_LEDGER1
14:47:22   2  WHERE year=2008 AND
14:47:22   3  (warehouse_id='02' OR to_warehouse='02') AND
14:47:22   4  product_id='001' AND
14:47:22   5  brand_id='001' AND
14:47:22   6  model_id='0016'AND
14:47:22   7  condition='0'
14:47:22   8  /

1338 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=198)
   1    0   NESTED LOOPS (Cost=4 Card=1 Bytes=198)
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'STOCK_TRANSACTI
          ONS' (Cost=3 Card=1 Bytes=111)

   3    2       INDEX (RANGE SCAN) OF 'STOCK_TRANS_PK' (UNIQUE) (Cost=
          2 Card=1)

   4    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TRANSACTIONS' (
          Cost=1 Card=1 Bytes=87)

   5    4       INDEX (UNIQUE SCAN) OF 'TRANS_PK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12693  consistent gets
       3288  physical reads
          0  redo size
      50848  bytes sent via SQL*Net to client
       1478  bytes received via SQL*Net from client
         91  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1338  rows processed

14:47:42 SQL> 



thanks.

Re: need help to create index to optimize query [message #333534 is a reply to message #333530] Sat, 12 July 2008 04:47 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
one more thing i forgot to mention is number of records.

SQL> select year,count(*)
  2  from transactions
  3  group by year
  4  /

      YEAR   COUNT(*)
---------- ----------
      2003          3
      2004       1015
      2005       1178
      2006     110222
      2007     144294
      2008     154982
      2009       1148

7 rows selected.

SQL> select year,count(*)
  2  from stock_transactions
  3  group by year
  4  /

      YEAR   COUNT(*)
---------- ----------
      2003          5
      2004       1069
      2005       1226
      2006     115721
      2007     137383
      2008     138692
      2009       1365

7 rows selected.
Re: need help to create index to optimize query [message #333541 is a reply to message #333534] Sat, 12 July 2008 07:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's not easy because the query columns come from both of the underlying tables.

Without understanding the selectivity of each column, I'd consider indexing:

transactions(year, warehouse_id)
transactions(year, to_warehouse_id)
stock_transactions(year, product_id, brand_id, model_id, condition)

If you created these indexes as LOCALLY PARTITIONED, you could remove the YEAR column from each.

Ross Leishman
Re: need help to create index to optimize query [message #333542 is a reply to message #333530] Sat, 12 July 2008 07:44 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Thanks for reply Sir.

Both tables are partitioned using LIST Partitioning on YEAR column. First i need to select the year column to display the stock ledger of a particular year.

What should be the type of index for both tables.?
Secondly for best performance the indexes i am going to create should be LOCAL or Global Partitioned indexes.?

please help.

Thanks

[Updated on: Sat, 12 July 2008 08:50]

Report message to a moderator

Re: need help to create index to optimize query [message #333575 is a reply to message #333542] Sat, 12 July 2008 23:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use regular b-tree indexes.

For your query. it will not matter whether the indexes are local or global, so go with local because they are easier to maintain.

Ross Leishman
Re: need help to create index to optimize query [message #333603 is a reply to message #333530] Sun, 13 July 2008 08:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Your view is this:

CREATE VIEW Stock_Ledger1
AS
  (SELECT a.YEAR,
          a.Branch_Id,
          a.Book,
          a.srNo,
          a.REF,
          a.tRans_Date,
          a.dr_Account,
          a.cr_Account,
          a.Party_Code,
          a.Warehouse_Id,
          a.To_Warehouse,
          a.To_From_Branch,
          b.Product_Id,
          b.Brand_Id,
          b.Model_Id,
          b.Condition,
          b.qty,
          b.Rate,
          b.Disc,
          b.gst,
          b.n_Rate
   FROM   Transactions a,
          Stock_Transactions b
   WHERE  a.YEAR = b.YEAR
          AND a.Branch_Id = b.Branch_Id
          AND a.Book = b.Book
          AND a.srNo = b.srNo);


I would ask a question first.

Is this view for general purpose querying? Or is it meant strictly to support the one query you are doing?

If this is a general purpose view then you should start its tuning process by giving it the indexes it needs to support itself in all situations. That would be these two indexes:

transactions(year,branch_id,book,srno)
stock_transactions(year,branch_id,book,srno)

Then, if your query is important enough, you can add additional indexes to support this specific query. There are two indexes here because of what I believe is called DISJUNCTIVE NORMAL FORM TRANSFORMATION, which is a tuning step Oracle will do to a query when faced with OR conditions like the one you have here. I will show in a moment what this transformation is.

transactions(year,warehouse_id)
transactions(year,to_warehouse_id)

Thus giving you this final index set:

transactions(year,warehouse_id)
transactions(year,to_warehouse_id)
transactions(year,branch_id,book,srno)
stock_transactions(year,branch_id,book,srno)


If your tuning is specific to this query then one would consider Oracle's view merging process in addition to other query optimizations. After doing view merging and DISJUNCTIVE NORMAL FORM TRANSFORMATION, your specific query:

14:47:21 SQL> SELECT * FROM STOCK_LEDGER1
14:47:22   2  WHERE year=2008 AND
14:47:22   3  (warehouse_id='02' OR to_warehouse='02') AND
14:47:22   4  product_id='001' AND
14:47:22   5  brand_id='001' AND
14:47:22   6  model_id='0016'AND
14:47:22   7  condition='0'
14:47:22   8  /

Will merge with your view query, and be transformed into a reformulated query of this nature, given view merging and disjunctive normal form translation (OR indexing). Notice how if there are indexes that support the OR condition, Oracle can look at splitting up the query into something that resembles a UNION ALL query. This may be a crude description of the process, but it gives us what we need to understand where we have to go with indexing.

   SELECT a.YEAR,
          a.Branch_Id,
          a.Book,
          a.srNo,
          a.REF,
          a.tRans_Date,
          a.dr_Account,
          a.cr_Account,
          a.Party_Code,
          a.Warehouse_Id,
          a.To_Warehouse,
          a.To_From_Branch,
          b.Product_Id,
          b.Brand_Id,
          b.Model_Id,
          b.Condition,
          b.qty,
          b.Rate,
          b.Disc,
          b.gst,
          b.n_Rate
   FROM   Transactions a,
          Stock_Transactions b
   WHERE  a.YEAR = b.YEAR
          AND a.Branch_Id = b.Branch_Id
          AND a.Book = b.Book
          AND a.srNo = b.srNo
and a.year = 2008
and a.warehouse_id = 02
and b.year = 2008
and b.product = '001'
and b.brand_id = '001'
and b.model_id = '0016'
and b.condition = '0'
union all
select ...
   FROM   Transactions a,
          Stock_Transactions b
   WHERE  a.YEAR = b.YEAR
          AND a.Branch_Id = b.Branch_Id
          AND a.Book = b.Book
          AND a.srNo = b.srNo
and a.year = 2008
and a.to_warehouse_id = 02
and b.year = 2008
and b.product = '001'
and b.brand_id = '001'
and b.model_id = '0016'
and b.condition = '0'


From this we would think the following indexes might work well. Under certain conditions this index set might be best for this query.

transactions(a.year,a.warehouse_id,branch_id,book,srno)
transactions(a.year.a.to_warehouse_id,branch_id,book,srno)
stock_transactions(year,branch_id,book,srno,product,brand_id,model_id,condition)

However, if you are an old school oracle person, then you would shorten the indexes on transactions giving you this index set:

transactions(a.year,a.warehouse_id)
transactions(a.year.a.to_warehouse_id)
stock_transactions(year,branch_id,book,srno,product,brand_id,model_id,condition)

Then if understanding the data yeilds the knowledge that matching on (year,branch_id,book,srno) means that (product,brand_id,model_id,condition) provide little help to shortening query times because of the small number of rows left, then you would shorten the stocktransactions index giving this set of indexes:

transactions(a.year,a.warehouse_id)
transactions(a.year.a.to_warehouse_id)
stock_transactions(year,branch_id,book,srno)

And now we have learned something. The indexes above, are identical to the indexes we created when we were tuning the view for general use first and then tuned our specific query. Hmm... To see this, here are our final two index set alternatives:

View tuned for General Use first, then warehouse query tuned:

transactions(year,warehouse_id)
transactions(year,to_warehouse_id)
transactions(year,branch_id,book,srno)
stock_transactions(year,branch_id,book,srno)

Warehouse Query tuned without respect to general purpose use given our assumption is correct that (product,brand_id,model_id,condition) provide only minimal value once we join across (year,branch_id,book,srno).

transactions(a.year,a.warehouse_id)
transactions(a.year.a.to_warehouse_id)
stock_transactions(year,branch_id,book,srno)

Ross is a more experienced tuner than I when it comes to the storage aspects of tuning. I would be interested in his comments of this analysis. Ross, you got any comments?

Good luck, Kevin

Re: need help to create index to optimize query [message #333702 is a reply to message #333603] Mon, 14 July 2008 03:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This query can be resolved in one of two ways:
- Filter table A, Filter table B, Hash join; or
- Filter table A, nested loop join table B, filter result on B

Using the first option, you would need indexes on
- transactions(year, warehouse_id)
- transactions(year, to_warehouse_id)
to cover the OR condition; and

- stock_transactions(year, product_id, brand_id, model_id, condition)
to cover the remaining filter predicates.

Using the second option, one of these tables will be used as the driving table in a nested loops join. If that driving table is TRANSACTIONS, then you will need the first two indexes and then join to stock_transactions on its PK index. If the driving table is stock_transactions, you will need the last index and then join to TRANSACTIONS on its PK index.

So I agree with Kevin that you need stock_transactions(year,branch_id,book,srno) but I assumed it already existed because of the PK.

I didn't assume that stock_transactions(year, product_id, brand_id, model_id, condition) would have poor selectivity, so I recommended it.

In the Nested Loop scenario, you could append the join columns to the filter indexes to avoid some redundant reads, but I thought that was a bit gratuitous.

Ross Leishman
Re: need help to create index to optimize query [message #333733 is a reply to message #333702] Mon, 14 July 2008 05:13 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
I have decided to use first option. if i am not satisfied with this i will go for the second.

Quote:

Using the first option, you would need indexes on
- transactions(year, warehouse_id)
- transactions(year, to_warehouse_id)
to cover the OR condition; and

- stock_transactions(year, product_id, brand_id, model_id, condition)
to cover the remaining filter predicates.



i have created three idexes on both tables but ORACLE is showing their status as "N/A".

Can you please tell me why?


SQL> create index warehouse_id_local_idx on transactions (warehouse_id) local
  2  /

Index created.

SQL> create index to_warehouse_local_idx on transactions (to_warehouse) local
  2  /

SQL> create index item_local_idx on stock_transactions (product_id,brand_id,model_id,condition) local
  2  /

Index created.

SQL> select index_name,index_type,table_name,status
  2  from dba_indexes
  3  where index_name in ('WAREHOUSE_ID_LOCAL_IDX','TO_WAREHOUSE_LOCAL_IDX','ITEM_LOCAL_IDX')
  4  /

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS
------------------------------ --------------------------- ------------------------------ --------
WAREHOUSE_ID_LOCAL_IDX         NORMAL                      TRANSACTIONS                   N/A
TO_WAREHOUSE_LOCAL_IDX         NORMAL                      TRANSACTIONS                   N/A
ITEM_LOCAL_IDX                 NORMAL                      STOCK_TRANSACTIONS             N/A



Also i wanted to clear one thing, Specificaly with my case when quering Stock_leder1 view, will oracle join whole data ( all partiitons ) from two tables and then after select the partition p_2008?

OR

it will only select parition P_2008 from both tables and join them?

Thanks

[Updated on: Mon, 14 July 2008 05:55]

Report message to a moderator

Re: need help to create index to optimize query [message #333810 is a reply to message #333733] Mon, 14 July 2008 08:46 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Can anyone reply me.? If Ross is busy.
Re: need help to create index to optimize query [message #333811 is a reply to message #333530] Mon, 14 July 2008 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>it will only select parition P_2008 from both tables and join them?


http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above


What does EXPLAIN PLAN show?
Re: need help to create index to optimize query [message #333815 is a reply to message #333811] Mon, 14 July 2008 08:57 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
And What indexes i have created?
Re: need help to create index to optimize query [message #333923 is a reply to message #333815] Mon, 14 July 2008 21:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since you view contains WHERE A.YEAR=B.YEAR and your query contains WHERE year=2008, Oracle should apply a transitive rule and scan only the relevant partition(s).

Make sure you gather statistics with DBMS_STATS.GATHER_TABLE_STATS.

Try it out and post the plan if you're not happy with it.

Ross Leishman
Re: need help to create index to optimize query [message #333991 is a reply to message #333923] Tue, 15 July 2008 02:36 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
I tried few things but the situation was not improving much
Finaly i tried by making STOCK_TRANSACTIONS table as driving table.


Quote:

Using the second option, one of these tables will be used as the driving table in a nested loops join. If that driving table is TRANSACTIONS, then you will need the first two indexes and then join to stock_transactions on its PK index. If the driving table is stock_transactions, you will need the last index and then join to TRANSACTIONS on its PK index.



i changed my view as


SQL> CREATE OR REPLACE VIEW STOCK_LEDGER1 AS
  2  (SELECT A.YEAR,A.BRANCH_ID,A.BOOK,A.SRNO,A.PRODUCT_ID,A.BRAND_ID,A.MODEL_ID,A.CONDITION,A.QTY,A.RATE,A.DISC,A.GST,A.N_RATE,
  3  B.REF,B.TRANS_DATE,B.DR_ACCOUNT,B.CR_ACCOUNT,B.PARTY_CODE,B.WAREHOUSE_ID,B.TO_WAREHOUSE,B.TO_FROM_BRANCH
  4  FROM STOCK_TRANSACTIONS A,TRANSACTIONS B
  5  WHERE A.YEAR=B.YEAR AND
  6  A.BRANCH_ID=B.BRANCH_ID
  7  AND A.BOOK=B.BOOK AND
  8  A.SRNO=B.SRNO)
  9  /

View created.


Then i created following index on STOCK_TRANSACTIONS table


SQL> create index item_global_idx on stock_transactions (year,product_id,brand_id,model_id,condition)
  2  /

Index created.



Then i tried this query. The query and the plan is


SQL> set autotrace traceonly explain statistics
SQL> set echo on term on time on
13:17:01 SQL> select * from stock_ledger1
13:17:03   2  where year=2008 AND
13:17:03   3  product_id='001' AND
13:17:03   4  brand_id='001' AND
13:17:03   5  model_id='0016'AND
13:17:03   6  condition='0' AND
13:17:03   7  (warehouse_id='02' OR to_warehouse='02')
13:17:03   8  
13:17:04 SQL> /

1338 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=198)
   1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=198)
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'STOCK_TRANSACTI
          ONS' (Cost=2 Card=1 Bytes=111)

   3    2       INDEX (RANGE SCAN) OF 'ITEM_GLOBAL_IDX' (NON-UNIQUE) (
          Cost=1 Card=1)

   4    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TRANSACTIONS' (
          Cost=1 Card=1 Bytes=87)

   5    4       INDEX (UNIQUE SCAN) OF 'TRANS_PK' (UNIQUE)




Statistics
----------------------------------------------------------
        169  recursive calls
          0  db block gets
      10019  consistent gets
       2618  physical reads
          0  redo size
      65819  bytes sent via SQL*Net to client
       1478  bytes received via SQL*Net from client
         91  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1338  rows processed



The i created two indexes on transactions table as


13:17:18 SQL> create index warehouse_id_global_idx on transactions (year,warehouse_id)
13:19:00   2  /

Index created.

13:19:08 SQL> create index to_warehouse_global_idx on transactions (year,to_warehouse)
13:19:30   2  /

Index created.



Plan after creating above two indexes is


SQL> set autotrace traceonly explain statistics
SQL> set echo on term on time on
13:20:40 SQL> select * from stock_ledger1
13:20:41   2  where year=2008 AND
13:20:41   3  product_id='001' AND
13:20:41   4  brand_id='001' AND
13:20:41   5  model_id='0016'AND
13:20:41   6  condition='0' AND
13:20:41   7  (warehouse_id='02' OR to_warehouse='02')
13:20:41   8  /

1338 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=198)
   1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=198)
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'STOCK_TRANSACTI
          ONS' (Cost=2 Card=1 Bytes=111)

   3    2       INDEX (RANGE SCAN) OF 'ITEM_GLOBAL_IDX' (NON-UNIQUE) (
          Cost=1 Card=1)

   4    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TRANSACTIONS' (
          Cost=1 Card=1 Bytes=87)

   5    4       INDEX (UNIQUE SCAN) OF 'TRANS_PK' (UNIQUE)




Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
      10003  consistent gets
       2222  physical reads
          0  redo size
      65819  bytes sent via SQL*Net to client
       1478  bytes received via SQL*Net from client
         91  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1338  rows processed

13:20:53 SQL> 



Can you advice me please?
Re: need help to create index to optimize query [message #334020 is a reply to message #333991] Tue, 15 July 2008 03:47 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
The result gathered after analyizing tables are


SQL> exec dbms_stats.gather_table_stats('RSS09','TRANSACTIONS',cascade => TRUE);

PL/SQL procedure successfully completed.


SQL> exec dbms_stats.gather_table_stats('RSS09','STOCK_TRANSACTIONS',cascade => TRUE);

PL/SQL procedure successfully completed.


SQL> ED
Wrote file afiedt.buf

  1*  select table_name,column_name,num_distinct,last_analyzed,num_buckets from user_tab_cols where table_name like ('%TRANSACTIONS%')
SQL> /

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL NUM_BUCKETS
------------------------------ ------------------------------ ------------ --------- -----------
STOCK_TRANSACTIONS             YEAR                                      7 15-JUL-08           1
STOCK_TRANSACTIONS             BRANCH_ID                                25 15-JUL-08           1
STOCK_TRANSACTIONS             BOOK                                    192 15-JUL-08           1
STOCK_TRANSACTIONS             SRNO                                  16199 15-JUL-08           1
STOCK_TRANSACTIONS             PRODUCT_ID                               26 15-JUL-08           1
STOCK_TRANSACTIONS             BRAND_ID                                 92 15-JUL-08           1
STOCK_TRANSACTIONS             MODEL_ID                                127 15-JUL-08           1
STOCK_TRANSACTIONS             CONDITION                                 2 15-JUL-08           1
STOCK_TRANSACTIONS             QTY                                     170 15-JUL-08           1
STOCK_TRANSACTIONS             RATE                                   7064 15-JUL-08           1
STOCK_TRANSACTIONS             DISC                                     17 15-JUL-08           1

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL NUM_BUCKETS
------------------------------ ------------------------------ ------------ --------- -----------
STOCK_TRANSACTIONS             COST                                   5717 15-JUL-08           1
STOCK_TRANSACTIONS             QTYOUT                                  104 15-JUL-08           1
STOCK_TRANSACTIONS             N_RATE                                 1464 15-JUL-08           1
STOCK_TRANSACTIONS             BARCODE                                 672 15-JUL-08           1
STOCK_TRANSACTIONS             GST                                       2 15-JUL-08           1
STOCK_TRANSACTIONS             DEL_STATUS                                2 15-JUL-08           1
TRANSACTIONS                   TRANS_TIME                           198281 15-JUL-08           1
TRANSACTIONS                   LAST_UPD_BY                               0 15-JUL-08           1
TRANSACTIONS                   LAST_UPD_TIME                        157572 15-JUL-08           1
TRANSACTIONS                   ACC_BRANCH                               20 15-JUL-08           1
TRANSACTIONS                   LEA_YEAR                                  6 15-JUL-08           1

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL NUM_BUCKETS
------------------------------ ------------------------------ ------------ --------- -----------
TRANSACTIONS                   LEA_ACC                                2610 15-JUL-08           1
TRANSACTIONS                   INSPECTOR_ID                              0 15-JUL-08           1
TRANSACTIONS                   YEAR                                      7 15-JUL-08           1
TRANSACTIONS                   BRANCH_ID                                25 15-JUL-08           1
TRANSACTIONS                   BOOK                                    309 15-JUL-08           1
TRANSACTIONS                   SRNO                                  16202 15-JUL-08           1
TRANSACTIONS                   TRANS_DATE                             1584 15-JUL-08           1
TRANSACTIONS                   DR_ACCOUNT                             1180 15-JUL-08           1
TRANSACTIONS                   CR_ACCOUNT                              741 15-JUL-08           1
TRANSACTIONS                   PARTY_CODE                             1254 15-JUL-08           1
TRANSACTIONS                   WAREHOUSE_ID                             30 15-JUL-08           1

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL NUM_BUCKETS
------------------------------ ------------------------------ ------------ --------- -----------
TRANSACTIONS                   SALESMAN_ID                              23 15-JUL-08           1
TRANSACTIONS                   ORDER_ID                                  0 15-JUL-08           1
TRANSACTIONS                   RECV_DATE                                 6 15-JUL-08           1
TRANSACTIONS                   TO_WAREHOUSE                             28 15-JUL-08           1
TRANSACTIONS                   TO_FROM_BRANCH                           24 15-JUL-08           1
TRANSACTIONS                   REF                                  101760 15-JUL-08           1
TRANSACTIONS                   AMOUNT                                39784 15-JUL-08           1
TRANSACTIONS                   DISC                                      7 15-JUL-08           1
TRANSACTIONS                   BANK                                    135 15-JUL-08           1
TRANSACTIONS                   STATUS                                    2 15-JUL-08           1
TRANSACTIONS                   CREDIT_DAYS                               0 15-JUL-08           1

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL NUM_BUCKETS
------------------------------ ------------------------------ ------------ --------- -----------
TRANSACTIONS                   COST                                      0 15-JUL-08           1
TRANSACTIONS                   TYPE                                      2 15-JUL-08           1
TRANSACTIONS                   USER_ID                                  16 15-JUL-08           1


SQL> 
SQL> ed
Wrote file afiedt.buf

  1  select table_name,index_name,num_rows,last_analyzed from user_indexes
  2* where table_name in ('TRANSACTIONS','STOCK_TRANSACTIONS')
SQL> /

TABLE_NAME                     INDEX_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ------------------------------ ---------- ---------
TRANSACTIONS                   CR_ACCOUNT_GLOBAL_IDX              413452 15-JUL-08
TRANSACTIONS                   DR_ACCOUNT_GLOBAL_IDX              413452 15-JUL-08
STOCK_TRANSACTIONS             ITEM_GLOBAL_IDX                    395916 15-JUL-08
STOCK_TRANSACTIONS             STOCK_TRANS_PK                     395916 15-JUL-08
TRANSACTIONS                   TO_WAREHOUSE_GLOBAL_IDX            413452 15-JUL-08
TRANSACTIONS                   TRANS_PK                           413452 15-JUL-08
TRANSACTIONS                   WAREHOUSE_ID_GLOBAL_IDX            413452 15-JUL-08

7 rows selected.

SQL> 




Please help me.


Thanks

[Updated on: Tue, 15 July 2008 03:51]

Report message to a moderator

Re: need help to create index to optimize query [message #334059 is a reply to message #334020] Tue, 15 July 2008 06:07 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Although the performace has improved little. but i still want to improve it please guide me or advice me something else in the light of above.

Is there any other way?

Thanks.

[Updated on: Tue, 15 July 2008 06:49]

Report message to a moderator

Re: need help to create index to optimize query [message #334216 is a reply to message #334059] Tue, 15 July 2008 22:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do a SQL Trace and post the output of TKPROF.

I doubt there is much more you can do because you have separate filters on each table. This means you are probably going to get more rows than you need from one of those tables.

If you were absolutely desperate, you could add more columns to your indexes that would avoid redundant table access:

stock_transactions(year, product_id, brand_id, model_id, condition, branch_id, book, srno)

transactions(YEAR, BRANCH_ID, BOOK,SRNO, warehouse_id, to_warehouse_id)

But be aware that these indexes are using up quite a bit of space JUST TO SERVE THIS ONE QUERY. It had better be an important query.

Ross Leishman
Re: need help to create index to optimize query [message #334397 is a reply to message #334216] Wed, 16 July 2008 06:37 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Sorry for late reply Sir.

Here is TKPROF output.


select * from stock_ledger1
where year=2008 AND
product_id='001' AND
brand_id='001' AND
model_id='0016'AND
condition='0' AND
(warehouse_id='02' OR to_warehouse='02')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.04          1          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch      182      0.20      14.01       3244      19998          0        2676
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      186      0.20      14.06       3245      20000          0        2676

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 139  

Rows     Row Source Operation
-------  ---------------------------------------------------
   1338  NESTED LOOPS  
   2965   TABLE ACCESS BY GLOBAL INDEX ROWID STOCK_TRANSACTIONS PARTITION: 3 3 
   2965    INDEX RANGE SCAN ITEM_GLOBAL_IDX (object id 36127)
   1338   TABLE ACCESS BY GLOBAL INDEX ROWID TRANSACTIONS PARTITION: 3 3 
   2965    INDEX UNIQUE SCAN TRANS_PK (object id 35445)

********************************************************************************




Its been greate learning from you. Thanks

Please advice me What should i do Now?


Thanks.

[Updated on: Wed, 16 July 2008 06:52]

Report message to a moderator

Re: need help to create index to optimize query [message #334434 is a reply to message #334397] Wed, 16 July 2008 08:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You may be able to save perhaps 20-30% of the query time by adding warehouse_id and to_warehouse_id to the PK index

transactions(YEAR, BRANCH_ID, BOOK,SRNO, warehouse_id, to_warehouse_id)

You could do this by creating a completely new index in addition to the unique PK index, or save space by dropping the primary key, creating this index, and then recreating the primary key on just the first four columns with the USING INDEX clause to nominate this new index as the structure that will support the PK.

Note that this method can impact insert-performance because Oracle uses a different method of verifying uniqueness in the table.

What I am saying is that this query had better be VERY important, because you are jeopardising the performance of the rest of the applcation just to tune it.

Ross Leishman
Re: need help to create index to optimize query [message #334440 is a reply to message #334434] Wed, 16 July 2008 08:46 Go to previous message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
I dont want to disturb the whole application.


Thanks Again for your help.
Previous Topic: script to delete all objets of schema
Next Topic: Convert datetime representation
Goto Forum:
  


Current Time: Sat Dec 10 13:01:23 CST 2016

Total time taken to generate the page: 0.05261 seconds