Home » SQL & PL/SQL » SQL & PL/SQL » Index not used (Oracle Database 10g Release 10.2.0.1.0 , UNIX)
icon8.gif  Index not used [message #407061] Mon, 08 June 2009 05:43 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
Hi,
Below is my table structure.
CREATE TABLE PIN_TYPE
(
  PIN_NO            VARCHAR2(20 BYTE),
  AMOUNT            FLOAT(12)     NOT NULL,
  STATUS            CHAR(1 BYTE)  NOT NULL
);

CREATE INDEX IDX_AMOUNT_STATUS ON PIN_TYPE (AMOUNT, STATUS);

ALTER TABLE PIN_TYPE ADD (  PRIMARY KEY (PIN_NO));




I have 4 lac records with AMOUNT 20 and STATUS 'N'.Now a query (AMOUNT=20 and STATUS='N'and ROWNUM=1) takes 10 millisecond.

But if I have only 50000 rows(out of 4lac) with STATUS='N',the same query takes 50 millisecond.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production


SQL>  SELECT COUNT(*) FROM PIN_TYPE ;
  COUNT(*)
----------
    400000
SQL> SELECT COUNT(*) FROM PIN_TYPE WHERE AMOUNT=20 AND STATUS='N';
  COUNT(*)
----------
    400000

SQL> SET AUTOTRACE ON EXPLAIN TIMING ON
SQL> SELECT PIN_NO FROM PIN_TYPE WHERE STATUS='N' AND AMOUNT=20 AND ROWNUM=1;
PIN_NO
--------------------
1111111111

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4235783576

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| PIN_TYPE |     2 |    38 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - filter("STATUS"='N' AND "AMOUNT"=20)

SQL> SET AUTOTRACE OFF TIMING OFF
SQL> UPDATE PIN_TYPE SET STATUS='U' WHERE ROWNUM<=350000;

350000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> SET AUTOTRACE ON EXPLAIN TIMING ON
SQL> SELECT PIN_NO FROM PIN_TYPE WHERE STATUS='N' AND AMOUNT=20 AND ROWNUM=1;

PIN_NO
--------------------
 000000447467

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 4235783576

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| PIN_TYPE |     2 |    38 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - filter("STATUS"='N' AND "AMOUNT"=20)

SQL>


I tried to have bitmap index ,but my version doesn't support.
Please help to improve the performance.


Re: Index not used [message #407068 is a reply to message #407061] Mon, 08 June 2009 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Statistics are gathered?

Regards
Michel

Re: Index not used [message #407071 is a reply to message #407061] Mon, 08 June 2009 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The effect you are seeing is an artifact of how you are creating your test data.

Your query will stop as soon as it returns a single row - so you run it the first time and it searches through the table, finds a matching row, and stops.

For the second run, you make sure that the first 350,000 records in the table (ie those in the blocks searched first) all fail to match the query criteria.

Now when you run the query, it has to search through a minimum of 350,001 records before it can find a matching record.

Unsuprisingly, it takes longer to find a matching record.
Re: Index not used [message #407072 is a reply to message #407061] Mon, 08 June 2009 06:19 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
Gather statistics for table and index and try again. Without proper statistics oracle may not pick the best execution plan.
Re: Index not used [message #407073 is a reply to message #407071] Mon, 08 June 2009 06:28 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Wont it use index to directly fetch records with STATUS='N'?
Re: Index not used [message #407075 is a reply to message #407073] Mon, 08 June 2009 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on statistics and many other things.
Read Performances Tuning sticky

Regards
Michel
Re: Index not used [message #407077 is a reply to message #407068] Mon, 08 June 2009 06:32 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Sorry ,I don't know about statistics.
I found something in net and seems doesn't help.
Took 4 minutes to gather stat. and still taking 40 millisecond
Even tested on a loop of 100 at 1 per second.


SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

Elapsed: 00:04:00.04
SQL> /

PIN_NO
--------------------
 000000447467

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 4235783576

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| PIN_TYPE |     1 |    19 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - filter("STATUS"='N' AND "AMOUNT"=20)




Re: Index not used [message #407083 is a reply to message #407073] Mon, 08 June 2009 06:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not according to your explain plans - Full table scan both times.
icon14.gif  Re: Index not used [message #407086 is a reply to message #407075] Mon, 08 June 2009 07:03 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Hi,
After gathering stats.,I load tested with pro*c application and now it takes only 4 millisecond constantly.

Should I gather stats. once for every considerable changes in table data?


Re: Index not used [message #407091 is a reply to message #407086] Mon, 08 June 2009 07:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll take a guess and say that your new plan will be using the index.

As to regenerating stats - yes.

If you enable monitoring on your tables, you can get Dbms_Stats to generate stats for only the objects that it thinks have changed enough to require fresh stats.
Previous Topic: How to Use Cross Tab query
Next Topic: find difference of two numbers in oracle sql
Goto Forum:
  


Current Time: Fri Dec 09 06:15:26 CST 2016

Total time taken to generate the page: 0.06660 seconds