Oracle Version: 11gr2
OS: Red Hat Linux
There are two tables in relationship parent and child:
CREATE TABLE par_tab
list_id NUMBER (10) NOT NULL,
process_id NUMBER(6) NOT NULL,
file_name VARCHAR2(255) NOT NULL,
status INTEGER NOT NULL,
inserted_date TIMESTAMP NOT NULL,
last_updated TIMETAMP NOT NULL,
) PARTITION BY RANGE (process_id)
PARTITION PTMAX VALUES LESS THAN (MAXVALUE)
CREATE UNIQUE INDEX par_Tab_pk ON par_tab (list_id);
ALTER TABLE par_tab ADD CONSTRAINT par_tab_pk PRIMARY KEY (LIST_ID) USING INDEX;
CREATE INDEX par_tab_idx1 ON par_tab (process_id, process_Date) LOCAL;
CREATE INDEX par_tab_idx2 ON par_tab (session_id) LOCAL;
CREATE TABLE ch_tab
id NUMBER(10) NOT NULL,
list_id NUMBER(10) NOT NULL,
code VARCHAR2(30) NOT NULL,
code_type VARCHAR2(8) NOT NULL,
symbol VARCHAR2(20) NOT NULL,
thr_id NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT fk_ch_par_tab FOREIGN KEY (list_id) REFERENCES par_tab (list_id)
) PARTITION BY REFERENCE (fk_ch_par_tab);
CREATE UNIQUE INDEX ch_tab_pk ON ch_tab (id);
ALTER TABLE ch_tab ADD CONSTRAINT ch_tab_pk PRIMARY KEY (id) USING INDEX;
CREATE INDEX ch_tab_idx1 ON ch_tab (list_id, symbol) LOCAL;
CREATE INDEX ch_tab_idx2 ON ch_tab (list_id, code, code_type) LOCAL;
There partitions for 20 years such as one partition holds data for one month. The values in column PAR_TAB.PROCESS_DATE represents dates, for example 20140827, which is 2014-Aug-27.
Only four partitions are full of data. These are the first four partitions in the tables. Extents are allocated only for them.
Both tables and their indexes are analyzed, statistics are up-to-date on tables and indexes (statistics are not stale).
Data volumes in Parent table PAR_TAB are:
* 1st partition - 601 rows
* 2nd partition - 503 rows
* 3rd partition - 689 rows
* 4th partition - 601 rows
Data volumes in Child table CH_TAB are:
* 1st partition - 95,915,105 rows
* 2nd partition - 95,915,203 rows
* 3rd partition - 97,887,531 rows
* 4th partition, 95,915,105 rows
All constraints are validated and enabled on the two tables and all indexes are in valid state.
When query like this one is run, oracle optimizer goes for full scan on the child:
WHERE list_id = 123456789;
Query like above returns apporx 956,347 rows. The query plan is TABLE ACCESS FULL - oracle scans the whole partition where values LIST_ID = 123456789 are stored, even though the total count of rows for LIST_ID = 123456789 is much less than 10% of the total number of rows in the same partition. Oracle behaves the same, goes for full scan, with every search by LIST_ID. ORacle goes for FULL SCAN even if the final result set is just 14,000 rows (against 95 million in the whole partition).
If I hint the query with /*+ INDEX (ch_tab)*/, then it uses index and finishes significantly faster.
The estimation for using index is for higher cost compared with the plan using full scan, but at the same time the estimated time with usage of index is much much faster and the final result set is much smaller than 10 percent of total rows in partition.
What could be influential in this scenario, so oracle always decides to go for tabel access full?
Please share your knowledge.