CODEWITHSUNDEEP
sqloracle11g
user1951344
user1951344

Reputation: 11

Avoiding full Table scan in Oracle

I have a query which fetches the last 30 rows from the whole table which contains thousands of entries.The table is large so database query is taking more than 5 minutes to execute. How can I avoid the full table scan on the table so that it scans only the last few rows. I am using Oracle 11g 

SELECT DD,MODEL,CNT,SYS
  FROM DVCE_TAB
  WHERE DD >= TO_DATE('2015-08-09','YYYY-MM-DD')
  AND DD <= TO_DATE('2015-09-08','YYYY-MM-DD')
  AND SYS IN ('00','01')

DD is the date and SYS is System type of device and MODEL is name of device DD and MODEL is the combined primary key for the Table TABLE contains thousands of entries and has data from 2010 I have to just fetch last months data

Upvotes: 1

Views: 5532

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

A few ideas come to mind:

1) Define an index on the DD and SYS columns:

CREATE INDEX DVCE_TAB_1
  ON DVCE_TAB (DD, SYS);

You'll probably need to gather table statistics after adding indexes:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'YOUR_SCHEMA_NAME',
                                tabname => 'DVCE_TAB');
END;

That may be all you need, but if needed you can always...

2) Partition DVCE_TAB on the DD column.

CREATE TABLE DVCE_TAB
  (...column definitions...)
  PARTITION BY RANGE (DD) 
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  PARTITION DVCE_TAB_P1 VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY')));

To do this you may need to actually create a new table with a different name, copy all the existing data from the 'old' DVCE_TAB to the 'new' DVCE_TAB, then drop the original table and rename the 'new' table to DVCE_TAB.

Best of luck.

Upvotes: 2

Related Questions